To: Pallav Kalva <pkalva@livedatagroup.com>
cc: pgsql-performance@postgresql.org
Subject: Re: 8.2.4 Chooses Bad Query Plan 
In-reply-to: <46D43A8C.2080201@livedatagroup.com> 
References: <46D43A8C.2080201@livedatagroup.com>
Comments: In-reply-to Pallav Kalva <pkalva@livedatagroup.com>
	message dated "Tue, 28 Aug 2007 11:09:00 -0400"
Date: Tue, 28 Aug 2007 14:36:13 -0400
Message-ID: <1206.1188326173@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Pallav Kalva <pkalva@livedatagroup.com> writes:
>     We have recently upgraded our production database from 8.0.12 to
> 8.2.4, We have seen lot of improvements on 8.2.4 side but we are also
> seeing some queries which are slow.

>     Particularly this below query is really bad in 8.2.4 , I can get
> only the explain on this as explain analyze never finishes even after 20
> min.

What it's doing is scanning backward on activity1_.activityid and hoping
to find a row that matches all the other constraints soon enough to make
that faster than any other way of doing the query.  8.0 would have done
the same thing, I believe, if the statistics looked favorable for it.
So I wonder if you've forgotten to re-ANALYZE your data since migrating
(a pg_dump script won't do this for you).

>          ->  Index Scan using idx_accountactivity_fkactivityid on
> accountactivity accountact0_  (cost=0.00..3.94 rows=1 width=16)
>                Index Cond: (accountact0_.fkactivityid =
> activity1_.activityid)
>                Filter: (fkaccountid = 1455437)

>                      ->  Index Scan using
> idx_accountactivity_fkaccountid on accountactivity accountact0_ 
> (cost=0.00..1641.42 rows=1343 width=16) (actual time=115.348..864.416
> rows=10302 loops=1)
>                            Index Cond: (fkaccountid = 1455437)

The discrepancy in rowcount estimates here is pretty damning.
Even the 8.0 estimate wasn't really very good --- you might want to
consider increasing default_statistics_target.

			regards, tom lane