Обсуждение: Performance again

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).
Then I ran

vacuum verbose analyze tbas_transactions;

and I got

psql:mg2.txt:1: NOTICE:  --Relation tbas_transactions--
psql:mg2.txt:1: NOTICE:  Pages 14965: Changed 0, Empty 0; Tup 632852: Vac 0,
Keep 0, UnUsed 0.
Total CPU 0.96s/0.13u sec elapsed 1.30 sec.
psql:mg2.txt:1: NOTICE:  --Relation pg_toast_17242--
psql:mg2.txt:1: NOTICE:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0,
UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
psql:mg2.txt:1: NOTICE:  Analyzing tbas_transactions
VACUUM

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,

Mihai

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