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

Re: Optimizing No matching record Queries

From: Pallav Kalva <pkalva(at)livedatagroup(dot)com>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: "Dean Gibson (DB Administrator)" <postgresql(at)ultimeth(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimizing No matching record Queries
Date: 2008-02-13 19:46:41
Message-ID: 47B34921.1040507@livedatagroup.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.
>


In response to

Responses

pgsql-performance by date

Next:From: Tore HalsetDate: 2008-02-13 20:28:21
Subject: Re: Dell Perc/6
Previous:From: Tore HalsetDate: 2008-02-13 19:45:01
Subject: Re: Dell Perc/6

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