Re: Performance again

On Mon, 16 Sep 2002, Mihai Gheorghiu wrote:

> [edited for brevity]
>
> Total runtime: 26575.85 msec
>
> vacuum verbose analyze tbas_transactions;
>
> After that, the run time for the query became 22.3s (not enough improvement
> over what
> explain analyze came up with).
>
> Looking forward to your comments,

So it sounds like the data has been taken from cache, to sime extend, or the
planner has switched to a sequential scan. My money is on the second of these,
you need to do another EXPLAIN [ANALYZE] to confirm this. The speed improvement
isn't great so I'd say that you're lucky that the nature of the data load gave
an index scan that took as short a time as it did.

The sort does seem to be taking a while. It is done to perform the GROUP
BY. I don't think there is a way to avoid it although you could try using an
index something like:

CREATE INDEX anotherindex
ON tbas_transactions (trxtype, account)
WHERE isposted = true;

I really don't know if that's going to enable the sort stage to be skipped
although if anything can I would have thought that would.

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants