On Thu, Jan 15, 2026 at 3:41 PM Robert Haas <[email protected]> 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.
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index c175ee95b68..772306d8262 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -63,25 +63,27 @@
* all of the others, but that doesn't work here: unsetting PGS_SEQSCAN,
* for instance, would disable both partial and non-partial sequential scans.
*/
-#define PGS_SEQSCAN 0x00000001
-#define PGS_INDEXSCAN 0x00000002
-#define PGS_INDEXONLYSCAN 0x00000004
-#define PGS_BITMAPSCAN 0x00000008
-#define PGS_TIDSCAN 0x00000010
-#define PGS_FOREIGNJOIN 0x00000020
-#define PGS_MERGEJOIN_PLAIN 0x00000040
-#define PGS_MERGEJOIN_MATERIALIZE 0x00000080
-#define PGS_NESTLOOP_PLAIN 0x00000100
-#define PGS_NESTLOOP_MATERIALIZE 0x00000200
-#define PGS_NESTLOOP_MEMOIZE 0x00000400
-#define PGS_HASHJOIN 0x00000800
-#define PGS_APPEND 0x00001000
-#define PGS_MERGE_APPEND 0x00002000
-#define PGS_GATHER 0x00004000
-#define PGS_GATHER_MERGE 0x00008000
-#define PGS_CONSIDER_INDEXONLY 0x00010000
-#define PGS_CONSIDER_PARTITIONWISE 0x00020000
-#define PGS_CONSIDER_NONPARTIAL 0x00040000
+typedef enum {
+ PGS_SEQSCAN = 1 << 0,
+ PGS_INDEXSCAN = 1 << 1,
+ PGS_INDEXONLYSCAN = 1 << 2,
+ PGS_BITMAPSCAN = 1 << 3,
+ PGS_TIDSCAN = 1 << 4,
+ PGS_FOREIGNJOIN = 1 << 5,
+ PGS_MERGEJOIN_PLAIN = 1 << 6,
+ PGS_MERGEJOIN_MATERIALIZE = 1 << 7,
+ PGS_NESTLOOP_PLAIN = 1 << 8,
+ PGS_NESTLOOP_MATERIALIZE = 1 << 9,
+ PGS_NESTLOOP_MEMOIZE = 1 << 10,
+ PGS_HASHJOIN = 1 << 11,
+ PGS_APPEND = 1 << 12,
+ PGS_MERGE_APPEND = 1 << 13,
+ PGS_GATHER = 1 << 14,
+ PGS_GATHER_MERGE = 1 << 15,
+ PGS_CONSIDER_INDEXONLY = 1 << 16,
+ PGS_CONSIDER_PARTITIONWISE = 1 << 17,
+ PGS_CONSIDER_NONPARTIAL = 1 << 18
+} PgsScanType;
/*
* Convenience macros for useful combination of the bits defined above.
