From: Pallav Kalva <pkalva@livedatagroup.com>
To: Richard Huxton <dev@archonet.com>
Cc: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizing No matching record Queries
Date: Wed, 13 Feb 2008 14:46:41 -0500
Message-ID: <47B34921.1040507@livedatagroup.com> (raw)
In-Reply-To: <47B2B058.7020804@archonet.com>
References: <47B2112C.5070707@livedatagroup.com>
<47B234C1.1090707@ultimeth.com>
<47B2B058.7020804@archonet.com>
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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pkalva@livedatagroup.com, dev@archonet.com, postgresql@ultimeth.com
Subject: Re: Optimizing No matching record Queries
In-Reply-To: <47B34921.1040507@livedatagroup.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox