thks Gunnar,
Have a good day
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.bluth.extern@xxxxxxxxx>:
Am 15.12.2015 um 10:49 schrieb Andreas Kretschmer:
> Gunnar Nick Bluth <gunnar.bluth.extern@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
