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: Lukas Fittl <lukas@fittl.com>, 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-19T10:53:38Z
Lists: pgsql-hackers

Attachments

On Thu, Jan 15, 2026 at 3:41 PM Robert Haas <robertmhaas@gmail.com> wrote:
>
[..]
>
> So here's v10.
[..]
> I'm very appreciative to everyone for all the testing and reports
> about 0005; I still do need some substantive code review particularly
> of 0001.

Hi,

1. With v10 all my minimal TPC-H checks are OK (both with stats/without stats,
   parallel and non-parallel).

2. I couldn't find any glaring issue during code review of v10-000[124]. But I
   have some questions:
   a) v10-0001 - any example producing such a dummy subplan? (whatever
I've tried I
      cannot come up with one)
   b) v10-0001 - maybe we could add a comment nearby "dummy" struct
member to look
      on pgpa_plan_walker() on example how to use it, but that's part of v5 and
      contrib...
   c) In v10-0004, maybe in pathnodes.h we could use typedef enum rather than
      list of #defines? (see attached)

3. Yes, I could too also repro Jacob's and get the same failure, so it's real:
   TRAP: failed Assert("child_target->ttype == PGPA_TARGET_IDENTIFIER"),
   File: "../contrib/pg_plan_advice/pgpa_walker.c", Line: 679, PID: 32344

4. Some raw perf numbers on non-assert builds (please ignore +/- 3%
jumps), it just hurts
   in one scenario where oq2 drops like 9% of juice (quite expected, it's not
   an issue to be, just posting full results)

tps                           oq1  oq2    oq3  oq4
master                        41   14745  439  435
master+v10-000[1-4]           42   15055  439  432
master+v10full                41   14734  429  437
master+v10full+loaded         42   15014  442  438
master+v10full+loaded+advice  41   13481  424  439

(same but in percentages)
%tps_to_master                oq1  oq2    oq3  oq4
master                        100  100    100  100
master+v10-000[1-4]           102  102    100  99
master+v10full                100  100    98   100
master+v10full+loaded         102  102    101  101
master+v10full+loaded+advice  100  91     97   101


Some explanation:
* oq => my shortcut for Optimizer stress Query (to disambiguate from
TPC-H Queries)
* master+v10full+loaded - shared_preloaded_libraries was set to
  have pg_plan_advice
* master+v10full+loaded+advice - as above, but with system-wide GUC set
  to lengthy and irrelevant (as none of the queries used such aliases)
     JOIN_ORDER(x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11)
     NESTED_LOOP_PLAIN(x2 x3 x4 x5 x6 x7 x8 x9 x10 x11)
     SEQ_SCAN(x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11)
     NO_GATHER(x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11)
  The idea was to see how that impacts oq1..4 while not using those.

So out of curiosity the oq2 on 1 CPU core behavior looks like below:
- no advices --> ~1000 TPS
- enabled pg_plan_advice.advice to lengthy, but unrelated thing and it
gets ~890TPS
- in both cases (empty and set) the bottleneck seems to in palloc0, but
    empty plan_advice: it's more like palloc0() <- newNode() <-
create_index_path()
    <- build_index_paths()
    with plan_advice set: palloc0() <- newNode() <- create_nestloop_path() ..
- so if anything people should not put something there blindly, but just SET
  and RESET afterwards (unless we get pinning of SQL plan id to advices) as
  this might have cost in high-TPS scenarios.

-- details about suite for benchmarking:
SELECT 'CREATE TABLE t' || g || ' (id int primary key, val int)'
FROM generate_series(1, 11) g;
\gexec
-- 1k parts
CREATE TABLE tstresspart (id int, val text) PARTITION BY RANGE (id);
SELECT 'CREATE TABLE tpart' || g || ' PARTITION OF tstresspart FOR
VALUES FROM ('
|| g*10 || ') TO (' || (g+1)*10 || ')' FROM generate_series(1, 1000) g;
\gexec

-- oq1, obtakes ~500ms, below GEQO threshold
EXPLAIN SELECT * FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11
WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id
  AND t4.id = t5.id AND t5.id = t6.id AND t6.id = t7.id
  AND t7.id = t8.id AND t8.id = t9.id AND t9.id = t10.id
  AND t10.id = t11.id;

-- oq2, hit nested subqueries hard
EXPLAIN SELECT * FROM t1
    WHERE id IN (SELECT id FROM t2
    WHERE id IN (SELECT id FROM t3
    WHERE id IN (SELECT id FROM t4
    WHERE id IN (SELECT id FROM t5
    WHERE id IN (SELECT id FROM t6
    WHERE id IN (SELECT id FROM t7
    WHERE id IN (SELECT id FROM t8
    WHERE id IN (SELECT id FROM t9
    WHERE id IN (SELECT id FROM t10
    WHERE id IN (SELECT id FROM t11))))))))))
OR id IN (SELECT val FROM t1);

-- oq3, part stress test, no part pruning
EXPLAIN SELECT * FROM tstresspart WHERE id = (SELECT (random()*1000));

-- oq4, stress test IN/VALUES
perl -e 'print "SELECT * FROM t1 WHERE id IN ("; for(1..40000)
{ print "$_"; print "," if $_ != 40000 }; print ");"' > oq4.sql

-J.

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.