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
signature.asc
Description: OpenPGP digital signature
