Re: query overhead

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