Before You Read the Plan: The Environmental Causes of Slow Queries

When a query that used to take 200 milliseconds starts taking 4 seconds, the usual first move is to copy it into a console and run EXPLAIN ANALYZE. The plan is detailed and concrete, and it looks like the root of the problem. Often it is not. Many slow queries are slow for reasons the plan cannot show: the query was waiting for a lock, the disk was saturated, the table had filled with dead rows, or a sort no longer fit in memory.

Starting with the plan has three practical problems. First, EXPLAIN ANALYZE executes the query, which adds load to a server that is already under pressure. Second, the plan you see now may not be the plan that ran. Statistics, cache contents, settings and parameter values change between the moment the query was slow and the moment you investigate it. Third, a plan describes how the query was executed, not what it was waiting for. It does not record lock waits, I/O pressure from other sessions, or what else was running.

For these reasons, the slow-query specification we wrote for Argyronix orders the investigation by cost. Cheap catalog reads come first, configuration checks come next, and plan analysis comes last, only for what the earlier steps could not explain. This post walks through that order. Argyronix is pre-release, so this is the design, not results from customer systems.

The first question is whether the slow query is doing work or waiting for something. The pg_stat_activity view answers it for every active session: the state, the time the current statement started, and the two columns that matter most here, wait_event_type and wait_event.

If wait_event_type is empty, the backend is running on the CPU, and query-level diagnosis makes sense. If it is Lock, the session is waiting for a heavyweight lock that another session holds. In that case the query is a victim, and its plan is irrelevant: the fix is in the session that holds the lock. The function pg_blocking_pids() returns the process IDs that block a given session. Our specification stops plan diagnosis at this point and hands the case to lock and wait analysis.

If the wait type is IO, LWLock or BufferPin, the session is waiting for storage or for internal shared structures. That points to resource saturation at server level, not to one query. If the wait type is Client, the server is waiting for the application to send the next request or to read the result. The time is real, but it is not spent in the database.

Two cautions apply. A single snapshot can miss short waits, so repeated snapshots give a stronger signal. And pg_stat_activity also lists autovacuum workers. An anti-wraparound vacuum on a large table can compete for I/O for a long time and look like a sudden slowdown of unrelated queries.

The same plan can become slower because the data under it changed. A table full of dead tuples makes every sequential scan read more pages. A bloated index makes every lookup touch more pages. Stale statistics make the planner choose based on row counts that are no longer true. A missing index forces a scan where a lookup would do. We covered these conditions in earlier posts. Each has its own detector and its own fix, so a slow query on an affected table is routed there instead of being diagnosed a second time.

One signal from pg_stat_statements helps connect a query to the data layer: blocks read per row returned, which is shared buffer hits plus reads, divided by rows. A query that touches more than ten blocks for every row it returns is reading far more than it uses. That is a hint, not a diagnosis: it can mean bloat, a poor index or an unindexed filter.

All of these checks are catalog reads from views such as pg_stat_user_tables, pg_stat_user_indexes and pg_stats. They do not scan user tables and do not use pgstattuple. Every diagnostic query runs with a statement_timeout of 5 seconds, so a check cannot become the next slow query.

If the tables are healthy, look at what the query asks of the server. pg_stat_statements records temp_blks_written for each normalized query. A value above zero means that a sort or a hash did not fit in work_mem and was written to disk. The plan can be perfect and still run many times slower than it would in memory.

The fix is not always a larger global work_mem. The setting applies to each sort or hash operation, in each session, so on a server with hundreds of connections a small increase can add up to a large amount of memory. For one heavy report, a per-query SET work_mem is often the safer change. The right option depends on the workload, so this case is routed to configuration analysis.

The second configuration question is the cost model. The default random_page_cost of 4.0 assumes that a random read costs four times as much as a sequential one, which describes spinning disks. On SSD storage the real ratio is much closer to 1, and a planner that still uses 4.0 avoids index scans it should use. Parallel query settings can also be wrong for a workload: generous parallelism helps reporting queries and wastes CPU on a busy transactional system. In our design, server settings are recommendations with the evidence attached. A DBA decides, and they are never changed automatically.

All of this needs a list of queries worth investigating. pg_stat_statements provides it. It aggregates every normalized statement with its call count, total and mean execution time, the standard deviation of that time, rows, block counts and temporary file use. It also replaces literal values with placeholders, so the list does not carry values from WHERE clauses.

The useful ranking is by total execution time, not by mean. A query that takes 120 milliseconds and runs two million times a day costs the server more than a 30-second report that runs once. Our default filter keeps queries with more than 50 calls and a mean above 100 milliseconds. These thresholds depend on the workload: for transactional systems a lower floor of 10 to 50 milliseconds makes sense, and for batch systems a floor of a second or more avoids noise.

One more number deserves attention: the standard deviation of execution time divided by the mean. When that ratio is above 1, the query sometimes runs very differently from its average. Above 2, it is a strong sign that the same SQL runs with different plans or under very different conditions. That is a risk signal for plan instability, one of the cases where the plan itself is needed.

After the environment, the data layer and the configuration are ruled out, some slow queries remain. These are the problems that only a plan can show: the planner estimated 100 rows and got 100,000; it chose a nested loop that ran its inner side thousands of times; a correlated subquery ran once for every outer row; an implicit type cast prevented the use of an existing index; or the plan changed between yesterday and today. Comparing estimated and actual rows for each node is the best first test. A ratio above ten in either direction points to a cardinality problem, and the fix usually belongs to statistics, not to the query.

When you do need a plan, prefer one captured at the moment the query was slow. The auto_explain module can log the plans of statements that run longer than a threshold, and it is available on many managed services. Run EXPLAIN without ANALYZE first, because the estimated plan alone often shows the problem. If you need ANALYZE on a write statement, wrap it in a transaction that you roll back, and remember that it still does the work and takes the locks while it runs.

Ordering the investigation by cost prevents two common mistakes. The first is tuning a query that was only a victim: adding an index or a hint to a statement that was slow because another session held a lock. The second is changing the query when the cause was in the environment: rewriting SQL that was slow because it spilled to disk or because its table had not been vacuumed. Both produce a change that does not help.

Several causes can be present at once. A query can have stale statistics and spill to disk at the same time, so apart from a lock wait, which ends the plan investigation, every applicable check runs, not only the first one that finds something. Cheap and safe checks come first, expensive and intrusive ones last. A plan explains how a query ran. The environment often explains why it was slow at that moment.