Estimation row error
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 :
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 |
В списке pgsql-performance по дате отправления:
Предыдущее
От: ankur_adwyzeДата:
Сообщение: Re: Advise needed for a join query with a where conditional