create table fact_table (a int, x text);
create table dim_1 (a int, b int, x int);
create table dim_2 (b int, x int);

insert into fact_table select 1 + mod((random() * 100000)::int, 1000), md5(i::text) from generate_series(1,1000000) S(i);
insert into dim_1 select i, i, random() * 100 from generate_series(1,1000) s(i);
insert into dim_2 select i, random() * 100 from generate_series(1,1000) s(i);

vacuum analyze;

set max_parallel_workers_per_gather = 0;

set join_collapse_limit = 1;

-- plan 1: predicate transfer between (dim_2 -> dim_1), but not to fact_table
\echo "plan 1: predicate transfer between (dim_2 -> dim_1), but not to fact_table"
explain analyze select * from fact_table join dim_1 on (fact_table.a = dim_1.a) join dim_2 on (dim_1.b = dim_2.b) where dim_2.x < 1;

-- plan 1: predicate transfer between (dim_2 -> dim_1), and then (dim_1 -> fact_table), correct cardinality estimates
\echo "plan 1: predicate transfer between (dim_2 -> dim_1), and then (dim_1 -> fact_table), correct cardinality estimates"
explain analyze select * from fact_table join dim_1 on (fact_table.a = dim_1.a) join dim_2 on (dim_1.b = dim_2.b) where dim_1.x < 10 and dim_2.x < 10;

reset join_collapse_limit;

-- plan 2: predicate transfer between (dim_2 -> dim_1) and (dim_1, dim_2 -> fact_table), but wrong estimates
\echo "plan 2: predicate transfer between (dim_2 -> dim_1) and (dim_1, dim_2 -> fact_table), but wrong estimates"
explain analyze select * from fact_table join dim_1 on (fact_table.a = dim_1.a) join dim_2 on (dim_1.b = dim_2.b) where dim_2.x < 1 and dim_1.x < 65;

-- plan 2: predicate transfer between (dim_2 -> dim_1), but no prericate transfer to fact_table
\echo "plan 2: predicate transfer between (dim_2 -> dim_1), but no prericate transfer to fact_table"
explain analyze select * from fact_table join dim_1 on (fact_table.a = dim_1.a) join dim_2 on (dim_1.b = dim_2.b) where dim_2.x < 1;
