SQL Query Optimization: A Measured PostgreSQL EXPLAIN Lab

Optimize a SQL query by proving its result, measuring its work, making one targeted change, and checking the result again. This PostgreSQL 16 lab compares a date-cast predicate with an equivalent half-open timestamp range on a normal timestamp index. It demonstrates a diagnostic process, not a universal speedup or a promise that the planner will choose one fixed node.

Use a disposable, bounded experiment

Run the blocks in order in one isolated learning session. The fixture creates a temporary table of 10,000 synthetic rows, a temporary-table index, and statistics. The final ROLLBACK removes them. It does not install an extension, change a service, or touch an application table. EXPLAIN ANALYZE actually executes its statement; never treat it as a harmless plan-only wrapper for arbitrary writes or side-effecting functions.

Build a known distribution

BEGIN;
CREATE TEMP TABLE performance_demo(id integer PRIMARY KEY,occurred_at timestamp NOT NULL) ON COMMIT DROP;
INSERT INTO performance_demo
SELECT i,TIMESTAMP '2026-01-01'+(i/100)*INTERVAL '1 day'
FROM generate_series(1,10000) AS g(i);
CREATE INDEX performance_demo_occurred_idx ON performance_demo(occurred_at);
ANALYZE performance_demo;

Integer division groups IDs into days. March 1 is day offset 59, so its rows are IDs 5900 through 5999 inclusive: exactly 100 rows with id sum 594950. The plain timestamp type uses a stated UTC-clock convention here. A timestamptz column and a local business day need correctly converted instant bounds, especially near daylight-saving changes.

Verify equivalent results before reading plans

SELECT COUNT(*) AS rows,SUM(id) AS id_sum FROM pg_temp.performance_demo WHERE occurred_at::date=DATE '2026-03-01';

SELECT COUNT(*) AS rows,SUM(id) AS id_sum FROM pg_temp.performance_demo
WHERE occurred_at>=TIMESTAMP '2026-03-01 00:00:00'
  AND occurred_at<TIMESTAMP '2026-03-02 00:00:00';

Both statements return:
rows | id_sum
100 | 594950

The cast form asks for the date derived from each timestamp. The range directly constrains the indexed timestamp values. For this type and boundary contract they select the same rows. Do not rewrite predicates on a different date/time type without checking equivalence. A query that becomes faster by dropping boundary rows is incorrect.

Compare actual work, not a copied timing number

Now collect the executed plans for the same bounded SELECT statements:

EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) SELECT COUNT(*) AS rows,SUM(id) AS id_sum FROM pg_temp.performance_demo WHERE occurred_at::date=DATE '2026-03-01';

EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) SELECT COUNT(*) AS rows,SUM(id) AS id_sum FROM pg_temp.performance_demo
WHERE occurred_at>=TIMESTAMP '2026-03-01 00:00:00'
  AND occurred_at<TIMESTAMP '2026-03-02 00:00:00';

The exact JSON plan, cost, time, and buffer counts vary by PostgreSQL settings, statistics, machine, and cache state. Inspect the scan’s Index Cond versus Filter, estimated versus actual rows, rows removed, loop counts, sort work, and buffer activity. The aggregate emits one row even though its scan must identify 100 qualifying rows; distinguish the two levels.

The unmodified timestamp index gives the range predicate a direct searchable condition. A function on the column can prevent that direct condition for this index, but it does not mean functions can never use indexes: a matching expression index is a separate option. Do not force index use or disable sequential scans to make the demonstration appear successful. A sequential scan is reasonable for some small or broad workloads.

Record a reproducible comparison

Run each variant several times under comparable conditions after the initial run, record the order, and report both results and variability. A warm buffer hit is not the same as a physical read. Planner cost units are not milliseconds, and an EXPLAIN ANALYZE run includes measurement overhead. For a production optimization, also measure concurrency, input skew, write cost, and tail latency rather than extrapolating from this small fixture.

Change one element at a time. Keep the baseline SQL, exact parameters, row count or stronger result comparison, engine version, plan, and workload description. If estimates are badly wrong, investigate data distribution and statistics before assuming the join algorithm is defective. ANALYZE can improve information available to the planner; it does not guarantee a chosen plan or faster execution.

Choose the next technique only for an observed problem

An index can reduce selected-row lookup work but adds storage and maintenance to writes. Pre-aggregating independent fact tables can fix both inflated results and excess join work; it is not equivalent to adding DISTINCT after a bad join. A materialized summary can help repeated reports only with an acceptable freshness, refresh, and late-arrival policy.

Partition pruning requires predicates that can exclude partitions; partitioning is not automatically cross-server sharding. More parallel workers can consume more resources and may hurt small queries. Ordinary views do not store cached results. PostgreSQL core does not accept every other engine’s optimizer hints, and a subquery is not automatically slower than a join. Compare semantically equivalent alternatives.

Remove the fixture

ROLLBACK;

After rollback, this session’s fixture table and its index are gone. Do not adapt this cleanup into deleting a permanent table. If a statement failed and left the transaction aborted, ROLLBACK is also the explicit way to end that failed fixture transaction.

A useful optimization report is small and concrete

Report the original bottleneck, the exact query change, result-equivalence evidence, measured workload, and any new cost or freshness assumption. For this lab, the stable correctness claim is 100 rows and id sum 594950 for both predicates. Its plan comparison teaches how to gather evidence; it does not establish a production latency result. See PostgreSQL EXPLAIN and expression indexes.

FAQ

Does EXPLAIN execute the query? Plain EXPLAIN plans it; EXPLAIN ANALYZE executes it. Is every index scan better? No; selection size and workload matter. Should I change memory settings first? First identify the operation and evidence that its resource limit is the actual bottleneck. Can a faster query return fewer rows? Only if the requirement changed; otherwise that is a regression.