"Merlin Moncure" <merlin.moncure@rcsonline.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
useLIMIT 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.
