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.
>