Performance again
От | Mihai Gheorghiu |
---|---|
Тема | Performance again |
Дата | |
Msg-id | 005601c25dc4$d74cc120$6e646464@New6.Travel обсуждение исходный текст |
Ответы |
Re: Performance again
Re: Performance again |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: