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

> 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