Re: New design for FK-based join selectivity estimation

Hi hackers,

The commit 100340e2dcd05d6505082a8fe343fb2ef2fa5b2a introduce an
estimation error :

create table t3 as select j from generate_series(1,10000)
i,generate_series(1,100) j ;
create table t4 as select j from generate_series(1,100) j ;
create unique index ON t4(j);
alter table t3 add constraint fk foreign key (j) references t4(j);
analyze;

9.5.5
explain analyze select * from t3 where j in (select * from t4 where j<10);
QUERY PLAN

--------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=2.36..18053.61 rows=90000 width=4) (actual
time=0.217..282.325 rows=90000 loops=1)
Hash Cond: (t3.j = t4.j)
-> Seq Scan on t3 (cost=0.00..14425.00 rows=1000000 width=4)
(actual time=0.112..116.063 rows=1000000 loops=1)
-> Hash (cost=2.25..2.25 rows=9 width=4) (actual time=0.083..0.083
rows=9 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on t4 (cost=0.00..2.25 rows=9 width=4) (actual
time=0.019..0.074 rows=9 loops=1)
Filter: (j < 10)
Rows Removed by Filter: 91
Planning time: 0.674 ms
Execution time: 286.043 ms

On 9.6 HEAD

explain analyze select * from t3 where j in (select * from t4 where j<10);
QUERY PLAN

-------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=2.36..18053.61 rows=1000000 width=4) (actual
time=0.089..232.327 rows=90000 loops=1)
Hash Cond: (t3.j = t4.j)
-> Seq Scan on t3 (cost=0.00..14425.00 rows=1000000 width=4)
(actual time=0.047..97.926 rows=1000000 loops=1)
-> Hash (cost=2.25..2.25 rows=9 width=4) (actual time=0.032..0.032
rows=9 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on t4 (cost=0.00..2.25 rows=9 width=4) (actual
time=0.008..0.030 rows=9 loops=1)
Filter: (j < 10)
Rows Removed by Filter: 91
Planning time: 0.247 ms
Execution time: 235.295 ms
(10 rows)

Estimated row is 10x larger since 100340e2d

Regards,

--
Adrien NAYRAT

http://dalibo.com - http://dalibo.org