DEV Community

Alex Georgiev
Alex Georgiev

Posted on Edited on AI-assisted

PostgreSQL 19's pg_plan_advice turned a 28ms query into 264ms under load

Proves the planner usually beats manual hints

A ten-client pgbench run against a plain indexed lookup averaged 28ms a query. The same run, with one SET command telling PostgreSQL to use a sequential scan instead of the index it already wanted, averaged 264ms. Nothing else changed: same table, same data, same hardware, same query.

That SET command is PostgreSQL 19's new pg_plan_advice, a contrib module that lets you hand the planner a hint string and watch it comply, or tell you exactly why it didn't. PostgreSQL 19 is still at RC2 as I write this, pulled from Docker Hub as postgres:19rc2 on 14 November. It isn't GA yet, so anything here could still shift before release.

I went looking for a case where the hint would help. I didn't find one. What I found instead was a precise, well-documented way to make a healthy query slow, and a feedback system that is more honest about its own limits than most planner-hint tools I've used in other databases.

The setup

I built two tables: 50,000 customers and 2,000,000 orders, with status skewed so that 0.5% of orders are 'refunded' and the rest are 'shipped'. An index on status makes the refunded lookup cheap. This is the kind of table every production database has one of.

CREATE INDEX idx_orders_status ON orders (status);
ANALYZE;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT c.region, count(*), sum(o.amount)
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'refunded'
GROUP BY c.region;
Enter fullscreen mode Exit fullscreen mode

The planner picked an index scan on orders feeding a hash join against a sequential scan of the small customers table. Three repeated runs: 24.8ms (cold), 16.5ms, 18.0ms. That's the baseline for everything below.

pg_plan_advice works in two halves. EXPLAIN (PLAN_ADVICE) tells you the advice string that would reproduce the plan you just got:

 Generated Plan Advice:
   JOIN_ORDER(o c)
   HASH_JOIN(c)
   SEQ_SCAN(c)
   INDEX_SCAN(o public.idx_orders_status)
   NO_GATHER(o c)
Enter fullscreen mode Exit fullscreen mode

And SET pg_plan_advice.advice = '...' feeds a string back in, which the planner then tries to honour, with feedback on how well it managed.

Forcing the wrong plan

I took the advice vocabulary (SEQ_SCAN, INDEX_SCAN, HASH_JOIN, NESTED_LOOP_PLAIN, and a dozen others for merge joins, partitionwise plans, and parallel workers) and used it to force the one plan the planner had already rejected: a sequential scan on orders instead of the index scan.

Setup Single run Median of 3
Planner's own choice (index scan + hash join) 16.5 / 18.0 / 24.8 ms 18.0 ms
Forced SEQ_SCAN(o), join left alone 46.1 / 51.7 / 43.6 ms 46.1 ms
Forced SEQ_SCAN(o) + NESTED_LOOP_PLAIN(o c) 48.9 / 52.9 / 41.3 ms 48.9 ms

Forcing just the scan method cost roughly as much as forcing the scan method and the join method together. The scan choice was doing almost all the work; the join strategy barely mattered once the input was a full table instead of 10,000 rows out of an index.

Under concurrency the gap widened a lot further. I ran both plans through pgbench with 10 clients for 10 seconds:

Plan Transactions Avg latency tps
Planner's own choice 3,519 28.5 ms 351.3
Forced SEQ_SCAN(o) 384 264.0 ms 37.9

A 2.6x penalty on one connection became a 9.3x penalty on ten. Ten sequential scans over a 2-million-row table compete for the same buffer pages and CPU far harder than ten index probes do; the single-client number understates what a bad hint costs you in production.

The opposite reading: I never found a case where it helped

This is the part that mattered most to me. I expected to spend most of my time hunting for a query where PostgreSQL's planner gets it wrong and the hint fixes it. I built a schema where region and tier are perfectly correlated (both derived from the same id % 3), which should make the planner underestimate a combined filter on both columns under the independence assumption it still uses for most predicates.

It did underestimate: a filter I knew matched 16,666 rows was costed as if it would match roughly a third of that. But the plan it picked anyway — a hash join — stayed the fastest option regardless, because the table was small enough that the misestimate never changed which plan was cheapest. I could not get PostgreSQL 19 RC2 to pick a genuinely bad default plan on this schema no matter how I skewed the correlation. Every advice string I supplied either matched what the planner already intended, or made things worse.

That matches what I'd read going in: this is advice for pinning a plan you already trust, under a connection pooler or a parameter that occasionally throws the planner off, not a tool for correcting bad row estimates on the fly. I just hadn't believed it until I'd tried to break it for an hour and failed.

Pinning a plan, then moving the data under it

A reader asked the obvious follow-up: what does a pinned plan cost once the data shifts and the plan goes stale? So I ran it. I took the advice string PostgreSQL generated for the original query (INDEX_SCAN(o public.idx_orders_status), HASH_JOIN(c), NO_GATHER(o c) and the rest), pinned it with SET pg_plan_advice.advice, then updated half of all orders to refunded and ran VACUUM ANALYZE. The unpinned planner switched to a parallel sequential scan. The pinned session kept the index scan. Every line of the supplied advice still reported /* matched */, so nothing warns you the plan is now stale.

Run (avg latency, pgbench -T 10) Planner's own choice Stale pinned plan
Before the shift, 10 clients 18.5 ms 18.7 ms
After the shift, 1 client 224 ms 268 ms
After the shift, 10 clients (3 runs) 896 to 1,574 ms 595 to 755 ms
After CLUSTER, 1 client 414 ms 436 ms
After CLUSTER, 10 clients (3 runs) 847 to 1,465 ms 802 to 3,836 ms

On one connection the stale pin cost about 20%. Under ten clients it won, and for two reasons that have little to do with the hint itself. The pinned advice includes NO_GATHER, so the query stays serial, while the planner's parallel plan put ten clients and their workers on 8 cores at once. And the UPDATE wrote every new refunded row together at the end of the heap, which made the index scan read one contiguous region. After a CLUSTER spread those rows back out, the two plans were within noise of each other. One ten-client run produced a 3.8-second outlier, and this machine had other containers running, so I wouldn't read anything into the spread.

So on this query a stale pin degrades gently, because an equality index scan on a two-value column, read in physical order, ends up doing roughly what a sequential scan does. That's not a general result. A pinned nested loop whose outer side grows by orders of magnitude would be the case to worry about, and I haven't tested that one. What this run does show is that the finding in the section above, that nothing I forced ever beat the planner, stops holding once a pinned plan meets shifted data and parallel workers under concurrency.

What it refuses

Three cases, with the exact feedback PostgreSQL gives:

A nonexistent index:

Supplied Plan Advice:
  INDEX_SCAN(o no_such_index) /* matched, inapplicable, failed */
Enter fullscreen mode Exit fullscreen mode

Contradictory advice on the same table:

Supplied Plan Advice:
  SEQ_SCAN(o) /* matched, conflicting, failed */
  INDEX_SCAN(o idx_orders_status) /* matched, conflicting, failed */
Enter fullscreen mode Exit fullscreen mode

Both of these are refused safely — the query ran on the planner's own chosen plan, not a broken one. The one that worried me was a misspelled or missing table alias:

Supplied Plan Advice:
  SEQ_SCAN(zzz) /* not matched */
Enter fullscreen mode Exit fullscreen mode

No error. No warning. The query just ran as if you'd supplied no advice at all, which is exactly what you'd want if you're testing, and exactly what you don't want if you've just typo'd an alias in a connection pooler's startup script and are now silently not getting the plan you think you configured.

There is a documented fix: pg_plan_advice.feedback_warnings. With it on, the same typo produces a real log line:

WARNING:  supplied plan advice was not enforced
DETAIL:  advice SEQ_SCAN(zzz) feedback is "not matched"
Enter fullscreen mode Exit fullscreen mode

I turned it on and it did exactly what the documentation says. It's off by default, which means the failure mode most likely to bite someone — advice silently doing nothing — is also the one you have to opt into being told about.

The documented caveat, and one it doesn't mention

The docs warn that a cached prepared-statement plan won't notice a new pg_plan_advice.advice value until you run DISCARD PLANS. I reproduced this exactly. I forced plan_cache_mode = force_generic_plan so the first EXECUTE would cache a plan, set the advice to request a sequential scan, executed once (got the seq scan, as asked), then changed the advice to request the index scan and executed again:

-- exec 2, advice changed to INDEX_SCAN, plan still cached from before
Finalize Aggregate
  ->  Gather
        ->  Partial Aggregate
              ->  Parallel Seq Scan on orders
Enter fullscreen mode Exit fullscreen mode

Still the seq scan. Only after DISCARD PLANS did the next execution switch to the index scan. What the docs don't mention: EXPLAIN (PLAN_ADVICE) against that stale cached execution showed no Supplied Plan Advice section at all, because no replanning happened. If you're checking whether your new advice took effect by looking for that section, its absence looks identical to "nothing was supplied," not "your advice was ignored." I'd have assumed I'd made a typo before I'd have suspected caching.

What it costs when idle

With the module loaded fresh via LOAD on every transaction, a trivial single-row count ran at 0.457ms average latency against 0.408ms with the module absent entirely — a real but small 12% tax, and it's the tax of calling LOAD every time, not the module's planning hook. Preloaded once via shared_preload_libraries, the same query ran at 0.377ms, indistinguishable from not having the module at all. If you're going to use this in production, preload it; don't LOAD it per connection.

What I got wrong on the way

I spent close to half my testing time chasing a theory that turned out false: that a prepared statement with a parameter on the skewed status column would flip to a bad generic plan after Postgres's usual five-execution custom-plan threshold, because I assumed the generic plan would use a naive uniform-selectivity estimate for an unbound parameter. It didn't. PostgreSQL's planner already uses the column's most-common-value statistics when building the generic plan, so it picked the index scan on execution six exactly as it had on execution one — 9.99ms down to 3.3ms, with no flip at all. The "advice fixes a plan regression" post I'd half-planned to write wasn't there. The honest finding was that the planner didn't need fixing, and the correlated-columns schema in the section above was my second attempt at the same thing, which also failed to produce a bad default plan.

Run it yourself

This needs Docker and about ten minutes.

docker run -d --name pg19 -e POSTGRES_PASSWORD=test postgres:19rc2
sleep 6

cat > runit.sql <<'SQL'
CREATE TABLE customers (id bigint PRIMARY KEY, region text, tier text);
CREATE TABLE orders (id bigint PRIMARY KEY, customer_id bigint REFERENCES customers(id),
                      status text NOT NULL, amount numeric NOT NULL);
INSERT INTO customers SELECT g, (ARRAY['eu','us','apac'])[1+g%3], (ARRAY['gold','silver','bronze'])[1+g%3]
  FROM generate_series(1,50000) g;
INSERT INTO orders SELECT g, 1+(g%50000),
  CASE WHEN g % 200 = 0 THEN 'refunded' ELSE 'shipped' END, (random()*500)::numeric(10,2)
  FROM generate_series(1,2000000) g;
CREATE INDEX idx_orders_status ON orders (status);
ANALYZE;

LOAD 'pg_plan_advice';

-- the planner's own choice
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT c.region, count(*), sum(o.amount) FROM orders o JOIN customers c
  ON o.customer_id = c.id WHERE o.status = 'refunded' GROUP BY c.region;

-- force a sequential scan instead
SET pg_plan_advice.advice = 'SEQ_SCAN(o)';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, PLAN_ADVICE)
SELECT c.region, count(*), sum(o.amount) FROM orders o JOIN customers c
  ON o.customer_id = c.id WHERE o.status = 'refunded' GROUP BY c.region;
SQL

docker cp runit.sql pg19:/tmp/runit.sql
docker exec pg19 psql -U postgres -f /tmp/runit.sql
Enter fullscreen mode Exit fullscreen mode

For the concurrency numbers, swap the last EXPLAIN block for a pgbench custom script with the same SET line in it and run pgbench -U postgres -n -c 10 -T 10 -f script.sql postgres against both the advised and un-advised versions.

Run your own forced-scan test through pgbench with at least ten clients before you pin a plan anywhere permanent, such as a pooler's startup script or postgresql.conf. My single-connection number was 2.6x. My ten-client number was 9.3x. Production traffic looks like the second number, not the first, and I almost stopped testing before I found that out.

Top comments (5)

Collapse
 
aheadkit profile image
AheadKit •

I think the headline gives the module blame that belongs to the forced plan. The 264ms number is ten concurrent sequential scans over 2 million rows — any hint mechanism that forced the same scan would print the same 264ms, pg_hint_plan included. pg_plan_advice did not make the query slow; it obeyed an instruction to be slow, and its feedback even told you so.

The more interesting claim in your piece is the other one, that you never found a case where it helped — but the setup never gave it a fair chance to help. Every run compared the planner's own plan against a deliberately worse one. The case the feature exists for is the opposite: take the plan the planner already chose (your EXPLAIN (PLAN_ADVICE) output), pin it, then change the statistics or the version underneath it and check the plan does not move. You tested forcing; you did not test pinning. I would bet the honest headline is 'a forced seq scan costs 9.3x under load', which is a true and useful number — it just is not an indictment of the advice mechanism.

Collapse
 
alexgeorgiev17 profile image
Alex Georgiev •

that's fair, and the post kind of admits it without following through. I wrote that this is a tool for pinning a plan you already trust, then only ever tested forcing one I didn't. the 264ms is what a seq scan costs under ten clients, and pg_hint_plan would print the same number. the module did what it was told and the feedback said so.

the pinning test is the one I should have run. take the generated advice (INDEX_SCAN(o idx_orders_status), HASH_JOIN(c) and the rest), pin it, then shift the data under it, say push refunded from 0.5% to 40% and ANALYZE, so the planner would rather seq scan on its own. two things to check: does the plan stay put, and what does it cost once the pinned plan is the wrong one. that second number matters as much as the first, since pinning trades a surprise plan change for a plan that can go stale. I'll run it and update the post, and I'll fix the headline so it says what I actually measured.

Collapse
 
aheadkit profile image
AheadKit •

The pinning test is the one the post was missing — pin the advice, shift the data, see if the plan stays put. The second number is the one I'm most curious about, what the wrong pinned plan costs once the data moves under it. That's the real trade, a surprise plan change against a plan that goes stale quietly. Let me know what it prints.

Thread Thread
 
alexgeorgiev17 profile image
Alex Georgiev •

ran it, and it didn't land where I expected.

same setup as the post (PG19 RC2, 2M orders, 0.5% refunded). I pinned the planner's own generated advice (INDEX_SCAN on idx_orders_status, HASH_JOIN, NO_GATHER), then updated half the rows to 'refunded' and re-analyzed. ten clients, pgbench -T 10, avg latency:

before the shift: unpinned 18.5ms, pinned 18.7ms (same plan, as expected)
after the shift, single client: planner switches to a parallel seq scan at 224ms, the stale pinned index scan is 268ms
after the shift, ten clients: planner's plan 896 to 1574ms across three runs, pinned 595 to 755ms

so under load the stale pin actually won. two reasons, as far as I can tell. the pin carries NO_GATHER, so it stays serial while the planner's parallel plan oversubscribes 8 cores with 10 clients. and the UPDATE put all the new refunded rows together at the end of the heap, which flatters the index scan. after a CLUSTER to spread them back out it's basically a tie (single client 414 vs 436ms, ten clients within noise, and one run had a big outlier on both sides).

honest takeaway: on this query a stale pin degrades gently, because an equality index scan on a two-value column in TID order ends up close to a seq scan anyway. it's not the quiet cliff I was looking for. a pinned nested loop whose outer side grows would probably be the one that really hurts, that's the next thing to try.

Thread Thread
 
aheadkit profile image
AheadKit •

That's a better result than the cliff I was expecting — the stale pin winning under load because NO_GATHER keeps it serial is the kind of detail that only shows up when you actually run it. And the heap clustering flattering the index scan, that's a trap I'd have walked straight into. The nested loop with a growing outer side sounds like the real test — if that one degrades gently too, pinning starts looking a lot safer than I assumed. Let me know what it prints.