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

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

From: Greg Stark <gsstark(at)mit(dot)edu>
To: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: best way to fetch next/prev record based on index
Date: 2004-07-27 17:12:31
Message-ID: 876589v0g0.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-performance

> Interestingly, it is possible to rewrite the above query by switching
> and with or and >= with >.  However when written that way, the planner
> almost never gets it right.

Well, note it's still not really getting it right even in your case. It's
doing an index scan on a>=a1 but if you have lots of values in your table
where a=a1 and b<b1 then it's going to unnecessarily read through all of
those.


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.

I expected WHERE (a,b,c) > (a1,b1,c1) to work however it doesn't. It appears
to mean a>a1 AND b>b1 AND c>c1 which isn't at all what you want. I imagine the
standard dictates this meaning.

> My problem is deceptively simple: how you read the next record from a
> table based on a given set of values?  In practice, this is difficult to
> implement.  If anybody can suggest a alternative/better way to this, I'm
> all ears.

I've done this a million times for simple integer keys, but I've never had to
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.

-- 
greg


In response to

pgsql-performance by date

Next:From: Merlin MoncureDate: 2004-07-27 17:28:08
Subject: Re: best way to fetch next/prev record based on index
Previous:From: Merlin MoncureDate: 2004-07-27 16:02:17
Subject: Re: best way to fetch next/prev record based on index

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