To: pgsql-performance@postgresql.org
From: =?ISO-8859-1?Q?Nis_J=F8rgensen?= <nis@superlativ.dk>
Subject: Re: Incorrect estimates on columns
Date: Thu, 18 Oct 2007 11:29:08 +0200
Lines: 32
Message-ID: <ff7916$qml$1@ger.gmane.org>
References: <200710171334.24526.chris.kratz@vistashare.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
In-Reply-To: <200710171334.24526.chris.kratz@vistashare.com>

Chris Kratz skrev:
> Hello Everyone,
> 
> I'm struggling to get postgres to run a particular query quickly.  It
> seems that very early on, the planner seems to mis-estimate the
> number of rows returned by a join which causes it to assume that
> there is only 1 row as it goes up the tree.  It then picks a nested
> loop join which seems to cause the whole query to be slow.   Or at
> least if I turn off nestloop, it runs in 216ms.
> 
> explain analyze SELECT 1 FROM rpt_agencyquestioncache_171_0 par right
> outer join namemaster dem on (par.nameid = dem.nameid and
> dem.programid  = 171) right join activity_parentid_view ses on
> (par.activity = ses.activityid and ses.programid=171) left join   ( 
> select ct0.inter_agency_id,ct0.nameid from
> rpt_agencyquestioncache_171_0 ct0 join rpt_agencyquestioncache_171_2
> ct2 on ct2.participantid =ct0.participantid ) as   par30232   on
> (dem.nameid=par30232.nameid and par30232.inter_agency_id=30232) WHERE
>  ( ( (par.provider_lfm) ='Child Guidance Treatment Centers Inc.'))

The first two join-conditions seem strange - I think those are the cause
of the performance problems. The result of the first join, for instance,
is the return of all rows from dem, and matching rows from par IFF
dem.program_id =171 (NULLS otherwise).

In fact, since you are using a condition on the par table, you could
just as well use inner joins for
the first two cases.

Hope this helps,

Nis