> -----Original Message----- > From: hyelluas [mailto:helen_yelluas@xxxxxxxxxx] > Sent: Friday, August 12, 2011 5:30 PM > To: pgsql-performance@xxxxxxxxxxxxxx > 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
