To: "Pallav Kalva" <pkalva@livedatagroup.com>
Cc: "Richard Huxton" <dev@archonet.com>,
	"Dean Gibson \(DB Administrator\)" <postgresql@ultimeth.com>,
	<pgsql-performance@postgresql.org>
Subject: Re: Optimizing No matching record Queries
In-Reply-To: <47B34921.1040507@livedatagroup.com> (Pallav Kalva's message of
	"Wed\, 13 Feb 2008 14\:46\:41 -0500")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <47B2112C.5070707@livedatagroup.com>
	<47B234C1.1090707@ultimeth.com> <47B2B058.7020804@archonet.com>
	<47B34921.1040507@livedatagroup.com>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Wed, 13 Feb 2008 20:51:34 +0000
Message-ID: <877ih8bmw9.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"Pallav Kalva" <pkalva@livedatagroup.com> writes:

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

I think you're oversimplifying. Basically you were asking the planner for the
most recent record for a given user. The planner had the choice either of

a) going through all the records for a given user and picking the most recent,

or b) scanning the records from most recent to oldest and looking for the
given user.

It was a choice between two evils. If there are a lot of records for the user
then a) will be bad since it has to scan all of them to find the most recent
and if there are no records for the user then b) will be bad because it'll
have to go through all of the records to the beginning of time.

The suggested index lets it scan the records for the given user from most
recent to oldest without seeing any records for any other user.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!