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…
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.
This is a source-linked briefing. Read the original report from Dev Community .