auto_explain: Capture the Real Plan of a Slow Query in Production
Knowing a statement was slow is only half the story; you want the plan it used at the time, which may differ from the plan you get when you run it yourself later. The auto_explain module logs the execution plan of any statement over a duration threshold, optionally with analyze, buffers and timing turned on. It is the only reliable way to see a plan that happened at 3 a.m. with production parameters and a cold cache. The cost is overhead, so the timing options are usually reserved for a sampled fraction of statements.
Questions this Concept answers
Why is the plan captured at the time of a slow execution worth more than one you obtain by running the statement yourself later?
J
jeremy
Video
Logging Slow Query Plans with auto_explain and Its Overhead in PostgreSQL
auto_explain is a PostgreSQL contrib extension that captures the actual execution plan of statements exceeding a configured duration threshold and writes it to the server log, making it the only reli…