Re: pg_plan_advice

On Thu, Jan 15, 2026 at 3:41 PM Robert Haas <robertmhaas(at)gmail(dot)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.