Performance again

On Mon, 16 Sep 2002, Mihai Gheorghiu wrote:

I'm still at 7.2.1. I restored the db over the weekend, and I was curious to
see how it behaves, so I ran

select account, sum(amount) from tbas_transactions where isposted and
trxtype = 'MP' group by account;

It took 1 min.
Then I ran

explain analyze select account, sum(amount) from tbas_transactions where
isposted and trxtype = 'MP' group by account;

and I got

psql:mg.txt:1: NOTICE: QUERY PLAN:

Aggregate (cost=12086.32..12094.23 rows=158 width=28) (actual
time=22862.15..26451.23 rows=16643 loops=1)
-> Group (cost=12086.32..12090.27 rows=1582 width=28) (actual
time=22861.92..25394.47 rows=345573 loops=1)
-> Sort (cost=12086.32..12086.32 rows=1582 width=28) (actual
time=22861.90..23652.72 rows=345573 loops=1)
-> Index Scan using trx_trxtype_idx on tbas_transactions
(cost=0.00..12002.25 rows=1582 width=28) (actual time=0.48..5209.36
rows=345573 loops=1)
Total runtime: 26575.85 msec

EXPLAIN

That's back to the time it used to take in 7.1.3
I notice a discrepancy between the number of rows reported in the two parts
(cost vs. actual). I also wonder what happens between 5209 and 22861 msec
(which may be my real problem).

That seems to be the sort step. This could be an indication that you
should try out higher sort_mem values and see if it lowers the time.

Also, you might be able to cheat with an index on account, trxtype
if you add a dummy indexable where clause for account (something that'd
select all rows). I don't know if that'll help in practice since I
haven't tried it with real data and the expense of the index scan may
be planned as higher than that of the sort, but it's at least a plan
that's considered.

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