--- title: "How to Tell Whether Your Slow Query Is a Planning or an Execution Problem" published: 2026-08-21T11:47:50.000-04:00 updated: 2026-08-21T12:04:40.000-04:00 excerpt: "How to tell if your slow Postgres query is a planning or execution problem: read EXPLAIN ANALYZE's Planning Time and Execution Time to diagnose the bottleneck." tags: PostgreSQL Performance, PostgreSQL Tips authors: NanoHertz Communications --- > **TimescaleDB is now Tiger Data.** Every `EXPLAIN ANALYZE` prints two numbers at the bottom, and most engineers read only one of them. `Planning Time` is how long Postgres spent deciding how to answer your query, while `Execution Time` is how long it spent actually answering it. When latency climbs, those two numbers point to opposite fixes: plan caching and fewer partitions on one side, memory and storage layout on the other. Getting this backwards is expensive. On a table with 500 daily partitions, planning a simple time-range aggregate takes nine times longer than running it, and the reflex to add another index makes the problem worse. Every new index adds a path the planner has to consider, plus write amplification on a table that was already write-bound. You’ll end up spending weeks optimizing the phase that wasn't slow. The good news: Postgres tells you which phase is slow, for free, in one command. Every plan below is real output from PostgreSQL 16.13 against this table, partitioned into 500 daily chunks named `device_metrics_YYYYMMDD` and holding 2.1 billion rows, with `work_mem` at 4 MB and `shared_buffers` at 256 MB: ```SQL CREATE TABLE device_metrics ( ts timestamptz NOT NULL, device_id bigint NOT NULL, metric text NOT NULL, value double precision, rack int, -- one rack per 100 devices region int -- one region per 500 racks ) PARTITION BY RANGE (ts); ``` `rack` and `region` demonstrate correlated columns in Step 3. Substitute your own table and partition names. ## What you will learn - How planning time and execution time differ, and why each has a different fix. - How to [read EXPLAIN](https://www.tigerdata.com/learn/explaining-postgresql-explain) `(ANALYZE,BUFFERS)` to attribute wall-clock time to a phase. - The signature of a planning problem: high plan time, hundreds of pruned partitions, a nearly idle scan. - The signature of an execution problem: spills to disk, heavy buffer reads, filters that discard most of what they touch. - The exact commands to run once you know which one you have. ## Two clocks, two fixes The planner runs a full optimization pass on every query it hasn't cached a plan for. It enumerates access paths, pulls cardinality estimates out of `pg_statistic`, evaluates join orders, and prices each candidate. For a warehouse query with eight joins, that work pays for itself many times over. Partitioned tables change the math. Before the planner can exclude a partition, it locks the relation, loads its `relcache` entry, and builds planner state for it. That setup runs for every partition, whether or not pruning later discards it, so the cost scales with [how many partitions exist](https://www.tigerdata.com/blog/hidden-costs-table-partitioning-scale) rather than how many survive. The [PostgreSQL documentation](https://www.postgresql.org/docs/current/ddl-partitioning.html) is direct about it: "Planning times become longer and memory consumption becomes higher when more partitions remain after the planner performs partition pruning." Execution is a different animal. That time goes to reading heap pages, hashing, sorting, and aggregating. It grows with how much data the plan touches, not with how many plans were considered. One clock measures the decision. The other measures the work. ## Step 1: Read the split Run the query below with both flags. `ANALYZE` executes it and reports real timings. BUFFERS reports how many 8 KB blocks each phase touched, including the planner. ```SQL EXPLAIN (ANALYZE, BUFFERS) SELECT device_id, avg(value) FROM device_metrics WHERE ts > now() - interval '5 minutes' GROUP BY device_id; ``` You may see output like the following: ```Markdown HashAggregate (cost=5510.70..5632.72 rows=9762 width=16) (actual time=6.311..6.696 rows=2202 loops=1) Group Key: device_metrics.device_id Batches: 1 Memory Usage: 913kB Buffers: shared hit=932 -> Append (cost=0.28..5461.89 rows=9762 width=16) (actual time=0.775..4.570 rows=9498 loops=1) Subplans Removed: 499 -> Bitmap Heap Scan on device_metrics_20260729 (cost=212.21..1272.31 rows=9263 width=16) (actual time=0.774..3.796 rows=9498 loops=1) Recheck Cond: (ts > (now() - '00:05:00'::interval)) Heap Blocks: exact=898 Buffers: shared hit=932 Planning: Buffers: shared hit=20063 Planning Time: 62.913 ms Execution Time: 7.202 ms ``` Read the last two lines first. Execution took 7.2 ms. Planning took 62.9 ms. Postgres spent nine times longer choosing a plan than running one, and the plan it chose was excellent: pruning left one partition out of 500, the bitmap scan hit cache on every block, and the row estimate was within three percent. Now read the Buffers lines against each other. This is the part people miss. Execution touched 932 blocks. Planning touched 20,063. The planner read 21 times more pages than the query did, and it read them to open and price 500 partitions before discarding 499 of them. Nothing here is fixable with an index. This is a planning problem. One note on how pruning reports itself. `Subplans Removed` appears when Postgres prunes at run time, which is what happens with a cached generic plan or a stable expression like `now()`. When pruning happens at plan time, excluded partitions never appear in the output at all, so you count the ones that survived. ## Step 2: Learn the Other Signature Now the contrast: a different query against a single partition, where every number moves the other way: ```SQL EXPLAIN (ANALYZE, BUFFERS) SELECT device_id, avg(value) AS avg_value FROM device_metrics_20260715 WHERE value > 0.5 GROUP BY device_id ORDER BY avg_value DESC LIMIT 20; ``` After running the query, you may see: ```Markdown Limit (actual time=1658.814..1658.820 rows=20 loops=1) -> Sort (cost=257079.27..258179.28 rows=440004 width=16) (actual time=1649.815..1649.818 rows=20 loops=1) Sort Key: (avg(value)) DESC Sort Method: top-N heapsort Memory: 26kB -> HashAggregate (cost=219176.70..245370.92 rows=440004 width=16) (actual time=1189.615..1593.002 rows=492515 loops=1) Group Key: device_id Planned Partitions: 16 Batches: 17 Memory Usage: 8337kB Disk Usage: 78856kB Buffers: shared hit=13212 read=17686, temp read=8165 written=9857 -> Seq Scan on device_metrics_20260715 (cost=0.00..83422.95 rows=2119083 width=16) (actual time=0.098..410.231 rows=2102555 loops=1) Filter: (value > '0.5'::double precision) Rows Removed by Filter: 2099445 Buffers: shared hit=13212 read=17686 Planning: Buffers: shared hit=103 Planning Time: 0.505 ms Execution Time: 1682.995 ms ``` Planning is a rounding error: 0.5 ms against 1,683 ms of execution. All the time sits in the nodes, and three lines say where. Buffers: shared read=17686 means 17,686 blocks came off disk, roughly 138 MB, against 13,212 cache hits. Batches: 17 with Disk Usage: 78856kB means the hash aggregate outgrew `work_mem` and spilled 77 MB to temporary files. Rows Removed by Filter: 2099445 means the scan read 4.2 million rows to keep 2.1 million. Here's the part worth sitting with: the estimates were good. The planner predicted 2,119,083 rows from the scan and got 2,102,555. It predicted 440,004 groups and got 492,515. Both within 12 percent. This plan is slow despite being correctly planned, which means `ANALYZE` will do nothing for it. The fix is [`work_mem`](https://www.tigerdata.com/learn/postgresql-performance-tuning-key-parameters) or a storage layout that doesn't read four million rows to answer a question about two columns. A correct plan can still be a slow plan. That's why you read the split before touching anything. ## Step 3: When the Estimates Are the Problem Sometimes they are wrong, and the damage compounds because every choice downstream inherits the error. Divide estimated `rows=` by actual at each node and find the first divergence. ```Markdown Seq Scan on device_metrics_20260715 (cost=0.00..132989.21 rows=84 width=0) (actual time=4.128..350.008 rows=832 loops=1) Filter: ((region = 3) AND (rack = 1750)) ``` Eighty-four predicted, 832 returned. The planner treated region and rack as independent and multiplied their selectivities, but every rack belongs to exactly one region. When two columns carry a functional dependency, tell Postgres: ```SQL CREATE STATISTICS dm_region_rack (dependencies) ON region, rack FROM device_metrics_20260715; ANALYZE device_metrics_20260715; ``` The same scan afterward: ```Markdown Seq Scan on device_metrics_20260715 (cost=0.00..133489.25 rows=841 width=0) (actual time=4.167..352.177 rows=832 loops=1) ``` 841 predicted against 832 actual. One caveat that costs people an afternoon: `dependencies` statistics only apply to equality predicates. Rewrite the same filter as rack `BETWEEN 1700 AND 1799` and the estimate collapses back to 1, because range predicates fall outside what functional `dependencies` model. Reach for `ndistinct` or an expression index there instead. If estimates stay wrong after `ANALYZE`, raise the sample size. The default `default_statistics_target` of 100 samples roughly 30,000 rows, which is thin on a billion-row table. The new target changes nothing until the next `ANALYZE`, so run both: ```SQL ALTER TABLE device_metrics ALTER COLUMN device_id SET STATISTICS 500; ANALYZE device_metrics; ``` ## Step 4: Fix the Planning Side Execution fixes are familiar territory. Planning fixes are the ones teams skip, so here's the one that matters. A prepared statement caches the plan across executions. Prepare it once: ```SQL DEALLOCATE ALL; PREPARE recent_avg AS SELECT device_id, avg(value) FROM device_metrics WHERE ts > now() - interval '5 minutes' GROUP BY device_id ``` Then execute it several times on that same connection: ```SQL EXPLAIN (ANALYZE) EXECUTE recent_avg; ``` Prepared statements live and die with the session, so `PREPARE` and `EXECUTE` must share a connection. Running them through separate `psql -c` calls, or through a pooler in transaction mode, returns `prepared statement "recent_avg" does not exist`. Measured that way, `Planning Time` was 75.697 ms, then 0.179 ms, then 0.087 ms and stayed there. Execution held steady near 3 ms. The first call pays for the plan and every call after it inherits one, which turns a 79 ms query into a 3 ms query without touching an index. A statement with no parameters gets a generic plan immediately, because there is nothing to specialize on. With parameters, Postgres builds custom plans for the first five executions, then compares the generic plan's cost against the custom average and switches if it holds up. Force the decision with `plan_cache_mode = force_generic_plan`. Verify by re-running `EXPLAIN (ANALYZE) EXECUTE` and watching `Planning Time` collapse. If it collapses and total latency doesn't move, planning was never your problem. Caching hides the cost rather than removing it, so look at partition count too. If a typical query touches one partition and the planner starts from 500, [the chunk interval is finer than the workload needs](https://www.tigerdata.com/learn/determining-optimal-postgres-partition-size). Rolling data older than a month into weekly partitions cuts the count by roughly 7x. To find which queries deserve this treatment across the whole workload, [rank them in `pg_stat_statements`](https://www.tigerdata.com/blog/what-pg_stat_statements-actually-tells-you-about-your-queries) with `pg_stat_statements.track_planning` enabled, then sort by `total_plan_time` against `total_exec_time`. ## When Planning Time Is the Architecture Talking A query that spends most of its life being planned is [not a tuning failure](https://www.tigerdata.com/blog/six-signs-postgres-tuning-wont-fix-performance-problems). It's the shape of the workload pressing against a general-purpose planner. Postgres opens and prices every partition separately because it was built for schemas where each table might hold something different. Time-based chunks all hold the same thing, so most of those 20,063 buffer reads are waste. Tiger Data attacks this at the source. Hypertables record chunk time ranges in a catalog table, so [chunk exclusion](https://www.tigerdata.com/blog/boost-postgres-performance-by-7x-with-chunk-skipping-indexes) resolves which chunks a query needs before standard planning begins. The cost tracks the chunks a query matches rather than the chunks that exist. Continuous aggregates go further: a dashboard query hits a small incrementally-updated rollup instead of planning a scan across billions of raw rows. The 62.9 ms in Step 1 is precisely the overhead those layers exist to remove, and [Tiger Data’s whitepaper](https://www.tigerdata.com/docs/learn/deep-dive/whitepaper) covers how. Before you add another index, run `EXPLAIN (ANALYZE, BUFFERS)` on your slowest query and read the last two lines. If planning wins, you've been optimizing the wrong clock. Start a [Tiger Data free trial](https://console.cloud.timescale.com/signup) today to use the right architecture to fix your slow query for good.