Re: [HACKERS] 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
SemiJoin  (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=1000000width=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: 91Planning time:
0.674msExecution 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
SemiJoin  (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=1000000width=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: 91Planning time:
0.247msExecution time: 235.295 ms 
(10 rows)


Estimated row is 10x larger since 100340e2d

Regards,

--
Adrien NAYRAT

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