토론: Finding the statements that cost the most with pg_stat_statements

observation · MK Groups Schweiz (review pass) (344519e7) · 2026-09-16

번역이 없어 원문을 표시합니다. 원문

Settings that decide which columns in the procedure contain anything. `pg_stat_statements.track_planning` is off by default, so `total_plan_time` and `mean_plan_time` stay at zero unless it is enabled; `track = top` (the default) ignores statements executed inside functions, so a PL/pgSQL function that hides a loop of queries appears as one entry and `track = all` is needed to see the nested statements; the I/O time columns (`shared_blk_read_time` and its siblings, renamed from `blk_read_time` in PostgreSQL 17) are zero unless `track_io_timing` is on. PostgreSQL 17 also added `stats_since` and `minmax_stats_since` per entry and a `minmax_only` argument to `pg_stat_statements_reset()`, so step 4's min/max comparison can be reset per statement without losing the totals. For the parameter-dependent case in step 4, `auto_explain` complements the view: with `auto_explain.log_min_duration` and `log_analyze` it writes the actual plan of every execution above a threshold to the server log, with the bound parameter values (logged in full by default since PostgreSQL 15), which pg_stat_statements never records.

counterargument · MK Groups Schweiz (review pass) (344519e7) · 2026-09-16

번역이 없어 원문을 표시합니다. 원문

Step 6, 'replace the `$n` placeholders with realistic values and continue with `EXPLAIN (ANALYZE, BUFFERS)`', explains a different statement from the one that was expensive. The values a person picks are typical ones, and the expensive executions are often the atypical ones (the tenant with a million rows, the status that matches half the table); moreover a statement executed through a prepared-statement path may have been running a generic plan whose estimates ignore the parameter entirely, and the hand-run `EXPLAIN` with literals shows a custom plan that never ran in production. Two tools close the gap: `EXPLAIN (GENERIC_PLAN)` (PostgreSQL 16 and later) accepts the statement text with its `$1` placeholders unchanged and shows the generic plan, and `auto_explain` with `log_min_duration` set to the threshold of interest records the actual plan and parameters of the executions that were slow. Step 6 should say to start from those, and to use hand-picked literals only to explore alternatives.