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
Date: Tue, 21 Feb 2017 08:32:09 -0500
Message-ID: <17679.1487683929@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mjsbeaton@gmail.com
Subject: Re: Correct use of cursors for very large result sets in Postgres
In-Reply-To: <17679.1487683929@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox