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

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

From: andrew(at)pillette(dot)com
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:37:24
Message-ID: 200407271737.i6RHbOP04615@pillette.com (view raw or flat)
Thread:
Lists: pgsql-performance
"Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> wrote ..
[snip]
> select * from t where
> 	a >= a1 and
>      (a >  a1 or b >= b1) and
>      (a >  a1 or b > b1 or c > c1)

I don't see why this is guaranteed to work without an ORDER BY clause, even if TABLE t is clustered on the correct index. Am I missing something? I have two suggestions: 

(1) I think I would have written 

SELECT * FROM t WHERE
(a >= a1 AND b>=b1 AND c>=c1) ORDER BY a,b,c LIMIT 1 OFFSET 1;

using the way LIMIT cuts down on sort time (I've never tried it with both LIMIT and OFFSET, though; you could always use LIMIT 2 and skip a record client-side if that works better).

(2) I've seen code where depending on the types and values of the fields, it was possible to construct a string from a, b, c by some sort of concatenation where the index now agreed with the lexicographic (dictionary) ordering on the string. Postgres could do that with a functional index, if your values can be used with this trick.

pgsql-performance by date

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

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