Re: Estimation row error

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
>>
>
>