pg_advice_comment - A little helper for pg_plan_advice to set advice from hint-style comments

Supply pg_plan_advice plan advice from a comment embedded in the query.

pg_advice_comment registers an advisor with pg_plan_advice. Before a query is planned, it scans the raw query text for a leading hint-style comment:

/*+ SEQ_SCAN(foo) JOIN_ORDER(foo bar) */
SELECT * FROM foo JOIN bar USING (id);

The text found between /*+ and */ is passed to pg_plan_advice, which parses it with the same parser it uses for the pg_plan_advice.advice setting.

There are no GUCs and no SQL-callable functions: the module only utilizes the pg_plan_advice_advisor_hook hook mechanism.

Requirements

• PostgreSQL 19 or newer (pg_plan_advice is a PostgreSQL 19 feature).
• pg_plan_advice must be available and loaded in shared_preload_librares
• flex is required at build time (the comment scanner is generated from query_scan.l).

Build and install

pg_plan_advice ships with PostgreSQL 19; build pg_advice_comment against the same installation:

make PG_CONFIG=/path/to/pg_config
make install PG_CONFIG=/path/to/pg_config

Usage

Load both modules via shared_preload_libraries and restart:

shared_preload_libraries = 'pg_plan_advice,pg_advice_comment'

Then prefix any query with an advice comment:

/*+ SEQ_SCAN(t) */ SELECT * FROM t WHERE id = 42;

Use EXPLAIN (PLAN_ADVICE) to see the advice that was supplied and how it was applied:

/*+ SEQ_SCAN(t) */ EXPLAIN (PLAN_ADVICE) SELECT * FROM t WHERE id = 42;

 Seq Scan on t
   Filter: (id = 42)
 Supplied Plan Advice:
   SEQ_SCAN(t) /* matched */
 ...

If a query has no /*+ ... */ comment, this module supplies nothing and the planner (or the pg_plan_advice.advice GUC, or any other advisor) is left unaffected.

Tests

make install PG_CONFIG=/path/to/pg_config
make localcheck PG_CONFIG=/path/to/pg_config

Known limitations

Similar to the limitations of pg_hint_plan, advice is only used if the comment survives in the query's source text and that text reaches the planner. The advice is silently ignored in these cases:

• Server-side function bodies (PL/pgSQL, etc.). Each statement's source text begins at its first token, so a comment placed before the statement is not part of it. Put the advice comment after the first word, e.g. SELECT /*+ SEQ_SCAN(t) */ * FROM t, not /*+ ... */ SELECT * FROM t. (For directly submitted queries a leading comment is fine — only function bodies drop preceding comments.)

• New-style SQL functions (CREATE FUNCTION ... BEGIN ATOMIC ...). Their bodies are stored parsed, with no source text, so there is no query string to read a comment from at plan time.

• ECPG (embedded SQL in C). ECPG strips comments from embedded queries before sending them, so the advice never reaches the server. The exception is dynamic EXECUTE, which sends the string verbatim. (Same as pg_hint_plan; see its docs/functional_limitations.md.)

• Clients, drivers, ORMs, or connection poolers that strip comments. Anything that rewrites or removes comments before the SQL reaches PostgreSQL will drop the advice.

Other limitations:

• Only the first /*+ ... */ advice comment in the query is used; any later ones are treated as ordinary comments.
• In a multi-statement simple query, the whole string is the source text for every statement, so a single advice comment applies to all of them (you cannot give different advice to different statements in one batch).
• An advice comment shares its /*+ marker with pg_hint_plan. Do not load both modules against the same workload, or they will fight over the comment.

Authors

• Lukas Fittl

License

Query scanner code (query_scan.*) adapted from pg_hint_plan under the PostgreSQL license
Portions Copyright (c) 1996-2026, The PostgreSQL Global Development Group
Portions Copyright (c) 1994, The Regents of the University of California

All other parts are licensed under the PostgreSQL license
Copyright (c) 2026, Duboce Labs, Inc. (pganalyze) team@pganalyze.com

See LICENSE file for details.