Hello, No one to help me to understand this bad estimation rows ?
Mathieu VINCENT 2015-12-11 12:35 GMT+01:00 Mathieu VINCENT <mathieu.vinc...@pmsipilot.com>: > Sorry, I forget to precise Postgresql version > > 'PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 > 20120313 (Red Hat 4.4.7-11), 64-bit' > > > BR > > Mathieu VINCENT > > > > 2015-12-11 9:53 GMT+01:00 Mathieu VINCENT <mathieu.vinc...@pmsipilot.com>: > >> Hello, >> >> I would like to know how row estimation is calculed by explain ? >> In my execution plan, this estimation is extremely wrong (267 instead of >> 198000) >> I reproduced this estimation error in this simple case : >> >> 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 generate_Series(1,200000)%100 as >> c1,generate_Series(1,200000) as 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 t1; >> analyze t2; >> analyze t3; >> analyze 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 >> >> Explain plan : >> http://explain.depesz.com/s/wZ3v >> >> I think this error may be problematic because planner will choose nested >> loop instead of hash joins for ultimate join. Can you help me to improve >> this row estimation ? >> >> Thank you for answering >> >> Best Regards, >> <http://www.psih.fr/>PSIH Décisionnel en santé >> Mathieu VINCENT >> Data Analyst >> PMSIpilot - 61 rue Sully - 69006 Lyon - France >> > >
