Re: cursor MOVE vs OFFSET in SELECT

silly8888 escribió:
> 2009/10/26 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>:
> >
> >
> > On Mon, Oct 26, 2009 at 10:30 AM, silly8888 <silly8888(at)gmail(dot)com> wrote:
> >>
> >> Suppose that you have a query, say $sql_query, which is very
> >> complicated and produces many rows. Which of the following is going to
> >> be faser:
> >>
> >>    $sql_query OFFSET 3000 LIMIT 12;
> >>
> >> or
> >>
> >>    BEGIN;
> >>    DECLARE cur1 CURSOR FOR $sql_query;
> >>    MOVE 3000 IN cur1;
> >>    FETCH 12 FROM cur1;
> >>    COMMIT;
> >>
> >> Naturally, the former cannot be slower than the latter. So my question
> >> essentially is whether the MOVE operation on a cursor is
> >> (significantly) slower that a OFFSET on the SELECT.
> >
> >
> > OFFSET/LIMIT. Afaik cursor always fetches everything.
>
> Well, in my experiments they always perform the same. I suspect that
> the way SELECT/OFFSET is implemented is not much different than
> cursor/MOVE.

The cursor could choose a different plan due to the "fast startup"
behavior that Pavel alludes to. You can actually change that by setting
the cursor_tuple_fraction parameter. Whether this plan is faster or
slower than the other one is problem dependent.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.