public inbox for [email protected]help / color / mirror / Atom feed
pgsql: 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 <[email protected]> 2026-03-16 21:11 ` Re: pgsql: Add pg_plan_advice contrib module. David Rowley <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Robert Haas @ 2026-03-12 17:00 UTC (permalink / raw) To: [email protected] 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 <[email protected]> Reviewed-by: Jakub Wartak <[email protected]> Reviewed-by: Greg Burd <[email protected]> Reviewed-by: Jacob Champion <[email protected]> Reviewed-by: Haibo Yan <[email protected]> Reviewed-by: Dian Fay <[email protected]> Reviewed-by: Ajay Pal <[email protected]> Reviewed-by: John Naylor <[email protected]> Reviewed-by: Alexandra Wang <[email protected]> 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 <[email protected]> @ 2026-03-16 21:11 ` David Rowley <[email protected]> 2026-03-17 13:59 ` Re: pgsql: Add pg_plan_advice contrib module. Robert Haas <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: David Rowley @ 2026-03-16 21:11 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: [email protected] On Fri, 13 Mar 2026 at 06:01, Robert Haas <[email protected]> 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 <[email protected]> 2026-03-16 21:11 ` Re: pgsql: Add pg_plan_advice contrib module. David Rowley <[email protected]> @ 2026-03-17 13:59 ` Robert Haas <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Robert Haas @ 2026-03-17 13:59 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On Mon, Mar 16, 2026 at 5:12 PM David Rowley <[email protected]> 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 <[email protected]> 2026-03-16 21:11 ` David Rowley <[email protected]> 2026-03-17 13:59 ` Robert Haas <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox