Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TjHSM-0005aq-Br for pgsql-performance@arkaria.postgresql.org; Thu, 13 Dec 2012 22:40:42 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TjHSL-00055c-Sr for pgsql-performance@arkaria.postgresql.org; Thu, 13 Dec 2012 22:40:41 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TjHSK-00055X-SI for pgsql-performance@postgresql.org; Thu, 13 Dec 2012 22:40:41 +0000 Received: from mail-la0-f46.google.com ([209.85.215.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TjHSJ-0008CY-0i for pgsql-performance@postgresql.org; Thu, 13 Dec 2012 22:40:40 +0000 Received: by mail-la0-f46.google.com with SMTP id p5so2263670lag.19 for ; Thu, 13 Dec 2012 14:40:37 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=content-type:mime-version:subject:from:in-reply-to:date:cc :message-id:references:to:x-mailer; bh=kejBhdv2rgOzq3M/5RlelqVoAuqprRsmco2S3linzYs=; b=n1f6qX01U0HIhc2cbnEg+SAOU3XiZ97UNq4voPQYiA3BI7zBp5rGcvExIOs1dtHVEm UD47F+WCQje5J5S2x4aOWJctSP2aeUb093UPiZQK5G0neuY+oJv2ya+PD9kbxBxI57eh QgPtNCir7DBMU64L4ZxT9yZ+0W+qV1k7Sr+CpuYhAo+CUlrAswn0rWrboqgyZcwdVDCW Bl258HpznvqXP9n22A2YCAl3hjPE9YYcmz2BBnZu5kD6gwBJmlV0F3XEXvvfIskM5lRJ OIePYeo87rRpixQXEv5Gj8fZwdgHOlni8ImKwajWpTDVc3wgoctUeoc+PABHOPAlvEwJ +gfQ== Received: by 10.152.148.40 with SMTP id tp8mr764622lab.30.1355438437867; Thu, 13 Dec 2012 14:40:37 -0800 (PST) Received: from [192.168.20.5] ([176.195.200.36]) by mx.google.com with ESMTPS id ps11sm1140186lab.12.2012.12.13.14.40.36 (version=TLSv1/SSLv3 cipher=OTHER); Thu, 13 Dec 2012 14:40:37 -0800 (PST) Content-Type: multipart/alternative; boundary="Apple-Mail=_7931ACE9-FFA1-4840-B0AC-EA377BB909F2" Mime-Version: 1.0 (Mac OS X Mail 6.2 \(1499\)) Subject: Re: Why does the number of rows are different in actual and estimated. From: Evgeny Shishkin In-Reply-To: <50CA586A.30008@dunslane.net> Date: Fri, 14 Dec 2012 02:40:35 +0400 Cc: AI Rumman , pgsql-performance@postgresql.org Message-Id: <55F1445F-304C-41C7-AB55-5ABC19184F00@gmail.com> References: <50CA586A.30008@dunslane.net> To: Andrew Dunstan X-Mailer: Apple Mail (2.1499) X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_7931ACE9-FFA1-4840-B0AC-EA377BB909F2 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=iso-8859-1 On Dec 14, 2012, at 2:36 AM, Andrew Dunstan wrote: >=20 > On 12/13/2012 05:12 PM, AI Rumman wrote: >> Why does the number of rows are different in actual and estimated? >>=20 >=20 >=20 > Isn't that in the nature of estimates? An estimate is a heuristic = guess at the number of rows it will find for the given query or part of = a query. It's not uncommon for estimates to be out by several orders of = magnitude. Guaranteeing estimates within bounded accuracy and in a given = short amount of time (you don't want your planning time to overwhelm = your execution time) isn't possible. >=20 The main question i think is what to do with it. The problem starts here -> Hash Join (cost=3D9337.97..18115.71 rows=3D34489 width=3D244) = (actual time=3D418.054..1156.453 rows=3D205420 loops=3D1) Hash Cond: = (customerdetails.customerid =3D entity.id) -> Seq Scan on = customerdetails (cost=3D0.00..4752.46 rows=3D327146 width=3D13) (actual = time=3D0.021..176.389 rows=3D327328 loops=3D1) -> Hash = (cost=3D6495.65..6495.65 rows=3D227386 width=3D231) (actual = time=3D417.839..417.839 rows=3D205420 loops=3D1) Buckets: 32768 = Batches: 1 Memory Usage: 16056kB -> Index Scan using = entity_setype_idx on entity (cost=3D0.00..6495.65 rows=3D227386 = width=3D231) (actual time=3D0.033..2 53.880 rows=3D205420 loops=3D1) Index Cond: = ((setype)::text =3D 'con_s'::text) -> Index Scan using con_address_pkey on = con_address (cost=3D0.00..0.27 rows=3D1 width=3D46) (actual = time=3D0.003..0.004 rows=3D1 loops=3D2054 20) As you see access methods estimates are ok, it is join result set which = is wrong. How to deal with it? May be a hack with CTE can help, but is there a way to improve = statistics correlation? > cheers >=20 > andrew >=20 >=20 >=20 > --=20 > Sent via pgsql-performance mailing list = (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance --Apple-Mail=_7931ACE9-FFA1-4840-B0AC-EA377BB909F2 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=iso-8859-1 andrew@dunslane.net> = wrote:

On 12/13/2012 05:12 PM, AI Rumman = wrote:
Why does the number of rows are = different in actual and estimated?



Isn't = that in the nature of estimates? An estimate is a heuristic guess at the = number of rows it will find for the given query or part of a query. It's = not uncommon for estimates to be out by several orders of magnitude. = Guaranteeing estimates within bounded accuracy and in a given short = amount of time (you don't want your planning time to overwhelm your = execution time) isn't = possible.


The main question i = think is what to do with it.

The problem starts = here

  ->  Hash Join =  (cost=3D9337.97..18115.71 rows=3D34489 width=3D244) (actual = time=3D418.054..1156.453 rows=3D205420 loops=3D1)
  =                     =                     =    Hash Cond: (customerdetails.customerid =3D entity.id)
      =                     =                   =  ->  Seq Scan on customerdetails  (cost=3D0.00..4752.46 = rows=3D327146 width=3D13) (actual time=3D0.021..176.389 rows=3D327328 = loops=3D1)
              =                     =            ->  Hash =  (cost=3D6495.65..6495.65 rows=3D227386 width=3D231) (actual = time=3D417.839..417.839 rows=3D205420 loops=3D1)
    =                     =                     =        Buckets: 32768  Batches: 1  Memory = Usage: 16056kB
            =                     =                   =  ->  Index Scan using entity_setype_idx on entity =  (cost=3D0.00..6495.65 rows=3D227386 width=3D231) (actual = time=3D0.033..2
53.880 rows=3D205420 loops=3D1)
 =                     =                     =                Index Cond: = ((setype)::text =3D 'con_s'::text)
        =                   =  ->  Index Scan using con_address_pkey on con_address =  (cost=3D0.00..0.27 rows=3D1 width=3D46) (actual time=3D0.003..0.004 = rows=3D1 loops=3D2054
20)

As you see = access methods estimates are ok, it is join result set which is = wrong.

How to deal with = it?

May be a hack with CTE can help, but is = there a way to improve statistics = correlation?

cheers

andrew



--
Sent via = pgsql-performance mailing list (pgsql-performance@postgre= sql.org)
To make changes to your subscription:
http://www.p= ostgresql.org/mailpref/pgsql-performance

= --Apple-Mail=_7931ACE9-FFA1-4840-B0AC-EA377BB909F2--