Re: Queries with large ResultSets

Dave Cramer <pg@fastcrypt.com> writes:
> There's some confusion as to whether a cursor is materialized even
> inside a transaction. It could be that complicated queries will be
> stored on the disk too.

It depends on the query and on the cursor options.

If you don't say SCROLL nor WITH HOLD then the result isn't materialized
anywhere, it's just computed and delivered incrementally in response to
FETCH commands.

If you specify SCROLL and the query plan isn't one that's amenable to
being run backwards, then we materialize the result (ie, save aside each
row the first time it is read from the underlying query) so that we can
support FETCH BACKWARD.  By and large, only the simplest seqscan or
indexscan plans (no joins, aggregates, etc) are capable of being run
backwards and so can handle SCROLL without overhead.  You can use
"EXPLAIN DECLARE CURSOR" to see whether a particular query can do this
--- look at whether a Materialize node gets stuck atop the plan when
you add SCROLL.

If you specify WITH HOLD and don't close the cursor before transaction
end, then at transaction end the result is materialized: we read the
entire query output (including any rows you already read) and save it
aside to support future FETCHes.

            regards, tom lane