PostgreSQL 19 Will Allow Forcing the Execution Plan with pg_plan_advice

PostgreSQL 19 Will Allow Forcing the Execution Plan with pg_plan_advice

Two extensions announced for PostgreSQL 19, pg_plan_advice and pg_stash_advice, let you lock join, scan, and parallelism behavior when the optimizer gets it wrong. The community resisted hints for years: understand why it gave in and when it's worth using.

Editor's note: The technical description of how pg_plan_advice and pg_stash_advice work in practice (activation syntax, tag list, persistence scopes, degradation behavior, and query ID calculation) comes solely from the Snowflake post republished on Planet PostgreSQL. Since PostgreSQL 19 hasn't been released yet, there is no official PostgreSQL project documentation currently available to verify these details independently. The attribution facts, the publication date, and the performance example numbers cited in the article were confirmed directly from the source.

A Taboo the Community Resisted for Years

The PostgreSQL community has always treated execution plan hints as a hack. The historical argument is simple: if the table statistics are correct, the planner chooses the right path, and a bad plan is treated as a bug to be fixed in the optimizer, not worked around by the user. Those coming from SQL Server or Oracle find this resistance strange, because there, hints have always been part of the everyday toolbox.

In a post published on September 30, 2026 on the Snowflake blog and republished by Planet PostgreSQL (an aggregator of PostgreSQL community blogs), developer advocate Elizabeth Garrett Christensen describes the shift in stance: PostgreSQL 19, expected to launch this fall in the Northern Hemisphere, is set to bring two new contrib extensions, pg_plan_advice and pg_stash_advice. It's worth noting: the version hasn't officially launched yet, and the feature is still in its final stage before release.

One naming detail matters for anyone researching this later: the project avoided the word "hint" and adopted "advice" (in the sense of guidance) in the documentation and function names. It's worth searching for both terms.

How the Two Extensions Work

The pg_plan_advice extension is the execution layer. Activated with LOAD 'pg_plan_advice', it accepts an advice string via SET pg_plan_advice.advice, describing join order, join method, scan type, and parallelism for a specific session or query. RESET pg_plan_advice.advice returns control to the optimizer.

The second extension, pg_stash_advice, solves the persistence problem: it allows storing advice strings associated with a query's identifier (query ID), so they are applied automatically every time that query shape runs, without needing to rewrite the application.

The advice tags cover four aspects of the plan:

• Scan method: SEQ_SCAN, INDEX_SCAN, INDEX_ONLY_SCAN, BITMAP_SCAN, DO_NOT_SCAN
• Join order: JOIN_ORDER, specifying the sequence of tables by their aliases
• Join method: HASH_JOIN, MERGE_JOIN, NESTED_LOOP_PLAIN, NESTED_LOOP_MEMOIZE, NESTED_LOOP_MATERIALIZE
• Parallelism: GATHER, GATHER_MERGE, NO_GATHER

One side feature deserves mention: EXPLAIN (PLAN_ADVICE) now prints the advice string equivalent to the plan that was just generated. In practice, any EXPLAIN becomes an automatic generator of copyable advice, which the DBA can adjust and reapply later.

The Case Where the Optimizer Can't Get It Right

The example given in the post clearly illustrates where the feature makes a difference, and where ANALYZE, CREATE STATISTICS, and indexes don't solve the problem. The Postgres planner can't see what happens inside PL/pgSQL functions, PostGIS operations, or opaque business-rule functions. For a boolean function with no statistics of its own, it assumes, by default, that it matches 33% of rows.

In the example, a compliance function (is_flagged_order) flags only 50 orders out of 1 million, but the planner estimates 333,000 matches and builds hash joins doing full scans of order_items (3 million rows) and customers (100,000 rows). EXPLAIN (ANALYZE, COSTS OFF, PLAN_ADVICE) confirms the problem: the actual plan processes 50 rows, not 333,000, and execution time comes to 1,063 ms.

With the automatically generated advice manually adjusted, forcing JOIN_ORDER(o c oi) with NESTED_LOOP_PLAIN and INDEX_SCAN on the supporting tables, the plan switches to 50 targeted indexed lookups instead of full scans. Execution time drops to 387 ms, about half the original, according to the post's own numbers.

The Priority Order Before Touching Advice

The author is explicit: hints are the last resort, not the first. Before any SET pg_plan_advice.advice, the recommended checklist is:

1. Run ANALYZE to update table statistics
2. Use CREATE STATISTICS to inform the planner of correlations between columns
3. Review and adjust existing indexes
4. Check work_mem and other memory parameters, since resource constraints force inefficient plans

In short: if the problem lies in outdated statistics, a missing index, or insufficient memory, the correct solution is to fix the cause, not mask it with a manually locked plan. Advice exists for the residual case where the database structure simply has no way to inform the planner, such as opaque functions, foreign data wrappers, or external applications that can't be rewritten.

Scope, Degradation, and the Risk of Leaving the Hint Behind

The most sensitive point for anyone operating a database in production is the scope of persisted advice. pg_stash_advice allows applying the hint at three levels: per database (ALTER DATABASE ... SET pg_stash_advice.stash_name), per role (ALTER ROLE ... SET pg_stash_advice.stash_name), or per query identifier, via pg_set_stashed_advice.

The author herself advises against database-level scope, since it indiscriminately affects queries that never had a problem. Query ID scope is pointed out as the only one truly safe for production, because it limits the effect to already-diagnosed queries.

It's worth explaining what this identifier is: Postgres's query ID is the same one used by pg_stat_statements, calculated from the query's shape (tables, joins, clauses), ignoring literal values. In other words, WHERE id = 1 and WHERE id = 99999 generate the same ID, and anyone already using pg_stat_statements to find slow queries can reuse that identifier directly to lock the advice.

One important safety behavior: if the advice becomes inconsistent, pg_plan_advice degrades gracefully and falls back to the planner's default behavior, logging the event when pg_plan_advice.trace_mask is enabled. But the post also warns: if the advice is technically valid and still bad, nothing goes visibly wrong, the plan simply runs worse. It's up to the operator to audit regularly with EXPLAIN.

The Takeaway for Those Running Databases in Production

This feature is not an invitation to hint systematically. It's a targeted escape valve, and the architecture of the two extensions reflects that: they separate the act of testing a hint (pg_plan_advice, per session) from the act of persisting a validated hint (pg_stash_advice, per query ID). This separation is healthy and avoids the classic mistake of those coming from other databases, which is setting a hint and forgetting about it.

The real risk isn't technical, it's operational: advice locked by query ID remains in effect even when data volume changes, value distribution changes, or the Postgres version evolves the optimizer. A plan frozen today can become the worst plan a year from now. If the team adopts pg_stash_advice, it needs to become part of the major upgrade review routine, alongside pg_stat_statements, exactly as is done today with manual indexes and per-workload work_mem settings.