Debugging active queries with mid-flight instrumented explain plans
Thursday, October 24 at 09:30–10:20
Have you ever wondered what an active query is doing under the hood? Instrumented explain plans obtained with EXPLAIN ANALYZE are very powerful when investigating bottlenecks in a query, with one small limitation: a query needs to finish in order for the plan with statistics to become visible.
I recently wrote a patch for the already existing in-progress patch "Logging plan of the running query" to also log instrumentation details collected up until the time the plan was logged. This includes rows collected and time spent so far in each plan node.
In this talk I will present an extended/experimental version of that patch where active queries with an enabled flag print the instrumented execution plan to a catalog table in a regular interval and demonstrate how this can help troubleshoot queries that never finish.
