From: | Mathieu VINCENT <mathieu(dot)vincent(at)pmsipilot(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Estimation row error |
Date: | 2015-12-15 08:05:38 |
Message-ID: | CAL+j8ETVj=Y7Vo1PtyG5k4c0pBXRArnTfHC1YreUNB7uRNhHZg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)vincent(at)pmsipilot(dot)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(dot)vincent(at)pmsipilot(dot)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
>>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Gunnar "Nick" Bluth | 2015-12-15 09:25:30 | Re: Estimation row error |
Previous Message | Mattthew Lunnon | 2015-12-14 21:43:44 | Re: Performance difference between Slon master and slave |