Performance

Lookup sheet for stage 6. The question it exists to answer: which plan-derived count proves this query's shape, and which scan, join or index decision explains it?

Which numbers to trust

EXPLAIN (ANALYZE) prints two families of number, and only one survives a rerun on different hardware. Five runs of select count(*) from orders where customer_id = 4242 with no index gave execution times spread by about eight percent fastest to slowest, while Buffers: shared hit=7608 came back identical every time. A timing is a fact about the machine that produced it at that moment; a plan-derived count is a fact about the work done, reproducible on any copy of the same data.

The EXPLAIN options, and what release 18 changed

This stage's habit for a plan meant to be read is EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF). Release 18 changed EXPLAIN's output in four places a reader comparing against older writing will notice: BUFFERS is automatic under ANALYZE; an index scan reports Index Searches; a per-loop average row count prints fractionally instead of rounding; and more node types report memory and disk figures once limited to a sort or a hash. See Lesson 35.

The plan nodes of this stage

The scan decision

The planner prices every candidate scan against random_page_cost (default 4) and seq_page_cost (default 1): a page read out of physical order costs four times one read in order.

Raising random_page_cost or lowering seq_page_cost moves every crossover toward the sequential scan; the reverse moves them toward the index. See Lesson 36.

The index decisions

The leftmost-prefix rule is a fact about storage order, shown here on two columns rather than three. Sorting by a first puts every row sharing an a next to every other, so a filter on it is one run: find the start, then walk until the value changes. Sorting by a first also scatters the rows sharing a b, so a filter on b alone has no run to find and the walk never starts. The same reasoning is why the rule extends to c, and why (a, b) and (b, a) are different indexes.

See Lesson 38.

Statistics

pg_stats holds what one ANALYZE sampled, and every rows= estimate in every plan is arithmetic on these stored numbers, done before a single row of the query is read.

These are a sample, not a census, so the values differ between ANALYZE runs on unchanged data, unlike a buffer count: one run read customer_id's n_distinct at 78843 against a true 100000, and another run reads a different number. default_statistics_target, 100 by default, sets the sample size and can be raised per column with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS. ANALYZE refreshes all of it; autovacuum queues its own run once more than autovacuum_analyze_threshold (50) plus autovacuum_analyze_scale_factor (0.1) times the row count has changed, about 105042 rows on orders. CREATE STATISTICS ... (dependencies, ndistinct) fixes the one thing a per-column sample cannot see, two columns moving together priced as independent, but only for an equality condition or a column compared to an expression; nothing for a range or a LIKE, so the same misestimate returns the moment = becomes BETWEEN. See Lesson 37.

Join strategies

work_mem binds per node, not per statement: a three-table join can have two Hash nodes at the same setting, one spilling and the other not, by how many rows each side builds from. The enable_hashjoin, enable_mergejoin and enable_nestloop switches force a strategy for investigation only; reset them at once, since each removes a whole class of plan from every later query on the connection. See Lesson 39.

Pagination and counting

OFFSET walks the sort order from the start and discards every row before the one it returns; a keyset condition on the last row already seen starts exactly where the previous page ended. On orders ordered by id descending: the first page reads rows=20.00 at 4 buffers; the same page at OFFSET 500000 reads rows=500020.00 at 4993 buffers for the same twenty rows; the keyset rewrite, WHERE id < ..., is back to rows=20.00 at 7 buffers regardless of depth. OFFSET's cost grows with the offset; keyset's does not. A keyset condition on a non-unique sort column needs the same tiebreaker in WHERE as ORDER BY, compared as a row constructor, (customer_id, id) > (9, 1008), or it silently skips rows sharing the last-seen leading value.

Three honest answers to "how many rows match":

See Lesson 40.

When it is not the query

A query can be perfectly planned and still be slow because the table underneath it, or the client above it, is the real problem. A purpose-built 200,000-row table at 27 MB, fully updated once, grows to 54 MB with every row dead beside its new version. A plain VACUUM marks the dead space reusable, not returned: dead_tuple_percent falls to 0.0 and free_percent rises, but the file stays 54 MB. VACUUM FULL shrinks it back to 27 MB, at the cost of an ACCESS EXCLUSIVE lock for the rewrite. Two views answer "is this the table" with nothing to install: pg_stat_user_tables (n_live_tup, n_dead_tup, last_autovacuum) and pg_stat_all_indexes (idx_scan, for finding an index that costs every write and pays nothing back). pgstattuple needs only CREATE EXTENSION pgstattuple, turning a guess about bloat into a percentage. pg_stat_statements cannot be demonstrated without a server restart: it installs, but querying its view fails at once with ERROR: pg_stat_statements must be loaded via "shared_preload_libraries", SQLSTATE 55000, since the module must load at server start. Beyond the table, three causes never reach the server as a plan worth reading: the same statement run once per row from a client-side loop, a connection opened per request, and a full result set fetched when only a page was shown. See Lesson 41.

Diagnostics and settings