Thread: cursor MOVE vs OFFSET in SELECT
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.
On Mon, Oct 26, 2009 at 10:30 AM, silly8888 <silly8888@gmail.com> wrote:
OFFSET/LIMIT. Afaik cursor always fetches everything.
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.
--
GJ
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.
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. OFFSET/LIMIT process same rows as cursor. There could be only one difference. Cursors uses strategy "fast first row", SELECT uses "minimum complete query time". Regards Pavel Stehule > > > -- > GJ >
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.
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.