Re: Estimation row error - Mailing list pgsql-performance

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.vincent@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,

PSIH Décisionnel en santé Mathieu VINCENT  Data Analyst PMSIpilot - 61 rue Sully - 69006 Lyon - France