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;
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)
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.
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 */
Contradictory advice on the same table:
Supplied Plan Advice:
SEQ_SCAN(o) /* matched, conflicting, failed */
INDEX_SCAN(o idx_orders_status) /* matched, conflicting, failed */
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 */
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"
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
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
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 (0)