pg.ddx.io pgsql-committers@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql: Add pg_plan_advice contrib module.
3+ messages / 3 participants
[nested] [flat]
* pgsql: Add pg_plan_advice contrib module.
@ 2026-03-12 17:00 Robert Haas <rhaas@postgresql.org>
2026-03-16 21:11 ` Re: pgsql: Add pg_plan_advice contrib module. David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Robert Haas @ 2026-03-12 17:00 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
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(+)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pgsql: Add pg_plan_advice contrib module.
2026-03-12 17:00 pgsql: Add pg_plan_advice contrib module. Robert Haas <rhaas@postgresql.org>
@ 2026-03-16 21:11 ` David Rowley <dgrowleyml@gmail.com>
2026-03-17 13:59 ` Re: pgsql: Add pg_plan_advice contrib module. Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: David Rowley @ 2026-03-16 21:11 UTC (permalink / raw)
To: Robert Haas <rhaas@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pgsql: Add pg_plan_advice contrib module.
2026-03-12 17:00 pgsql: Add pg_plan_advice contrib module. Robert Haas <rhaas@postgresql.org>
2026-03-16 21:11 ` Re: pgsql: Add pg_plan_advice contrib module. David Rowley <dgrowleyml@gmail.com>
@ 2026-03-17 13:59 ` Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Robert Haas @ 2026-03-17 13:59 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-03-17 13:59 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-12 17:00 pgsql: Add pg_plan_advice contrib module. Robert Haas <rhaas@postgresql.org>
2026-03-16 21:11 ` David Rowley <dgrowleyml@gmail.com>
2026-03-17 13:59 ` Robert Haas <robertmhaas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox