From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mike Beaton <mjsbeaton@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Correct use of cursors for very large result sets in Postgres
In-reply-to: 
 <CAHzAAWTRCdbNkQqfS74jAtoT8ThBnQAuDm+=Uw-JOfgADQ3jtQ@mail.gmail.com>
References: 
 <CAHzAAWR4Hr8OQdjCuhDNsxTz3=qit4ufObSH=bJP9xxt2NLwLg@mail.gmail.com>
 <CAHzAAWRScAvevHu6AyK0ZZxnWo+SehXO9u51OSFvJEj9=61fXg@mail.gmail.com>
 <21063.1487349580@sss.pgh.pa.us>
 <CAHzAAWQhQKsE2isFr6bGL-Q06CTQSg6Sjd_+Nt-4h9Gqu0aVHg@mail.gmail.com>
 <26739.1487439829@sss.pgh.pa.us>
 <CAHzAAWTRCdbNkQqfS74jAtoT8ThBnQAuDm+=Uw-JOfgADQ3jtQ@mail.gmail.com>
Comments: In-reply-to Mike Beaton <mjsbeaton@gmail.com>
	message dated "Sun, 19 Feb 2017 07:54:18 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <17678.1487683929.1@sss.pgh.pa.us>
Date: Tue, 21 Feb 2017 08:32:09 -0500
Message-ID: <17679.1487683929@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Mike Beaton <mjsbeaton@gmail.com> writes:
> New TL;DR (I'm afraid): PostgreSQL is always generating a huge buffer file
> on `FETCH ALL FROM CursorToHuge`.

I poked into this and determined that it's happening because pquery.c
executes FETCH statements the same as it does with any other
tuple-returning utility statement, ie "run it to completion and put
the results in a tuplestore, then send the tuplestore contents to the
client".  I think the main reason nobody worried about that being
non-optimal was that we weren't expecting people to FETCH very large
amounts of data in one go --- if you want the whole query result at
once, why are you bothering with a cursor?

This could probably be improved, but it would (I think) require inventing
an additional PortalStrategy specifically for FETCH, and writing
associated code paths in pquery.c.  Don't know when/if someone might get
excited enough about it to do that.

			regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance