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
