thks Gunnar, I removed the correlation between t3.c1 and t3.c2 in this sql script :
drop table if exists t1; drop table if exists t2; drop table if exists t3; drop table if exists t4; create table t1 as select generate_Series(1,300000) as c1; create table t2 as select generate_Series(1,400) as c1; create table t3 as select floor(random()*100+1) as c1, c2 from generate_Series(1,200000) c2; create table t4 as select generate_Series(1,200000) as c1; alter table t1 add PRIMARY KEY (c1); alter table t2 add PRIMARY KEY (c1); alter table t3 add PRIMARY KEY (c1,c2); create index on t3 (c1); create index on t3 (c2); alter table t4 add PRIMARY KEY (c1); analyze verbose t1; analyze verbose t2; analyze verbose t3; analyze verbose t4; EXPLAIN (analyze on, buffers on, verbose on) select * from t1 t1 inner join t2 on t1.c1=t2.c1 inner join t3 on t2.c1=t3.c1 inner join t4 on t3.c2=t4.c1 Now, the estimate is good : http://explain.depesz.com/s/gCX Have a good day Mathieu VINCENT 2015-12-15 11:21 GMT+01:00 Gunnar "Nick" Bluth < gunnar.bluth.ext...@elster.de>: > Am 15.12.2015 um 10:49 schrieb Andreas Kretschmer: > > Gunnar Nick Bluth <gunnar.bluth.ext...@elster.de> wrote: > > > >> Am 15.12.2015 um 09:05 schrieb Mathieu VINCENT: > >>> Hello, > >>> > >>> No one to help me to understand this bad estimation rows ? > >> > >> Well, > >> > >> on a rather beefy machine, I'm getting quite a different plan: > >> http://explain.depesz.com/s/3y5r > > > > you are using 9.5, right? Got the same plan with 9.5. > > Nope...: > version > > > ------------------------------------------------------------------------------------------------------------ > PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc > (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit > > So much for those correlation improvements then ;-/ > > > > Btw.: Hi Gunnar ;-) > > Hi :) > > -- > Gunnar "Nick" Bluth > DBA ELSTER > > Tel: +49 911/991-4665 > Mobil: +49 172/8853339 > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
