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
