Re: Estimation row error

Here, another issue with row estimate.
And, in this example, there is not correlation beetween columns in a same
table.

drop table if exists t1;
drop table if exists t2;
drop table if exists t3;

create table t1 as select generate_Series(1,200000) as c1;
create table t2 as select generate_Series(1,200000)%100 as c1;
create table t3 as select generate_Series(1,1500)%750 as c1;

alter table t1 add PRIMARY KEY (c1);
create index on t2 (c1);
create index on t3 (c1);

analyze verbose t1;
analyze verbose t2;
analyze verbose t3;

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
the explain plan : http://explain.depesz.com/s/YVw
Do you understand how postgresql calculate the row estimate ?

BR
Mathieu VINCENT

2015-12-17 10:14 GMT+01:00 Matteo Grolla <matteo(dot)grolla(at)gmail(dot)com>:

> Thank you both for the help!
> happy holidays
>
> 2015-12-17 10:10 GMT+01:00 Mathieu VINCENT <mathieu(dot)vincent(at)pmsipilot(dot)com>
> :
>
>> thks Gunnar,
>>
>> I removed the correlation between t3.c1 and t3.c2 in this sql script :
>>
>> 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 floor(random()*100+1) as c1, c2 from
>> generate_Series(1,200000) 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 verbose t1;
>> analyze verbose t2;
>> analyze verbose t3;
>> analyze verbose 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
>>
>> Now, the estimate is good : http://explain.depesz.com/s/gCX
>>
>> Have a good day
>>
>> Mathieu VINCENT
>>
>> 2015-12-15 11:21 GMT+01:00 Gunnar "Nick" Bluth <
>> gunnar(dot)bluth(dot)extern(at)elster(dot)de>:
>>
>>> Am 15.12.2015 um 10:49 schrieb Andreas Kretschmer:
>>> > Gunnar Nick Bluth <gunnar(dot)bluth(dot)extern(at)elster(dot)de> wrote:
>>> >
>>> >> Am 15.12.2015 um 09:05 schrieb Mathieu VINCENT:
>>> >>> Hello,
>>> >>>
>>> >>> No one to help me to understand this bad estimation rows ?
>>> >>
>>> >> Well,
>>> >>
>>> >> on a rather beefy machine, I'm getting quite a different plan:
>>> >> http://explain.depesz.com/s/3y5r
>>> >
>>> > you are using 9.5, right? Got the same plan with 9.5.
>>>
>>> Nope...:
>>> version
>>>
>>>
>>> ------------------------------------------------------------------------------------------------------------
>>> PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc
>>> (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
>>>
>>> So much for those correlation improvements then ;-/
>>>
>>>
>>> > Btw.: Hi Gunnar ;-)
>>>
>>> Hi :)
>>>
>>> --
>>> Gunnar "Nick" Bluth
>>> DBA ELSTER
>>>
>>> Tel: +49 911/991-4665
>>> Mobil: +49 172/8853339
>>>
>>>
>>> --
>>> Sent via pgsql-performance mailing list (
>>> pgsql-performance(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-performance
>>>
>>
>>
>