Re: Optimizing No matching record Queries

"Stephen Denne" <Stephen(dot)Denne(at)datamail(dot)co(dot)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!