From: Kevin Grittner <kgrittn@ymail.com>
To: AI Rumman <rummandba@gmail.com>
To: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Planner is getting wrong row count
Date: Wed, 3 Apr 2013 08:34:33 -0700 (PDT)
Message-ID: <1365003273.32054.YahooMailNeo@web162905.mail.bf1.yahoo.com> (raw)
In-Reply-To: <CAGoODpdTE__dLDhJe2d_4dwZj0YOx1vp7O-Lrwj2pYtxdMeBKw@mail.gmail.com>
References: <CAGoODpdTE__dLDhJe2d_4dwZj0YOx1vp7O-Lrwj2pYtxdMeBKw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
AI Rumman <rummandba@gmail.com> wrote:
> Why is the following query getting wrong estimation of rows?
> I am using Postgresql 9.2.1 with default_statistics_target = 100.
> I execute vacuum analyze each night.
> Hash Join (cost=14067.90..28066.53 rows=90379 width=26) (actual time=536.009..1772.910 rows=337139 loops=1)
> Hash Cond: (leads.leadid = entity.id)
> -> Seq Scan on leads (cost=0.00..7764.83 rows=533002 width=18) (actual time=0.008..429.576 rows=532960 loops=1)
> Filter: (converted = 0)
> -> Hash (cost=9406.25..9406.25 rows=372932 width=16) (actual time=535.800..535.800 rows=342369 loops=1)
> Buckets: 65536 Batches: 1 Memory Usage: 16049kB
> -> Index Scan using entity_type_idx on entity (cost=0.00..9406.25 rows=372932 width=16) (actual time=0.030..305.250 rows=342369 loops=1)
> Index Cond: ((type)::text = 'Leads'::text)
The estimates match up well with actual until the hash join. That
suggests that there is a correlation between the join conditions
and the other selection criteria which the planner doesn't know
about. Right now PostgreSQL has no way to adjust estimates based
on such correlations. If an inefficient plan is being chosen due
to this, there are a few tricks to coerce the plan.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kgrittn@ymail.com, rummandba@gmail.com
Subject: Re: Planner is getting wrong row count
In-Reply-To: <1365003273.32054.YahooMailNeo@web162905.mail.bf1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox