Re: Performance with very large tables

On Mon, Jan 15, 2007 at 11:52:29 +0100,
Jan van der Weijde <Jan(dot)van(dot)der(dot)Weijde(at)attachmate(dot)com> wrote:
> Does anyone have a suggestion for this problem ? Is there for instance
> an alternative to LIMIT/OFFSET so that SELECT on large tables has a good
> performance ?

Depending on exactly what you want to happen, you may be able to continue
where you left off using a condition on the primary key, using the last
primary key value for a row that you have viewed, rather than OFFSET.
This will still be fast and will not skip rows that are now visible to
your transaction (or show duplicates when deleted rows are no longer visible
to your transaction).

Another option would be to do all of the selects in a single serializable
transaction. This will use the same snapshot for all of the selects, so
you won't have rows appear or disappear on you do to other concurrent
transactions.