Message-ID: <500A68B4.6060309@ringerc.id.au>
Date: Sat, 21 Jul 2012 16:30:44 +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: Andy Halsall <halsall_andy@hotmail.com>
CC: postgres performance <pgsql-performance@postgresql.org>
Subject: Re: query overhead
References: <BLU123-W2825EA0DA3346EA0D91091F5D10@phx.gbl>,
	<5000E75A.6030108@ringerc.id.au>
	<BAY171-W1196FB3EA93E693D52E84BAF5D40@phx.gbl>,
	<500428AD.5060400@ringerc.id.au>
	<BAY171-W419B829CE21D20649C0793F5DB0@phx.gbl>
In-Reply-To: <BAY171-W419B829CE21D20649C0793F5DB0@phx.gbl>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit

On 07/17/2012 11:33 PM, Andy Halsall wrote:

>
>  If you're working with ISAM-like access though, cursors may well be 
> very helpful for you. It's a pity for your app that Pg doesn't support 
> cursors that see changes committed after cursor creation, since these 
> are ideal when emulating ISAM "next record" / "previous record" access 
> models. They're still suitable for tasks where you know the app 
> doesn't need to see concurrently modified data, though.
>
> > That's right, that would've been ideal behaviour for us. We're going 
> to manage our own shared cache in the application layer to give 
> similar functionality. We have lots of reads but fewer writes.

How have you gone with this? I'm curious.

By the way, when replying it's the convention to indent the text written 
by the person you're replying to, not indent your own text. It's kind of 
hard to read.


> > In the context of what we've been talking about, we're reading a set 
> of information which is ordered in a reasonably complex way. Set is 
> about 10000 records and requires a table join. This sort takes a while 
> as it heap scans - couldn't persuade it to use indexes.
>
> > Having read the set, the application "gets next" until the end. To 
> start with we were re-establishing the set (minus the previous record) 
> and choosing the first (LIMIT 1) on each "get next" - obviously a 
> non-starter. We moved to caching the record keys for the set and only 
> visiting the database for the specific records on each "get next" - 
> hence the questions about round trip overhead for small queries.
Given that pattern, why aren't you using a cursor? Do you need to see 
concurrent changes? Is the cursor just held open too long, affecting 
autovacum?

--
Craig Ringer