Skip site navigation (1) Skip section navigation (2)

Re: Incorrect estimates on columns

From: Nis Jørgensen <nis(at)superlativ(dot)dk>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Incorrect estimates on columns
Date: 2007-10-18 09:29:08
Message-ID: ff7916$qml$1@ger.gmane.org (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

pgsql-performance by date

Next:From: John MajorDate: 2007-10-18 17:01:01
Subject: How to improve speed of 3 table join &group (HUGE tables)
Previous:From: Stéphane SchildknechtDate: 2007-10-18 06:19:43
Subject: Re: Vacuum goes worse

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group