Re: How to see memory usage using explain analyze ?

> -----Original Message-----
> From: hyelluas [mailto:helen_yelluas(at)mcafee(dot)com]
> Sent: Friday, August 12, 2011 5:30 PM
> To: pgsql-performance(at)postgresql(dot)org
> Subject: 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.

Helen,

I'm probably a bit late answering your question.
But, just in case...

It looks like one table has "combined" index summ_app_fw_datex_15191 on
both: datasource_id and datex, which works better than 2 separate
indexes ind_datex_15191(datex) and ind_fw_15191(datasource_id), that you
have on the other table.
Besides, this:

-> 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)

Shows that statistics on ind_datex_15191 are completely "out of wack"
(expected rows=46855, actual rows=9370944).

HTH,
Igor Neyman