Search code examples
postgresqlcursor

How does cursor and fetch work in PostgreSQL

I want to know how CURSOR and FETCH work internally in PostgreSQL.

At the first, I assumed that

  1. When CURSOR is declared with a select statement, DB will execute the select statement and then have the result stored in DB memory.

  2. When FETCH is called on the CURSOR, DB will just read the result moving on the CURSOR.

  3. When the CURSOR is closed, results stored is removed from memory.

If my assumption is correct, FETCH should have short response time regardless of how the select statement is complex.

However, When I test, FETCH show more poor response time than I expected like It has done something I haven't expected.

How do they work?

--------- EDIT ---------

The below is what I get when I test with my actual database table. (select statement contains join clause for 3 tables and one of the tables has 3 million rows)

(   8sec) DECLARE “123" NO SCROLL CURSOR WITH HOLD FOR SELECT .....
(0.04sec) FETCH FORWARD 2 FROM "123";
(   4sec) FETCH FORWARD 10000 FROM "123";

--------- EDIT ---------

The 4sec response time in FETCH FORWARD 10000 FROM "123" seems because of pgcli(PostgreSQL client tool) I used.

I don't know why but It has clearly came to be fast up to 0.04sec after changing client tool.

Solution

  • SQL Commands: DECLARE:

    In the current implementation, the rows represented by a held cursor are copied into a temporary file or memory area so that they remain available for subsequent transactions.

    It depends on whether you used the cursor from a single transaction or used "WITH HOLD" and multiple transactions.

    If you use "WITH HOLD" then on "COMMIT" of the transaction which called "DECLARE" a temporary table is created with all the data from the cursor. If the data size is large, the table is saved to disk and can therefore be somewhat slower to fetch. But not that slow, as this should be a sequential scan for some reasonable number of rows.

    tometzky=> begin;
BEGIN
Time: 0.301 ms
tometzky=> declare c no scroll cursor with hold for select pg_sleep(1) from generate_series(1,6);
DECLARE CURSOR
Time: 1.140 ms
tometzky=> commit;
COMMIT
Time: 6007.180 ms (00:06.007)
tometzky=> fetch forward 3 from c;
 pg_sleep 
----------



(3 rows)

Time: 0.384 ms
tometzky=> fetch forward 3 from c;
 pg_sleep 
----------



(3 rows)

Time: 0.336 ms
tometzky=> fetch forward 3 from c;
 pg_sleep 
----------
(0 rows)

Time: 0.338 ms

    When you use a cursor from the same transaction that called DECLARE then each FETCH will return as soon as the requested number of rows is available:

    tometzky=> begin;
BEGIN
Time: 0.301 ms
tometzky=> declare c no scroll cursor for select pg_sleep(1) from generate_series(1,6);
DECLARE CURSOR
Time: 1.225 ms
tometzky=> fetch forward 3 from c;
 pg_sleep 
----------



(3 rows)

Time: 3004.041 ms (00:03.004)
tometzky=> fetch forward 3 from c;
 pg_sleep 
----------



(3 rows)

Time: 3003.855 ms (00:03.004)
tometzky=> fetch forward 3 from c;
 pg_sleep 
----------
(0 rows)

Time: 0.229 ms
tometzky=> commit;
COMMIT
Time: 0.444 ms

    But, for example, if the query you used requires sorting as a last step, it will have to fetch all rows first anyway to sort them.