Horton
Menu

Re: pg_plan_advice

Jakub Wartak <jakub.wartak@enterprisedb.com>

View thread
From: Jakub Wartak <jakub.wartak@enterprisedb.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Jacob Champion <jacob.champion@enterprisedb.com>, Dian Fay <di@nmfay.com>, Matheus Alcantara <matheusssilv97@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2026-01-09T13:52:59Z
Lists: pgsql-hackers 
On Thu, Jan 8, 2026 at 7:21 PM Robert Haas <robertmhaas@gmail.com> wrote:
[..]
> I've attempted to address that in v8 [..snip]
[..]

The full TPC-H queries set still reported some issues with v8 (for
q20/with wrong plan after using advice and two "partially matched" for
q9 and q2). However after applying that tiny patch from [1] it makes
all of the problems go away and for the TPC-H query set there are no
failures anymore (yay!). By failure I mean a different plan when using
advice and/or any partial match or failure to apply advice.

So, I've switched to more realistic test for each TPC-H query:
1) ensure we have valid stats, gather plan, save advices
2) clear stats using pg_clear_relation_stats() , apply advice and
check if we have exact same plan

The only thing this hav revealed is what appears to be some tiny
problem with placing GroupAggregates or am I wrong or is that known
limitation? (The original plan shows "Partial GroupAggregate" while
the one using advice is not aware of the need to use it; yet
contrib/pg_plan_advices/README in "Future Work" indicates it is out of
scope for now, right?)

--- /tmp/plan
+++ /tmp/planadviced
Sort
   Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric -
lineitem.l_discount)))) DESC
-  ->  Finalize GroupAggregate
+  ->  GroupAggregate
         Group Key: nation.n_name
         ->  Gather Merge
               Workers Planned: 2
-              ->  Partial GroupAggregate
-                    Group Key: nation.n_name
-                    ->  Sort
-                          Sort Key: nation.n_name
-                          ->  Hash Join
-                                Hash Cond: ((lineitem.l_suppkey =
supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey))
+              ->  Sort
+                    Sort Key: nation.n_name
+                    ->  Hash Join
+                          Hash Cond: ((lineitem.l_suppkey =
supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey))

-J.

[1] - https://www.postgresql.org/message-id/CA%2BTgmoZzBkd1BG8qusicUjme0kZuT8konQM_rcr0gMXs-TpK7A%40mail.gmail.com

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. pg_plan_advice: Fix another unique-semijoin bug.

  2. pg_plan_advice: Export feedback-related definitions.

  3. pg_plan_advice: Fix a bug when a subquery is pruned away entirely.

  4. pg_plan_advice: Add alternatives test to Makefile.

  5. pg_plan_advice: Handle non-repeatable TABLESAMPLE scans.

  6. pg_stash_advice: Allow stashed advice to be persisted to disk.

  7. Add pg_stash_advice contrib module.

  8. pg_plan_advice: Avoid assertion failure with partitionwise aggregate.

  9. pg_plan_advice: Invent DO_NOT_SCAN(relation_identifier).

  10. Add an alternative_plan_name field to PlannerInfo.

  11. pg_plan_advice: Refactor to invent pgpa_planner_info

  12. Respect disabled_nodes in fix_alternative_subplan.

  13. get_memoize_path: Don't exit quickly when PGS_NESTLOOP_PLAIN is unset.

  14. test_plan_advice: Set TAP test priority 50 in meson.build.

  15. pg_plan_advice: Avoid a crash under GEQO.

  16. Test pg_plan_advice using a new test_plan_advice module.

  17. pg_plan_advice: Always install pg_plan_advice.h, and in the right place

  18. pg_plan_advice: Fix failures to accept identifier keywords.

  19. Add pg_plan_advice contrib module.

  20. Allow extensions to mark an individual index as disabled.

  21. Replace get_relation_info_hook with build_simple_rel_hook.

  22. Store information about Append node consolidation in the final plan.

  23. Store information about elided nodes in the final plan.

  24. Store information about range-table flattening in the final plan.

  25. Pass cursorOptions to planner_setup_hook.

  26. Fix PGS_CONSIDER_NONPARTIAL interaction with Materialize nodes.

  27. Fix mistakes in commit 4020b370f214315b8c10430301898ac21658143f

  28. Allow for plugin control over path generation strategies.

  29. Update some comments for fasthash

  30. Allow passing a pointer to GetNamedDSMSegment()'s init callback.

  31. Don't reset the pathlist of partitioned joinrels.

  32. Treat number of disabled nodes in a path as a separate cost metric.