Hello!
I see the motivation / idea behind it, but I think this needs some more thought.
The patch seems to cause some test failures in the test suite
(test_misc/003_check_guc, test_plan_advice/001_replan_regress.
And some quick testing revealed two more specific issues:
1. Shouldn't the patch either handle subplans, or skip queries containing them?
CREATE TABLE t1 (id int PRIMARY KEY, a int, c int);
CREATE TABLE t2 (id int PRIMARY KEY, b int);
INSERT INTO t1 SELECT g, g % 10, g % 7 FROM generate_series(1, 2000) g;
INSERT INTO t2 SELECT g, g % 10 FROM generate_series(1, 2000) g;
CREATE INDEX ON t2 (b);
ANALYZE t1, t2;
CREATE SEQUENCE flip_seq;
CREATE FUNCTION flip() RETURNS bool VOLATILE LANGUAGE sql
AS $$ SELECT nextval('flip_seq') % 3 = 0 $$;
SELECT count(*) FROM t1, t2
WHERE (SELECT flip() WHERE t1.id > 0) OR t1.a = t2.b;
2.
> The default of 8 is a nice guess because i found that starting from 7 arms,
> planning time sometimes doubles (in complicated queries).
The planning time and memory use seems to be exponential with nesting,
this explain takes 153 seconds for me in a debug build:
create table t1 (id int primary key, a int, c int);
create table t2 (id int primary key, b int);
insert into t1 select g, g % 10, g % 7 from generate_series(1, 2000) g;
insert into t2 select g, g % 10 from generate_series(1, 2000) g;
analyze t1, t2;
explain (summary, memory, costs off, timing off) select x.id, max(s.b)
as b from t1 x, (select x.id, max(s.b) as b from t1 x, (select x.id,
max(s.b) as b from t1 x, (select x.id, max(s.b) as b from t1 x,
(select x.id, max(s.b) as b from t1 x, (select x.id, max(s.b) as b
from t1 x, (select x.id, max(s.b) as b from t1 x, (select id, b from
t2) s where x.a = s.b or x.c = s.id or x.id = s.b group by x.id) s
where x.a = s.b or x.c = s.id or x.id = s.b group by x.id) s where x.a
= s.b or x.c = s.id or x.id = s.b group by x.id) s where x.a = s.b or
x.c = s.id or x.id = s.b group by x.id) s where x.a = s.b or x.c =
s.id or x.id = s.b group by x.id) s where x.a = s.b or x.c = s.id or
x.id = s.b group by x.id) s where x.a = s.b or x.c = s.id or x.id =
s.b group by x.id;
