diff options
Diffstat (limited to 'src/test/regress/expected/join.out')
| -rw-r--r-- | src/test/regress/expected/join.out | 50 |
1 files changed, 50 insertions, 0 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index a46b1573bd..6c9a5e26dd 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -5843,6 +5843,56 @@ 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; + QUERY PLAN +----------------------------------------------------------- + Nested Loop + -> Hash Join + Hash Cond: ((f2.x = f1.x) AND (f2.x10b = f1.x10)) + -> Seq Scan on fkest f2 + Filter: (x100 = 2) + -> Hash + -> Seq Scan on fkest f1 + Filter: (x100 = 2) + -> Index Scan using fkest_x_x10_x100_idx on fkest f3 + Index Cond: (x = f1.x) +(10 rows) + +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; + QUERY PLAN +----------------------------------------------------- + Hash Join + Hash Cond: ((f2.x = f1.x) AND (f2.x10b = f1.x10)) + -> Hash Join + Hash Cond: (f3.x = f2.x) + -> Seq Scan on fkest f3 + -> Hash + -> Seq Scan on fkest f2 + Filter: (x100 = 2) + -> Hash + -> Seq Scan on fkest f1 + Filter: (x100 = 2) +(11 rows) + +rollback; +-- -- test that foreign key join estimation performs sanely for outer joins -- begin; |