Обсуждение: 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