DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
	h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=1+wN9z3+M9WnNpXNRYe2rVIzLXAQfOygF+Qk/ZLXcY4hxtQFtJYuYnGWstcszrS6QbX1cukF+Bfdyecb9oPdyJoEHF5yeTs8rCwKQcXFAJEGe2m7ii9YjV/4cFHqWnRrBsEhGuE4PThVEVwt45d8llbCHSwf7nFF6509krcN08I=;
References: 
 <CAGoODpdTE__dLDhJe2d_4dwZj0YOx1vp7O-Lrwj2pYtxdMeBKw@mail.gmail.com>
Message-ID: <1365003273.32054.YahooMailNeo@web162905.mail.bf1.yahoo.com>
Date: Wed, 3 Apr 2013 08:34:33 -0700 (PDT)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Planner is getting wrong row count
To: AI Rumman <rummandba@gmail.com>,
	postgres performance list <pgsql-performance@postgresql.org>
In-Reply-To: 
 <CAGoODpdTE__dLDhJe2d_4dwZj0YOx1vp7O-Lrwj2pYtxdMeBKw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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 =3D 100.
> I execute vacuum analyze each night.

> Hash Join=A0 (cost=3D14067.90..28066.53 rows=3D90379 width=3D26) (actual =
time=3D536.009..1772.910 rows=3D337139 loops=3D1)
>=A0=A0=A0 Hash Cond: (leads.leadid =3D entity.id)
>=A0=A0=A0 ->=A0 Seq Scan on leads=A0 (cost=3D0.00..7764.83 rows=3D533002 w=
idth=3D18) (actual time=3D0.008..429.576 rows=3D532960 loops=3D1)
>=A0=A0=A0=A0=A0=A0=A0=A0=A0 Filter: (converted =3D 0)
>=A0=A0=A0 ->=A0 Hash=A0 (cost=3D9406.25..9406.25 rows=3D372932 width=3D16)=
 (actual time=3D535.800..535.800 rows=3D342369 loops=3D1)
>=A0=A0=A0=A0=A0=A0=A0=A0=A0 Buckets: 65536=A0 Batches: 1=A0 Memory Usage: =
16049kB
>=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Index Scan using entity_type_idx on enti=
ty=A0 (cost=3D0.00..9406.25 rows=3D372932 width=3D16) (actual time=3D0.030.=
.305.250 rows=3D342369 loops=3D1)
>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: ((type)::text =
=3D 'Leads'::text)

The estimates match up well with actual until the hash join.=A0 That
suggests that there is a correlation between the join conditions
and the other selection criteria which the planner doesn't know
about.=A0 Right now PostgreSQL has no way to adjust estimates based
on such correlations.=A0 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


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