Re: Estimation row error

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hi

2015-12-18 16:21 GMT+01:00 Mathieu VINCENT <mathieu.vincent@xxxxxxxxxxxxx>:

Hello,

No one to help me to understand this bad estimation rows ?

It's NOT caused by :

• correlation between columns (cross-correlation)
• bad statistics (i tried with  default_statistics_target to 10 000)
• bad number of distinct values
• complexe join conditions

I have no more ideas.

PostgreSQL has not cross tables statistics - so expect uniform distribution of foreign keys.  This expectation is broken in your example.

You can find some prototype solutions by Tomas Vondra in hackars mailing list.

Regards

Pavel

 

thank you for your help.

Mathieu VINCENT

2015-12-17 11:58 GMT+01:00 Mathieu VINCENT <mathieu.vincent@xxxxxxxxxxxxx>:

Adding foreign key between on t2 and t3, does not change the plan.

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+1 as c1; 

create table t3 as select generate_Series(1,1500)%750+1 as c1;

alter table t1 add PRIMARY KEY (c1);

create index on t2 (c1);

create index on t3 (c1);

ALTER TABLE t2  ADD CONSTRAINT t2_fk FOREIGN KEY (c1) REFERENCES t1(c1);

ALTER TABLE t3  ADD CONSTRAINT t3_fk FOREIGN KEY (c1) REFERENCES t1(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 t1.c1=t3.c1

Cordialement,

PSIH Décisionnel en santé Mathieu VINCENT  Data Analyst PMSIpilot - 61 rue Sully - 69006 Lyon - France

2015-12-17 11:37 GMT+01:00 Mathieu VINCENT <mathieu.vincent@xxxxxxxxxxxxx>:

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.grolla@xxxxxxxxx>:

Thank you both for the help!

happy holidays

2015-12-17 10:10 GMT+01:00 Mathieu VINCENT <mathieu.vincent@xxxxxxxxxxxxx>:

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