Re: pg_plan_advice - Mailing list pgsql-hackers

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.