Slow Query Checklist

A Checklist for Diagnosing a Slow Analytical Query

Read the plan before changing anything: EXPLAIN (ANALYZE) in PostgreSQL 1,289 (which reports buffers by default since version 18), EXPLAIN ANALYZE or profiling in DuckDB 61,228 (DuckDB: In-Process OLAP), EXPLAIN indexes = 1 in ClickHouse 29,491 (Tables and ORDER BY Keys), the query profile in a cloud warehouse. Then work through the usual causes in order of cost:

Five common causes of a slow analytical query
Check	Symptom in the plan	Fix
Reads too much	Seq scan, rows removed by filter	Range predicates, partitions, BRIN, sort keys
Wrong row estimates	Estimated rows far from actual	ANALYZE, extended statistics
Spills to disk	Sort or hash "Disk:", temp buffers	More work_mem, fewer columns, pre-aggregate
Join explodes rows	Join output far above inputs	Check the grain (Fact Tables and Grain)
Same work repeated	Identical heavy queries per dashboard	Pre-aggregate or cache (Pre-Aggregation and Caching)

The first row is the most common, and often self-inflicted. Wrapping a column in a function hides it from the index and from partition pruning; the same filter written as a range on the bare column does not:

4151-sargable.sql: one filter, written two ways, on 1.38 million rowsSQL

CREATE INDEX sales_x10_date ON mart.sales_x10 (order_date);
ANALYZE mart.sales_x10;
EXPLAIN (ANALYZE, COSTS OFF)                     -- a function hides the column from the index
SELECT sum(gross_amount) FROM mart.sales_x10
WHERE date_trunc('month', order_date) = '2026-03-01';
EXPLAIN (ANALYZE, COSTS OFF)                     -- the same filter as a range on the column
SELECT sum(gross_amount) FROM mart.sales_x10
WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01';
DROP INDEX mart.sales_x10_date;

Output

 Finalize Aggregate (actual time=367.526..383.416 rows=1.00 loops=1)
   Buffers: shared hit=11013 read=12689
...
                     Rows Removed by Filter: 433537
 Execution Time: 383.468 ms
...
   Buffers: shared hit=729 read=702
         ->  Bitmap Index Scan on sales_x10_date (actual time=2.208..2.208 rows=78830.00
           loops=1)
 Execution Time: 70.403 ms

The function form read 23,702 buffers and discarded 94% of the rows; the range read 1,431 through the index and ran 5.4 times faster (9.2 in a second run). Keep the column bare in filters (a sargable predicate) and compute on the constant side.

MENU