On Tue, 10 Feb 2004, CSN wrote:
>
> I have a pretty simple select query that joins a table
> (p) with 125K rows with another table (pc) with almost
> one million rows:
>
> select p.*
> from product_categories pc
> inner join products p
> on pc.product_id = p.id
> where pc.category_id = $category_id
> order by p.title
> limit 25
> offset $offset
This idiom looks to me a lot like "results paging". You have a query
that returns a lot of rows, and you are formatting them one page at a
time in your CGI or whatever.
In PostgreSQL, cursors do this very well:
BEGIN;
DECLARE resultset CURSOR FOR
select p.* from product_categories pc
inner join products p on pc.product_id = p.id
where pc.category_id = $category_id
order by p.title ;
MOVE $offset IN resultset;
FETCH 25 FROM resultset;
[ repeat as necessary ];
This does use some resources on the server side, but it is very much
faster than LIMIT/OFFSET.
The biggest "gotcha" about cursors is that their lifetime is limited to
the enclosing transaction, so they may not be appropriate for CGI-type
applications.
Bill Gribble
