Thank you both for the help! happy holidays 2015-12-17 10:10 GMT+01:00 Mathieu VINCENT <mathieu.vinc...@pmsipilot.com>:
> 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 >> > >
