Message-ID: <47B34921.1040507@livedatagroup.com>
Date: Wed, 13 Feb 2008 14:46:41 -0500
From: Pallav Kalva <pkalva@livedatagroup.com>
User-Agent: Thunderbird 2.0.0.6 (X11/20070728)
MIME-Version: 1.0
To: Richard Huxton <dev@archonet.com>
CC: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>,
	pgsql-performance@postgresql.org
Subject: Re: Optimizing No matching record Queries
References: <47B2112C.5070707@livedatagroup.com>
	<47B234C1.1090707@ultimeth.com> <47B2B058.7020804@archonet.com>
In-Reply-To: <47B2B058.7020804@archonet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thanks! for all your replies, I tried increasing the statistics on 
fklistingsourceid to 1000 it made any difference.  Then I created an 
index on (fklistingsourceid,entrydate) it helped and it was fast.

This index would fix this problem but in general I would like to know 
what if  there are queries where it does "index scan backwards" and 
there is no "order by clause" and the query is still bad ? Would there 
be a case like that or the planner uses index scan backwards only when 
use order by desc also.


Richard Huxton wrote:
> Dean Gibson (DB Administrator) wrote:
>> The questions are:
>>
>> 1. Why in the planner scanning the entire idx_listing_entrydate, when 
>> I'd think it should be scanning the entire 
>> pk_listingstatus_listingstatusid ?
>
> It's looking at the ORDER BY and sees that the query needs the 10 most 
> recent, so tries searching by date. That's sensible where you are 
> going to have a lot of matches for fklistingsourceid.
>
> Which suggests that statistics for "fklistingsourceid" aren't high 
> enough, like Greg suggested. If that doesn't help, the index on 
> (fklistingsourceid,entrydate) that Stephen might well do so.
>
>> 2. Why is "Index Scan using pk_listingstatus_listingstatusid on 
>> listingstatus listingsta1_  (cost=0.00..0.27 rows=1 width=4) (never 
>> executed)" ?
>
> Because nothing comes out of the first index-scan.
>