diff options
Diffstat (limited to 'src/test/regress/sql/join.sql')
| -rw-r--r-- | src/test/regress/sql/join.sql | 29 |
1 files changed, 29 insertions, 0 deletions
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 1403e0ffe7b..dd60d6a1f3b 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1975,6 +1975,35 @@ select t1.b, ss.phv from join_ut1 t1 left join lateral drop table join_pt1; drop table join_ut1; + +-- +-- test estimation behavior with multi-column foreign key and constant qual +-- + +begin; + +create table fkest (x integer, x10 integer, x10b integer, x100 integer); +insert into fkest select x, x/10, x/10, x/100 from generate_series(1,1000) x; +create unique index on fkest(x, x10, x100); +analyze fkest; + +explain (costs off) +select * from fkest f1 + join fkest f2 on (f1.x = f2.x and f1.x10 = f2.x10b and f1.x100 = f2.x100) + join fkest f3 on f1.x = f3.x + where f1.x100 = 2; + +alter table fkest add constraint fk + foreign key (x, x10b, x100) references fkest (x, x10, x100); + +explain (costs off) +select * from fkest f1 + join fkest f2 on (f1.x = f2.x and f1.x10 = f2.x10b and f1.x100 = f2.x100) + join fkest f3 on f1.x = f3.x + where f1.x100 = 2; + +rollback; + -- -- test that foreign key join estimation performs sanely for outer joins -- |