How does cursor and fetch work in PostgreSQL

Question

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

At the first, I assumed that

When CURSOR is declared with a select statement, DB will execute the select statement and then have the result stored in DB memory.
When FETCH is called on the CURSOR, DB will just read the result moving on the CURSOR.
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.

SELECT is not executed when you declare the cursor but when you use it for the first time (the first FETCH). See also this answer. — klin
– klin, Commented Dec 31, 2018 at 14:03
@klin According to my test, DECLARE seems to do something. Please check my edited question. — SangminKim
– SangminKim, Commented Dec 31, 2018 at 15:39

Tometzky · Accepted Answer · 2019-01-01 12:29:03Z

3

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.

answered Jan 1, 2019 at 12:29

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SangminKim Over a year ago

Thank you for your answering. In an example of the question, If FETCH FORWARD 10000 FROM "123" takes 4sec, it is just because of slow fetching operation, not any other operation? If so, 4sec is a reasonable time for 10000 rows?

Tometzky Over a year ago

If you did commit after a declare then fetch should be quite fast always.

SangminKim Over a year ago

I post a relative question in here. I hope you would help me again.

Collectives™ on Stack Overflow

How does cursor and fetch work in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related