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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
