From: Gregory Stark <stark@enterprisedb.com>
To: Pallav Kalva <pkalva@livedatagroup.com>
Cc: Richard Huxton <dev@archonet.com>
Cc: Dean Gibson \(DB Administrator\) <postgresql@ultimeth.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizing No matching record Queries
Date: Wed, 13 Feb 2008 20:51:34 +0000
Message-ID: <877ih8bmw9.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <47B34921.1040507@livedatagroup.com>
References: <47B2112C.5070707@livedatagroup.com>
<47B234C1.1090707@ultimeth.com>
<47B2B058.7020804@archonet.com>
<47B34921.1040507@livedatagroup.com>
"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!
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, pkalva@livedatagroup.com, dev@archonet.com, postgresql@ultimeth.com
Subject: Re: Optimizing No matching record Queries
In-Reply-To: <877ih8bmw9.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