Skip site navigation (1) Skip section navigation (2)

Re: best way to fetch next/prev record based on index

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: <gsstark(at)mit(dot)edu>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: best way to fetch next/prev record based on index
Date: 2004-07-27 17:28:08
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB34101AEFD@Herge.rcsinc.local (view raw or flat)
Thread:
Lists: pgsql-performance
Greg wrote:
> One thing that can help is to add ORDER BY a,b,c LIMIT 1 to your
query.
> That
> will virtually guarantee that it uses an index scan, which will at
least
> avoid
> making it scan all the records *after* finding the match. However it
still
> doesn't seem to make Postgres use an Index Cond to allow it to do an
> instant
> lookup.

Yes, order by/limit was accidentally left of my original example.  My
problem is with the word 'virtually'.

> do it for multi-column keys. It seems it would be nice if some syntax
> similar
> to (a,b,c) > (a1,b1,c1) worked for this.

'nice' would be an understatement...
if the above syntax is not defined in the standard, I would humbly
suggest, well, beg for it to work as you thought it did.  That would be
GREAT!  ISMT it may be that that is in fact standard...(I don't have it,
so I don't know).

Merlin



Responses

pgsql-performance by date

Next:From: andrewDate: 2004-07-27 17:37:24
Subject: Re: best way to fetch next/prev record based on index
Previous:From: Greg StarkDate: 2004-07-27 17:12:31
Subject: Re: best way to fetch next/prev record based on index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group