From: Craig Ringer <ringerc@ringerc.id.au>
To: Gregg Jaskiewicz <gryzman@gmail.com>
Cc: Misa Simic <misa.simic@gmail.com>
Cc: Hermann Matthes <hermann.matthes@web.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Paged Query
Date: Mon, 09 Jul 2012 20:02:38 +0800
Message-ID: <4FFAC85E.4010607@ringerc.id.au> (raw)
In-Reply-To: <CAJY59_icde+87mo65fJT8v9GdOTFemQFMNg5mxy52GCgjVe85w@mail.gmail.com>
References: <4FF43638.5080301@web.de>
<CAH3i69=B7VPj0rkbuxv9TLEF6V0DjB2xzcEUkf8U7BYULCOBug@mail.gmail.com>
<CAJY59_icde+87mo65fJT8v9GdOTFemQFMNg5mxy52GCgjVe85w@mail.gmail.com>
On 07/09/2012 07:55 PM, Gregg Jaskiewicz wrote:
> Use cursors.
> By far the most flexible. offset/limit have their down sides.
Do do cursors.
Keeping a cursor open across user think time has resource costs on the
database. It doesn't necessarily require keeping the transaction open
(with hold cursors) but it's going to either require a snapshot to be
retained or the whole query to be executed by the DB and stored somewhere.
Then the user goes away on a week's holiday and leaves their PC at your
"next" button.
All in all, limit/offset have better bounded and defined costs, albeit
not very nice ones.
--
Craig Ringer
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: ringerc@ringerc.id.au, gryzman@gmail.com, misa.simic@gmail.com, hermann.matthes@web.de
Subject: Re: Paged Query
In-Reply-To: <4FFAC85E.4010607@ringerc.id.au>
* 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