HN Debrief

Looking Forward to Postgres 19: Query Hints

  • Databases
  • Open Source
  • Infrastructure
  • Developer Tools

The post walks through PostgreSQL 19's likely addition of "plan advice," a new mechanism built around pg_plan_advice. Instead of Oracle-style comment hints that directly dictate execution, this design lets you attach advice that only steers the planner toward plans it already considers valid. That constraint is the whole point. It gives operators a way to stabilize bad edge-case queries without turning SQL into hand-authored execution plans. A key detail people highlighted is `EXPLAIN (PLAN_ADVICE)`, which makes the feature look less like a blunt hint system and more like a plan stability framework that can grow over time. The initial version is still narrow. It does not yet solve everything people want, like directly correcting bad row or join estimates, and it deliberately avoids comment parsing in core.

If you run Postgres at scale, pay attention to this feature as a way to stabilize a few pathological queries across upgrades and skewed workloads. Do not treat it as a substitute for statistics, indexing, monitoring, and periodic query review, because stale advice can lock in yesterday's assumptions.

Discussion mood

Mostly positive and relieved. People see plan advice as an overdue escape hatch for rare but painful planner failures, especially after upgrades or on skewed multi-tenant workloads. The caution running through the comments is that hints can calcify into long-term debt and should not replace better statistics, indexing, monitoring, or query redesign.

Key insights

  1. 01

    Plan advice is really about plan stability

    The feature is more interesting as a controlled stability layer than as a generic hint system. `EXPLAIN (PLAN_ADVICE)` lets you discover advice from actual bad plans, and the core design refuses advice that would make planning fail or force an impossible path. That keeps the planner in charge while still giving operators a supported way to pin down pathological cases.

    Use this first for a small set of high-risk queries that regress across releases or data shifts. Build your workflow around inspecting generated advice and removing it when the planner no longer needs help.

      Attribution:
    • lfittl #1 #2
  2. 02

    Hints often paper over bad statistics

    The sharpest warning was that bad plans often come from stale assumptions about data shape, not from the planner being fundamentally broken. Multi-tenant SaaS tables are a prime trap because per-customer distributions can differ wildly, but PostgreSQL already has extended statistics for some multi-column cases. If you jump straight to hints, you can freeze a workaround where better stats or index changes would have fixed the root cause more cleanly.

    Before adding advice, check whether extended statistics, better indexes, or tenant-aware schema changes solve the problem. Advice should come after you have ruled out missing information the planner could have used.

      Attribution:
    • alexpotato #1
    • fabian2k #1
    • tschellenbach #1
  3. 03

    LLMs help diagnose plans better than they rewrite queries

    People are already using models like Claude Opus to read slow query logs, inspect plans, and connect them with telemetry or old PostgreSQL mailing list posts. That works surprisingly well for spotting odd planner limitations and low-hanging fixes. The dangerous part is letting the model rewrite ORM-generated code. Several comments said it often introduces subtle correctness changes, especially in old code with poor tests, even when it produces a much faster plan on sample data.

    Use LLMs as a query-plan analyst, not an autonomous optimizer. Let them explain plans, suggest indexes, and surface known planner quirks, then validate any SQL or ORM rewrite with strong tests and real edge cases.

      Attribution:
    • nijave #1 #2
    • Atotalnoob #1
    • hylaride #1
  4. 04

    Predictable worst-case behavior drives demand

    The demand for plan advice is not only about average performance. It is about bounding bad surprises. Planner changes on upgrade can help most workloads and still wreck one business-critical query because the cost model or cardinality estimate shifted. One commenter connected that directly to why some large systems prefer simpler data stores with more predictable worst-case behavior even when their average performance is lower.

    If your business cannot tolerate sudden latency cliffs, treat planner variance as an operational risk. Add regression tests around critical query plans before upgrades and consider advice for the few queries where worst-case behavior matters more than elegance.

      Attribution:
    • crimsonnoodle58 #1
    • da_chicken #1
    • mpyne #1
  5. 05

    The syntax and extension model may matter more than hints

    Several comments focused less on whether hints are philosophically pure and more on the fact that this design looks readable, composable, and extensible. A commenter pointed out that if you really want Oracle-style comment hints, you can already build that on top via an extension hook instead of baking brittle comment parsing into core. That makes the core feature a safer substrate for multiple plan-control approaches, not a one-off syntax choice.

    Watch the extension ecosystem around PostgreSQL 19, not just core syntax. The long-term value may come from standard hooks that let vendors and teams build specialized plan-control tooling without forking Postgres.

      Attribution:
    • robertlagrant #1
    • cryptonector #1
    • skywhopper #1

Against the grain

  1. 01

    The planner usually beats manual meddling

    One experienced operator said every attempt to force planner behavior made things worse, including LLM-suggested tweaks. In that view, if smart joins and filtering cannot make a workload fast enough, the real problem is the architecture, not the optimizer. For their workloads, memory settings and broader system design mattered more than trying to micromanage plan choice.

    Do not assume a slow query needs planner advice. First ask whether workload shape, refresh strategy, memory settings, or data architecture are the bigger constraint.

      Attribution:
    • throwatdem12311 #1
  2. 02

    Hints are a smell in a declarative language

    A deeper objection was that SQL's value comes from staying declarative. Once users must think about execution strategy, the database is pushing optimization costs back onto application teams and eroding the abstraction that made SQL productive in the first place. The practical concession was that escape hatches are still worth having, but only while maintainers keep pushing toward better optimizers that make manual advice obsolete.

    If you adopt plan advice, treat each use as technical debt to review later. Keep pressure on schema design, statistics quality, and optimizer improvements so the number of hand-tuned queries does not quietly grow.

      Attribution:
    • zackmorris #1

In plain english

cardinality ↩
A way of comparing the sizes of sets, including infinite sets, by asking whether their elements can be paired off one-to-one.
EXPLAIN (PLAN_ADVICE) ↩
A PostgreSQL explain mode that shows what plan advice could be used for a query.
extended statistics ↩
PostgreSQL statistics that capture relationships across multiple columns so the planner can make better estimates.
index ↩
A data structure that helps a database find rows quickly without scanning an entire table.
LLM ↩
Large language model, a machine learning system trained to generate and understand text.
pg_hint_plan ↩
A PostgreSQL extension that adds optimizer hints outside the core database.
pg_plan_advice ↩
A PostgreSQL extension and planned core framework for generating and applying plan advice.
plan advice ↩
PostgreSQL's proposed mechanism for nudging the query planner toward certain plans without forcing impossible ones.
planner ↩
The higher-privilege agent component that decides what steps to take and what tools to call.
Postgres ↩
PostgreSQL, an open source relational database system.
PostgreSQL ↩
A widely used open source relational database system for storing and querying structured data.
SaaS ↩
Software as a Service, software delivered over the internet by a vendor instead of installed and run locally.

Reference links

PostgreSQL design and documentation

Related PostgreSQL extensions and tools

  • pg_hint_plan
    Existing PostgreSQL extension for optimizer hints mentioned as the current workaround people use today.