On Mon, Oct 26, 2009 at 10:28 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> silly8888 escribió:
>> 2009/10/26 Grzegorz Jaśkiewicz <gryzman@gmail.com>:
>> >
>> >
>> > On Mon, Oct 26, 2009 at 10:30 AM, silly8888 <silly8888@gmail.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.
