Here, another issue with row estimate. And, in this example, there is not correlation beetween columns in a same table.
drop table if exists t1; drop table if exists t2; drop table if exists t3; create table t1 as select generate_Series(1,200000) as c1; create table t2 as select generate_Series(1,200000)%100 as c1; create table t3 as select generate_Series(1,1500)%750 as c1; alter table t1 add PRIMARY KEY (c1); create index on t2 (c1); create index on t3 (c1); analyze verbose t1; analyze verbose t2; analyze verbose t3; 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 the explain plan : http://explain.depesz.com/s/YVw Do you understand how postgresql calculate the row estimate ? BR Mathieu VINCENT 2015-12-17 10:14 GMT+01:00 Matteo Grolla <matteo.gro...@gmail.com>: > 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 >>> >> >> >
