Re: cursor MOVE vs OFFSET in SELECT

On Mon, Oct 26, 2009 at 10:28 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> 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.
>

OK, so based on what Alvaro & Pavel said, the following two
possibilities are equivalent as far as the query planner is concerned:

$sql_query OFFSET 3000 LIMIT 12;

or

BEGIN;
SET LOCAL cursor_tuple_fraction=1;
DECLARE cur1 CURSOR FOR $sql_query;
MOVE 3000 IN cur1;
FETCH 12 FROM cur1;
COMMIT;

The problem is that in the latter case, the query planner doesn't know
in advance that we are going to skip the first 3000 rows.