Horton

Thread

Commits

  1. pg_plan_advice: Fix variable type confusion.

  2. Add pg_plan_advice contrib module.

  1. pgsql: Add pg_plan_advice contrib module.

    Robert Haas <rhaas@postgresql.org> — 2026-03-12T17:00:57Z

    Add pg_plan_advice contrib module.

Provide a facility that (1) can be used to stabilize certain plan choices
so that the planner cannot reverse course without authorization and
(2) can be used by knowledgeable users to insist on plan choices contrary
to what the planner believes best. In both cases, terrible outcomes are
possible: users should think twice and perhaps three times before
constraining the planner's ability to do as it thinks best; nevertheless,
there are problems that are much more easily solved with these facilities
than without them.

This patch takes the approach of analyzing a finished plan to produce
textual output, which we call "plan advice", that describes key
decisions made during plan; if that plan advice is provided during
future planning cycles, it will force those key decisions to be made in
the same way.  Not all planner decisions can be controlled using advice;
for example, decisions about how to perform aggregation are currently
out of scope, as is choice of sort order. Plan advice can also be edited
by the user, or even written from scratch in simple cases, making it
possible to generate outcomes that the planner would not have produced.
Partial advice can be provided to control some planner outcomes but not
others.

Currently, plan advice is focused only on specific outcomes, such as
the choice to use a sequential scan for a particular relation, and not
on estimates that might contribute to those outcomes, such as a
possibly-incorrect selectivity estimate. While it would be useful to
users to be able to provide plan advice that affects selectivity
estimates or other aspects of costing, that is out of scope for this
commit.

Reviewed-by: Lukas Fittl <lukas@fittl.com>
Reviewed-by: Jakub Wartak <jakub.wartak@enterprisedb.com>
Reviewed-by: Greg Burd <greg@burd.me>
Reviewed-by: Jacob Champion <jacob.champion@enterprisedb.com>
Reviewed-by: Haibo Yan <tristan.yim@gmail.com>
Reviewed-by: Dian Fay <di@nmfay.com>
Reviewed-by: Ajay Pal <ajay.pal.k@gmail.com>
Reviewed-by: John Naylor <johncnaylorls@gmail.com>
Reviewed-by: Alexandra Wang <alexandra.wang.oss@gmail.com>
Discussion: http://postgr.es/m/CA+TgmoZ-Jh1T6QyWoCODMVQdhTUPYkaZjWztzP1En4=ZHoKPzw@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/5883ff30b02ceed3c5eabba4d9c09a7766f9a8fc

Modified Files
--------------
contrib/Makefile                                  |    1 +
contrib/meson.build                               |    1 +
contrib/pg_plan_advice/.gitignore                 |    7 +
contrib/pg_plan_advice/Makefile                   |   43 +
contrib/pg_plan_advice/README                     |  260 +++
contrib/pg_plan_advice/expected/gather.out        |  371 ++++
contrib/pg_plan_advice/expected/join_order.out    |  500 +++++
contrib/pg_plan_advice/expected/join_strategy.out |  339 ++++
contrib/pg_plan_advice/expected/partitionwise.out |  426 ++++
contrib/pg_plan_advice/expected/prepared.out      |   67 +
contrib/pg_plan_advice/expected/scan.out          |  757 +++++++
contrib/pg_plan_advice/expected/semijoin.out      |  377 ++++
contrib/pg_plan_advice/expected/syntax.out        |  192 ++
contrib/pg_plan_advice/meson.build                |   66 +
contrib/pg_plan_advice/pg_plan_advice.c           |  456 +++++
contrib/pg_plan_advice/pg_plan_advice.h           |   45 +
contrib/pg_plan_advice/pgpa_ast.c                 |  351 ++++
contrib/pg_plan_advice/pgpa_ast.h                 |  185 ++
contrib/pg_plan_advice/pgpa_identifier.c          |  481 +++++
contrib/pg_plan_advice/pgpa_identifier.h          |   52 +
contrib/pg_plan_advice/pgpa_join.c                |  638 ++++++
contrib/pg_plan_advice/pgpa_join.h                |  105 +
contrib/pg_plan_advice/pgpa_output.c              |  571 ++++++
contrib/pg_plan_advice/pgpa_output.h              |   22 +
contrib/pg_plan_advice/pgpa_parser.y              |  301 +++
contrib/pg_plan_advice/pgpa_planner.c             | 2198 +++++++++++++++++++++
contrib/pg_plan_advice/pgpa_planner.h             |   19 +
contrib/pg_plan_advice/pgpa_scan.c                |  271 +++
contrib/pg_plan_advice/pgpa_scan.h                |   85 +
contrib/pg_plan_advice/pgpa_scanner.l             |  297 +++
contrib/pg_plan_advice/pgpa_trove.c               |  516 +++++
contrib/pg_plan_advice/pgpa_trove.h               |  114 ++
contrib/pg_plan_advice/pgpa_walker.c              | 1029 ++++++++++
contrib/pg_plan_advice/pgpa_walker.h              |  141 ++
contrib/pg_plan_advice/sql/gather.sql             |   86 +
contrib/pg_plan_advice/sql/join_order.sql         |  145 ++
contrib/pg_plan_advice/sql/join_strategy.sql      |   84 +
contrib/pg_plan_advice/sql/partitionwise.sql      |   99 +
contrib/pg_plan_advice/sql/prepared.sql           |   37 +
contrib/pg_plan_advice/sql/scan.sql               |  195 ++
contrib/pg_plan_advice/sql/semijoin.sql           |  118 ++
contrib/pg_plan_advice/sql/syntax.sql             |   68 +
doc/src/sgml/contrib.sgml                         |    1 +
doc/src/sgml/filelist.sgml                        |    1 +
doc/src/sgml/pgplanadvice.sgml                    |  813 ++++++++
src/tools/pgindent/typedefs.list                  |   33 +
46 files changed, 12964 insertions(+)

  2. Re: pgsql: Add pg_plan_advice contrib module.

    David Rowley <dgrowleyml@gmail.com> — 2026-03-16T21:11:54Z

    On Fri, 13 Mar 2026 at 06:01, Robert Haas <rhaas@postgresql.org> wrote:
> Add pg_plan_advice contrib module.

Hi Robert,

I was just building with VS2026 when I noticed the following warning:

../contrib/pg_plan_advice/pgpa_planner.c(1195): warning C4319: '~':
zero extending 'uint32' to 'uint64' of greater size

I guess the compiler is concerned that you might not have meant to
unconditionally switch off all the upper 32-bits of the 64-bit
variable.  Adding the following does get rid of the warning for me,
but not sure if that's what you had intended. I've not studied the
code enough to understand why there are two different integer widths
being used for the bits.

-               *pgs_mask_p &= ~(PGS_JOIN_ANY & ~join_mask);
+               *pgs_mask_p &= (uint32) ~(PGS_JOIN_ANY & ~join_mask);

David

  3. Re: pgsql: Add pg_plan_advice contrib module.

    Robert Haas <robertmhaas@gmail.com> — 2026-03-17T13:59:10Z

    On Mon, Mar 16, 2026 at 5:12 PM David Rowley <dgrowleyml@gmail.com> wrote:
> I was just building with VS2026 when I noticed the following warning:
>
> ../contrib/pg_plan_advice/pgpa_planner.c(1195): warning C4319: '~':
> zero extending 'uint32' to 'uint64' of greater size
>
> I guess the compiler is concerned that you might not have meant to
> unconditionally switch off all the upper 32-bits of the 64-bit
> variable.  Adding the following does get rid of the warning for me,
> but not sure if that's what you had intended. I've not studied the
> code enough to understand why there are two different integer widths
> being used for the bits.
>
> -               *pgs_mask_p &= ~(PGS_JOIN_ANY & ~join_mask);
> +               *pgs_mask_p &= (uint32) ~(PGS_JOIN_ANY & ~join_mask);

Thanks for the report. This is an error on my part, pure and simple.
At one point, pgs_mask was a 32-bit value, and although that changed
months ago, this holdover escaped detection. I'll go fix that.

-- 
Robert Haas
EDB: http://www.enterprisedb.com