Re: Optimizing No matching record Queries

"Stephen Denne" <Stephen.Denne@datamail.co.nz> writes:

> Pallav Kalva asked
> ...
>> and listing0_.fklistingsourceid=5525
> ...
>> order by listing0_.entrydate desc limit 10;
>
>>                ->  Index Scan Backward using idx_listing_entrydate on
>> listing listing0_  (cost=0.00..781557.28 rows=5118 width=107) (actual
>> time=2113544.412..2113544.412 rows=0 loops=1)
>>                      Filter: (fklistingsourceid = 5525)
>
> Would it help to have a combined index on fklistingsourceid, entrydate?

I think that would help. You already have a ton of indexes, you might consider
whether all your queries start with a listingsourceid and whether you can have
that as a prefix on the existing index.

Another thing to try is raising the stats target on fklistingsourceid and/or
entrydate. The estimate seems pretty poor. It could just be that the
distribution is highly skewed which is a hard case to estimate correctly.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!