How to see memory usage using explain analyze ?

Hello,

I need to compare quiery execution : I have 2 tables partitioned by Datex (
daily):

summary_daily (
counter | bigint
datasource_id | integer
application_id | integer
action | character(1)
srcreporter_id | integer
destreporter_id | integer
bytes | bigint
srcusergroup_id | integer
datex | timestamp with time zone
root_cause_id | integer
rule_id | integer
srcgeo_id | integer
destgeo_id | integer
mlapp_id | bigint
)

app (
counter | bigint
bytes | bigint
action | character(1)
datex | timestamp with time zone
datasource_id | integer
application_id | integer
mlapp_id | bigint
root_cause_id | integer
)

The second table has been created from the first by aggregation.

table Summary has 9 mln rec per partition,
table App has 7 mln rec per partition

execution plan looks the same except the actual time is a huge difference.

work_mem=10mb,

days/partitions query from Summary query from App

1 2.5 sec 1 sec
3 5.5 sec 1.5 sec
7 60 sec 8 sec.

when I set session work_mem=60mb query for 7 days takes 8.5 sec vs 60 sec.

how can I see where/when it is using disk or memory?

explain analyze SELECT summary_app.action, sum(summary_app.counter),
summary_app.mlapp_id,
summary_app.application_id, sum(summary_app.bytes),
summary_app.root_cause_id
FROM summary_app
WHERE summary_app.datasource_id = 10 and
summary_app.datex >= '2011-08-03 00:00:00+00'::timestamp with time zone
AND summary_app.datex < '2011-08-06 00:00:00+00'::timestamp with time zone
group by mlapp_id, application_id,action, root_cause_id

HashAggregate (cost=8223.97..8226.97 rows=200 width=37) (actual
time=4505.607..4506.806 rows=3126 loops=1)
-> Append (cost=0.00..8213.42 rows=703 width=37) (actual
time=1071.043..4046.780 rows=283968 loops=1)
-> Seq Scan on summary_daily_data summary_app (cost=0.00..23.83
rows=1 width=37) (actual time=0.001..0.001 rows=0 loops=1)
Filter: ((datex >= '2011-08-03 00:00:00+00'::timestamp with
time zone) AND (datex < '2011-08-06 00:00:00+00'::timestamp with time zone)
AND (datasource_id = 10))
-> Bitmap Heap Scan on summ_daily_15191 summary_app
(cost=1854.89..2764.60 rows=234 width=37) (actual time=1071.041..1343.235
rows=94656 loops=1)
Recheck Cond: ((datasource_id = 10) AND (datex >= '2011-08-03
00:00:00+00'::timestamp with time zone) AND (datex < '2011-08-06
00:00:00+00'::timestamp with time zone))
-> BitmapAnd (cost=1854.89..1854.89 rows=234 width=0)
(actual time=1054.310..1054.310 rows=0 loops=1)
-> Bitmap Index Scan on ind_fw_15191
(cost=0.00..868.69 rows=46855 width=0) (actual time=17.896..17.896
rows=94656 loops=1)
Index Cond: (datasource_id = 10)
-> Bitmap Index Scan on ind_datex_15191
(cost=0.00..985.83 rows=46855 width=0) (actual time=1020.834..1020.834
rows=9370944 loops=1)
Index Cond: ((datex >= '2011-08-03
00:00:00+00'::timestamp with time zone) AND (datex < '2011-08-06
00:00:00+00'::timestamp with time zone))

the same query from the smaller table:

HashAggregate (cost=252859.36..253209.94 rows=23372 width=34) (actual
time=371.164..372.153 rows=3126 loops=1)
-> Append (cost=0.00..249353.62 rows=233716 width=34) (actual
time=11.028..115.915 rows=225072 loops=1)
-> Seq Scan on summary_app (cost=0.00..28.03 rows=1 width=37)
(actual time=0.001..0.001 rows=0 loops=1)
Filter: ((datex >= '2011-08-03 00:00:00+00'::timestamp with
time zone) AND (datex < '2011-08-06 00:00:00+00'::timestamp with time zone)
AND (datasource_id = 10))
-> Bitmap Heap Scan on summ_app_15191 summary_app
(cost=2299.40..82014.85 rows=72293 width=34) (actual time=11.027..31.341
rows=75024 loops=1)
Recheck Cond: ((datasource_id = 10) AND (datex >= '2011-08-03
00:00:00+00'::timestamp with time zone) AND (datex < '2011-08-06
00:00:00+00'::timestamp with time zone))
-> Bitmap Index Scan on summ_app_fw_datex_15191
(cost=0.00..2281.32 rows=72293 width=0) (actual time=10.910..10.910
rows=75024 loops=1)
Index Cond: ((datasource_id = 10) AND (datex >=
'2011-08-03 00:00:00+00'::timestamp with time zone) AND (datex < '2011-08-06
00:00:00+00'::timestamp with time zone))

Why the difference is so large? How I can tune this query?

thank you.

Helen

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-see-memory-usage-using-explain-analyze-tp4694681p4694681.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.