Re: How to see memory usage using explain analyze ?

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