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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2004-07-27 17:28:08 | Re: best way to fetch next/prev record based on index |
Previous Message | Merlin Moncure | 2004-07-27 16:02:17 | Re: best way to fetch next/prev record based on index |