Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M53754=pgsql-performance-ar@postgresql.org>)
 id 1a8lrY-0006YG-IB
 for pgsql-performance@arkaria.postgresql.org; Tue, 15 Dec 2015 09:25:40 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.84) (envelope-from
 <pgsql-performance-owner+M53754=pgsql-performance-ar@postgresql.org>)
 id 1a8lrY-0006xo-4G
 for pgsql-performance@arkaria.postgresql.org; Tue, 15 Dec 2015 09:25:40 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
 (Exim 4.84) (envelope-from <gunnar.bluth.extern@elster.de>)
 id 1a8lrX-0006xf-EA
 for pgsql-performance@postgresql.org; Tue, 15 Dec 2015 09:25:39 +0000
Received: from edmmai01.een.elster.de ([217.237.175.34] helo=elster.de)
 by makus.postgresql.org with esmtp (Exim 4.84)
 (envelope-from <gunnar.bluth.extern@elster.de>) id 1a8lrT-0004Sk-Cr
 for pgsql-performance@postgresql.org; Tue, 15 Dec 2015 09:25:38 +0000
X-AuthUser: gunnar.bluth.extern@elster.de
Received: from [10.200.128.23] ([192.168.90.2]:37184)
 by elster.de with [XMail 1.27 ESMTP Server]
 id <S1027115> for <pgsql-performance@postgresql.org> from
 <gunnar.bluth.extern@elster.de>; Tue, 15 Dec 2015 10:25:31 +0100
Subject: Re: Estimation row error
To: pgsql-performance@postgresql.org
References: 
 <CAL+j8ETNDw=76dfWtbM5-eToD-nEEXf+e2jTMy71ugr+j2Sjkw@mail.gmail.com>
 <CAL+j8ERpv43D=oc8gGdfA7FKMwPWWOxteDdZ0=6ya1kB-9GhNA@mail.gmail.com>
 <CAL+j8ETVj=Y7Vo1PtyG5k4c0pBXRArnTfHC1YreUNB7uRNhHZg@mail.gmail.com>
From: "Gunnar \"Nick\" Bluth" <gunnar.bluth.extern@elster.de>
X-Enigmail-Draft-Status: N1110
Message-ID: <566FDC8A.8050701@elster.de>
Date: Tue, 15 Dec 2015 10:25:30 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.4.0
MIME-Version: 1.0
In-Reply-To: 
 <CAL+j8ETVj=Y7Vo1PtyG5k4c0pBXRArnTfHC1YreUNB7uRNhHZg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
X-Pg-Spam-Score: -4.2 (----)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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

Which may be related to this setting:
perftest=# show default_statistics_target ;
 default_statistics_target
---------------------------
 1000
(1 Zeile)


I guess the wrong row assumption (which I get as well!) is caused by the
given correlation of t3.c1 and t3.c2 (which the planner doesn't "see").

Tomas Vondra has written a nice blog post, covering that topic as well:
http://blog.pgaddict.com/posts/common-issues-with-planner-statistics

AFAIK, 9.5 has received some improvements in that field, but I didn't
try that yet.

Best regards,

Nick

> 
> Mathieu VINCENT
> 
> 2015-12-11 12:35 GMT+01:00 Mathieu VINCENT
> <mathieu.vincent@pmsipilot.com <mailto:mathieu.vincent@pmsipilot.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.vincent@pmsipilot.com <mailto:mathieu.vincent@pmsipilot.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
> 
> 
> 


-- 
Gunnar "Nick" Bluth
DBA ELSTER

Tel:   +49 911/991-4665
Mobil: +49 172/8853339


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance