Re: [SQL] OFFSET impact on Performance???

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: Ragnar Hafstað <gnari(at)simnet(dot)is>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: [SQL] OFFSET impact on Performance???
Date: 2005-01-20 19:29:34
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB3412A75C8@Herge.rcsinc.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

> this will only work unchanged if the index is unique. imagine , for
> example if you have more than 50 rows with the same value of col.
>
> one way to fix this is to use ORDER BY col,oid

nope! oid is
1. deprecated
2. not guaranteed to be unique even inside a (large) table.

Use a sequence instead.

create view a_b as
select nextval('some_sequnce')::k, a.*, b.* from a, b [...]

select * from a_b where k > k1 order by k limit 1000
*or*
execute fetch_a_b(k1, 1000) <-- pass limit into prepared statement for extra flexibility.

Browse pgsql-performance by date

  From Date Subject
Next Message Jean-Max Reymond 2005-01-20 19:35:44 Re: PostgreSQL clustering VS MySQL clustering
Previous Message Ragnar Hafstað 2005-01-20 19:23:12 Re: [SQL] OFFSET impact on Performance???