Re: 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.