pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: andrew@pillette.com
To: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: best way to fetch next/prev record based on index
Date: Tue, 27 Jul 2004 10:37:24 -0700
Message-ID: <200407271737.i6RHbOP04615@pillette.com> (raw)

"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 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.



view thread (34+ messages)  latest in thread

Message-ID: <200407271737.i6RHbOP04615@pillette.com>
Permalink:  ../200407271737.i6RHbOP04615@pillette.com/
Also on:    postgresql.org/message-id/200407271737.i6RHbOP04615@pillette.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: andrew@pillette.com, merlin.moncure@rcsonline.com
  Subject: Re: best way to fetch next/prev record based on index
  In-Reply-To: <200407271737.i6RHbOP04615@pillette.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox