deniz.in

Markets

Weather

Loading weather

· via dev.to (home feed)

Bad pg_plan_advice hint slowed a PostgreSQL 19 indexed query 9.3x under 10-client load

A dev.to benchmark of PostgreSQL 19 RC2's pg_plan_advice found that forcing a sequential scan cut throughput from 351 to 37.9 tps, and the author could not find a single case where a hint helped.

Bad pg_plan_advice hint slowed a PostgreSQL 19 indexed query 9.3x under 10-client load

A hands-on benchmark published on dev.to has quantified what happens when PostgreSQL 19's new plan-hinting module is given bad advice: under a ten-client load test, a forced sequential scan pushed an indexed query's average latency from 28.5ms to 264.0ms — a 9.3x slowdown — and cut throughput from 351.3 to 37.9 transactions per second.

The module, pg_plan_advice, is a contrib extension in PostgreSQL 19 that accepts a hint string telling the planner which scans, joins and plan shapes to use. The author, posting as alexgeorgiev17 on dev.to, tested the second release candidate, pulled from Docker Hub on 14 November. PostgreSQL 19 has not reached general availability, so behaviour could still change before release.

The setup

The test schema is deliberately ordinary: 50,000 customers joined to 2,000,000 orders, with a skewed status column where 0.5% of orders are marked refunded and an index on that column. Left alone, the planner chose an index scan on orders feeding a hash join against a sequential scan of the small customers table, with three runs of 24.8ms, 16.5ms and 18.0ms — a median of 18.0ms.

pg_plan_advice works in two halves. EXPLAIN (PLAN_ADVICE) prints an advice string that reproduces the plan just chosen, and SET pg_plan_advice.advice feeds a string back in, with feedback on whether the planner honoured it. The vocabulary covers scan methods, join strategies and orders, partitionwise plans and parallel workers.

What the wrong hint cost

Forcing a sequential scan on orders — the one plan the planner had already rejected — raised the single-client median to 46.1ms. Adding a nested-loop hint on top barely moved the needle at 48.9ms: once the join input is a full 2-million-row table rather than roughly 10,000 rows out of an index, the join strategy hardly matters and the scan choice does the damage.

The gap widened sharply under concurrency. In a pgbench run with ten clients over ten seconds, the planner's own plan completed 3,519 transactions at 351.3 tps with 28.5ms average latency, while the forced sequential scan managed 384 transactions at 37.9 tps and 264.0ms. A penalty of roughly 2.6x on one connection grew to 9.3x on ten, because ten simultaneous full-table scans contend for buffer pages and CPU in a way ten index probes do not.

No case where a hint helped

The author set out to find a query the planner gets wrong and a hint fixes, and reports failing to find one in an hour of trying. A schema built with perfectly correlated columns — region and tier both derived from the same id % 3 — did trip up row estimates: a filter known to match 16,666 rows was costed as though it matched about a third of that. But the hash join the planner picked anyway stayed the cheapest option, and no amount of skew produced a genuinely bad default plan. Every advice string either matched what the planner already intended or made things worse.

That matches the module's intended use, according to the author: locking in a plan already known to work, for instance when a connection pooler or a particular bind parameter occasionally pushes the planner toward a poor choice — not correcting bad estimates on the fly.

Silent failure modes

Some advice is refused safely. A hint naming a nonexistent index, or contradictory hints about the same table, both fail with explicit feedback, and the query falls back to the planner's own plan rather than a broken one. The dangerous case is a misspelled table alias: it produces no error or warning, and the query simply runs as if no advice were supplied. A documented setting, pg_plan_advice.feedback_warnings, turns that into a logged warning, but it is off by default — meaning the failure most likely to bite someone is the one you must opt in to hear about.

The documentation also warns that a cached prepared-statement plan ignores a new advice value until DISCARD PLANS runs, which the benchmark reproduced. Undocumented, per the author: EXPLAIN (PLAN_ADVICE) against that stale cached plan shows no advice section at all, so an ignored hint and an absent hint look identical.

There is also a small standing cost. A trivial single-row count averaged 0.457ms with the module loaded on every transaction versus 0.408ms without it, about a 12% difference.

Why it matters

Plan hints have long been among the most requested PostgreSQL features, and pg_plan_advice is the first mainstream mechanism for them inside the server itself. This benchmark shows both edges of the tool: an explicit, reversible way to pin trusted plans, and a straightforward way to degrade an already fast query by nearly an order of magnitude under load. Because the likeliest production failure — advice silently doing nothing after a typo — is invisible by default, anyone experimenting with the module once PostgreSQL 19 ships should treat pg_plan_advice.feedback_warnings as a first toggle, and remember that single-client numbers badly understate what a bad hint costs under real concurrency.

  • #postgresql
  • #databases
  • #query-planner
  • #performance
  • #benchmarks

Related posts