[GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cursor MOVE vs OFFSET in SELECT
2009/10/26 Grzegorz Jaśkiewicz gryz...@gmail.com: On Mon, Oct 26, 2009 at 10:30 AM, silly silly8...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cursor MOVE vs OFFSET in SELECT
2009/10/26 Grzegorz Jaśkiewicz gryz...@gmail.com: On Mon, Oct 26, 2009 at 10:30 AM, silly silly8...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cursor MOVE vs OFFSET in SELECT
silly escribió: 2009/10/26 Grzegorz Jaśkiewicz gryz...@gmail.com: On Mon, Oct 26, 2009 at 10:30 AM, silly silly8...@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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cursor MOVE vs OFFSET in SELECT
On Mon, Oct 26, 2009 at 10:28 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: silly escribió: 2009/10/26 Grzegorz Jaśkiewicz gryz...@gmail.com: On Mon, Oct 26, 2009 at 10:30 AM, silly silly8...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
