From: Gregory Stark <stark@enterprisedb.com>
To: Stephen Denne <Stephen.Denne@datamail.co.nz>
Cc: Pallav Kalva <pkalva@livedatagroup.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizing No matching record Queries
Date: Wed, 13 Feb 2008 00:16:44 +0000
Message-ID: <87r6fhaexf.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <F0238EBA67824444BC1CB4700960CB4804A4F34F@dmpeints002.isotach.com>
References: <47B2112C.5070707@livedatagroup.com>
<F0238EBA67824444BC1CB4700960CB4804A4F34F@dmpeints002.isotach.com>
"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!
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: stark@enterprisedb.com, Stephen.Denne@datamail.co.nz, pkalva@livedatagroup.com
Subject: Re: Optimizing No matching record Queries
In-Reply-To: <87r6fhaexf.fsf@oxford.xeocode.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