Message-ID: <46D4777B.5030701@livedatagroup.com>
Date: Tue, 28 Aug 2007 15:28:59 -0400
From: Pallav Kalva <pkalva@livedatagroup.com>
User-Agent: Thunderbird 1.5.0.10 (X11/20060911)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org
Subject: Re: 8.2.4 Chooses Bad Query Plan
References: <46D43A8C.2080201@livedatagroup.com>
	<1206.1188326173@sss.pgh.pa.us>
In-Reply-To: <1206.1188326173@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi Tom,

   Thanks! for the reply, see my comments below

Tom Lane wrote:
> 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).
>
>   

So, if I understand this correctly it keeps doing index scan backwards
until it finds
a matching record , if it cant find any record it pretty much scans the
whole table
using "index scan backward" ?

If I have no matching record I pretty much wait until the query
finishes  ? 

Is there anything else I can do to improve the query ?

I have analyzed tables again and also my default_stats_target is set to
100,
still it shows the same plan.

>>          ->  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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>