Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 3ED6A2E006E
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 12 Feb 2008 20:16:53 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 20082-03 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 12 Feb 2008 20:16:50 -0400 (AST)
Received: from oxford.xeocode.com (unknown [87.127.95.196])
	by postgresql.org (Postfix) with ESMTP id ECBD02E002E
	for <pgsql-performance@postgresql.org>;
	Tue, 12 Feb 2008 20:16:49 -0400 (AST)
Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com)
	by oxford.xeocode.com with esmtp (Exim 4.69)
	(envelope-from <stark@enterprisedb.com>)
	id 1JP5Ir-0001PK-5S; Wed, 13 Feb 2008 00:16:45 +0000
To: "Stephen Denne" <Stephen.Denne@datamail.co.nz>
Cc: "Pallav Kalva" <pkalva@livedatagroup.com>,
	<pgsql-performance@postgresql.org>
Subject: Re: Optimizing No matching record Queries
In-Reply-To: 
 <F0238EBA67824444BC1CB4700960CB4804A4F34F@dmpeints002.isotach.com>
	(Stephen Denne's message of "Wed\, 13 Feb 2008 11\:09\:29 +1300")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.performance" 4227)
References: <47B2112C.5070707@livedatagroup.com>
	<F0238EBA67824444BC1CB4700960CB4804A4F34F@dmpeints002.isotach.com>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Wed, 13 Feb 2008 00:16:44 +0000
Message-ID: <87r6fhaexf.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/125
X-Sequence-Number: 28667

"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!