Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 4EA0C16FA501
	for <pgsql-performance@postgresql.org>;
	Mon,  9 Jul 2012 09:03:00 -0300 (ADT)
Received: from mail-pb0-f46.google.com ([209.85.160.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ringerc@ringerc.id.au>) id 1SoCg6-0008CC-O5
	for pgsql-performance@postgresql.org; Mon, 09 Jul 2012 12:03:00 +0000
Received: by pbbrp8 with SMTP id rp8so19167114pbb.19
	for <pgsql-performance@postgresql.org>;
	Mon, 09 Jul 2012 05:02:44 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding
	:x-gm-message-state;
	bh=NOy65xYF5oMTtTd3/zgq8ig3JLPN3FWeFbaeuKj5Aj0=;
	b=PfI53Bgrkwq/pDfV+uBhCo8rxqA3vZGNDGod38QXYXXuKxdoIbf8Joig7QtJW/vqvv
	a7W8QNx41vRz8iQS+MeV/7A3tzp506G4w7e82SN3fIsBOqa71Jr8JC0+jaHo/vNIV1bP
	IUaRwh/HqcQCHhgUdwk6N3WBoekSsanUxI9nkBWzje5n2m7S+dGqsLEuHnv0LkfcqUt9
	zGzmr+7L87SDGpJZ005/cGISZACNPHwhh8Dj/De6ZkauNm8UTSlQE5tYYVeQH+aCbqod
	A3krpozt0ksLZKw/DtCQOwc08Y6qrMOYkcnsZo4+vYEq3C/DWTwAy9YgSbpyxWzCOJZe
	YSfw==
Received: by 10.68.217.3 with SMTP id ou3mr60033067pbc.117.1341835364328;
	Mon, 09 Jul 2012 05:02:44 -0700 (PDT)
Received: from ayaki.localdomain (124-169-169-118.dyn.iinet.net.au.
	[124.169.169.118])
	by mx.google.com with ESMTPS id vz9sm27616484pbc.12.2012.07.09.05.02.41
	(version=SSLv3 cipher=OTHER); Mon, 09 Jul 2012 05:02:43 -0700 (PDT)
Message-ID: <4FFAC85E.4010607@ringerc.id.au>
Date: Mon, 09 Jul 2012 20:02:38 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
To: Gregg Jaskiewicz <gryzman@gmail.com>
CC: Misa Simic <misa.simic@gmail.com>,
	Hermann Matthes <hermann.matthes@web.de>, pgsql-performance@postgresql.org
Subject: Re: Paged Query
References: <4FF43638.5080301@web.de>
	<CAH3i69=B7VPj0rkbuxv9TLEF6V0DjB2xzcEUkf8U7BYULCOBug@mail.gmail.com>
	<CAJY59_icde+87mo65fJT8v9GdOTFemQFMNg5mxy52GCgjVe85w@mail.gmail.com>
In-Reply-To: 
 <CAJY59_icde+87mo65fJT8v9GdOTFemQFMNg5mxy52GCgjVe85w@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Gm-Message-State: 
 ALoCoQkpIYqy8Zotc6XWi/sQFHufJMA8MjgxTbhiBsIQXPFFmItb/QGQOufHym6+OOYja+i8x0B/
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201207/85
X-Sequence-Number: 47266

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