Best way to estimate ETA for a refresh request

Shawn_Ferris

Hey All, I've got a newly created projection being refreshed, and I'm trying to estimate the amount of time remaining. Here's a query I'm using:

  select    node_name    ,operator_name    ,counter_name    ,sum(counter_value)  from    execution_engine_profiles  where    (      session_id=?    ) and (      (        operator_name='DataTarget'        and counter_name in ('input rows','written rows')      ) or (        operator_name='Scan'        and counter_name='estimated rows produced'      )    )  group by    node_name    ,operator_name    ,counter_name  order by    node_name    ,counter_name  ;  

It looks like the input rows and written rows (records) should align with the estimated rows produced. (I understand it's an estimation for rows produced, so it may vary, but I just need a rough estimate too) For instance:

        node_name      | operator_name |      counter_name       |     sum  ---------------------+---------------+-------------------------+--------------   v_vertprd1_node0001 | Scan          | estimated rows produced | 226595923824   v_vertprd1_node0001 | DataTarget    | input rows              |   5296970240   v_vertprd1_node0001 | DataTarget    | written rows            |            0   v_vertprd1_node0002 | Scan          | estimated rows produced | 226595923824   v_vertprd1_node0002 | DataTarget    | input rows              |   5292902400   v_vertprd1_node0002 | DataTarget    | written rows            |            0   v_vertprd1_node0003 | Scan          | estimated rows produced | 226595923824   v_vertprd1_node0003 | DataTarget    | input rows              |   5297440256   v_vertprd1_node0003 | DataTarget    | written rows            |            0  (9 rows)  

This would indicate that I'm roughly 2.5% complete? (each node) or am I way off? Thanks! Shawn

Sharon_Cutter

Shawn, You want to use "rows produced" instead of "estimated rows produced". The "estimated rows produced" correlate to the estimates that you'd see in the EXPLAIN output; the "rows produced" are the actual number which you can compare to the total row count for the table. After all the rows are scanned, the DataTarget operator will be doing a bunch of sorting, during which no rows my be written for a while. You can track progress of the sort by monitoring the "total merge phases" and the "completed merge phases". --Sharon

Shawn_Ferris

Thanks Sharon! I think I posted my gratitude earlier, but now that I recall, I think my browser crashed in the process. Anyway, I do appreciate the answer and it's working great from what I can tell!