Adding foreign key between on t2 and t3, does not change the plan. 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+1 as c1; create table t3 as select generate_Series(1,1500)%750+1 as c1; alter table t1 add PRIMARY KEY (c1); create index on t2 (c1); create index on t3 (c1); ALTER TABLE t2 ADD CONSTRAINT t2_fk FOREIGN KEY (c1) REFERENCES t1(c1); ALTER TABLE t3 ADD CONSTRAINT t3_fk FOREIGN KEY (c1) REFERENCES t1(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 t1.c1=t3.c1 Cordialement, <http://www.psih.fr/>PSIH Décisionnel en santé Mathieu VINCENT Data Analyst PMSIpilot - 61 rue Sully - 69006 Lyon - France 2015-12-17 11:37 GMT+01:00 Mathieu VINCENT <mathieu.vinc...@pmsipilot.com>: > 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 >>>> >>> >>> >> >