From: Craig Ringer <ringerc@ringerc.id.au>
To: Andy Halsall <halsall_andy@hotmail.com>
Cc: postgres performance <pgsql-performance@postgresql.org>
Subject: Re: query overhead
Date: Sat, 21 Jul 2012 16:30:44 +0800
Message-ID: <500A68B4.6060309@ringerc.id.au> (raw)
In-Reply-To: <BAY171-W419B829CE21D20649C0793F5DB0@phx.gbl>
References: <BLU123-W2825EA0DA3346EA0D91091F5D10@phx.gbl>
<5000E75A.6030108@ringerc.id.au>
<BAY171-W1196FB3EA93E693D52E84BAF5D40@phx.gbl>
<500428AD.5060400@ringerc.id.au>
<BAY171-W419B829CE21D20649C0793F5DB0@phx.gbl>
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
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, halsall_andy@hotmail.com
Subject: Re: query overhead
In-Reply-To: <500A68B4.6060309@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