I wrote:
> Over in the thread at [1] it's discussed how our code for making
> selectivity estimates using knowledge about FOREIGN KEY constraints
> is busted in the face of EquivalenceClasses including constants.
> ...
> Attached is a patch series that attacks it that way.
I'd failed to generate a test case I liked yesterday, but perhaps
the attached will do. (While the new code is exercised in the
core regression tests already, it doesn't produce any visible
plan changes.) I'm a little nervous about whether the plan
shape will be stable in the buildfarm, but it works for me on
both 64-bit and 32-bit machines, so probably it's OK.
regards, tom lane
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;
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 1403e0ffe7..dd60d6a1f3 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
--
