The dataset for a cursor is prepared by the server at the time of execution of the first FETCH. The client application receives only the results of subsequent FETCH statements.
If the server cannot use indexes to maintain a cursor, the temporary dataset is created. You can perform this simple test:
create table test(i int, v text);
insert into test
select i, i::text
from generate_series(1, 5000000) i;
Execute the statements in this script one by one:
begin;
declare cur cursor
for select * from test
order by random(); -- 17 ms
fetch next cur; -- 37294 ms (*)
fetch next cur; -- 0 ms
fetch prior cur; -- 0 ms
fetch absolute 1000000 cur; -- 181 ms
fetch relative 1000000 cur; -- 163 ms
fetch first cur; -- 0 ms
fetch last cur; -- 0 ms
rollback;
First FETCH (*) performs roughly around the same time as the creation of a similar temporary table:
create temp table temp_test as
select * from test
order by random(); -- 51684 ms
Some drivers may have their own implementation of cursor on client side. This should be explicitly described in the driver's documentation.
