Hi,
I have two similar queries that calculate "group by" summaries over a huge
table (74.6mil rows).
The only difference between two queries is the number of columns that group by
is performed on.
This difference is causing two different plans which are vary so very much in
performance.
Postgres is 8.4.4. on Linux 64bit. Work_mem is 4GB for both queries and
effective_cache_size = 30GB (server has 72GB RAM).
Both queries are 100% time on CPU (data is all in buffer cache or OS cache).
My questions are:
1) Is there a way to force plan that uses hashaggregate for the second
query?
2) I am not trying to achieve any particular execution time for the query,
but I noticed that when "disk sort" kicks in (and that happens eventually once
the dataset is large enough) the query drastically slows down, even if there is
no physical IO going on. I wonder if it's possible to have predictable
performance rather than sudden drop.
3) Why hashAggregate plan uses so much less memory (work_mem) than the
plan with groupAggregate/sort? HashAggregate plan for Query1 works even with
work_mem='2GB'; The second plan decides to use disk sort even with
work_mem='4GB'. Why sort is so memory greedy? Are there any plans to address
the sorting memory efficiency issues?
Thank you!
Query1:
explain analyze
smslocate_edw-# SELECT
smslocate_edw-# month_code,
smslocate_edw-# short_code,
smslocate_edw-# gateway_carrier_id,
smslocate_edw-# mp_code,
smslocate_edw-# partner_id,
smslocate_edw-# master_company_id,
smslocate_edw-# ad_id,
smslocate_edw-# sc_name_id,
smslocate_edw-# sc_sports_league_id,
smslocate_edw-# sc_sports_alert_type,
smslocate_edw-# al_widget_id,
smslocate_edw-# keyword_id,
smslocate_edw-# cp_id,
smslocate_edw-# sum(coalesce(message_count,0)), -- message_cnt
smslocate_edw-# sum(coalesce(message_sellable_count,0)), --
message_sellable_cnt
smslocate_edw-# sum(coalesce(ad_cost_sum,0)), -- ad_cost_sum
smslocate_edw-# NULL::int4, --count(distinct device_number), --
unique_user_cnt
smslocate_edw-# NULL::int4, --count(distinct case when
message_sellable_count <> 0 then device_number end), -- unique_user_sellable_cnt
smslocate_edw-# NULL, --
unique_user_first_time_cnt
smslocate_edw-# 1, -- ALL
smslocate_edw-# CURRENT_TIMESTAMP
smslocate_edw-# from staging.agg_phones_monthly_snapshot
smslocate_edw-# group by
smslocate_edw-# month_code,
smslocate_edw-# short_code,
smslocate_edw-# gateway_carrier_id,
smslocate_edw-# mp_code,
smslocate_edw-# partner_id,
smslocate_edw-# master_company_id,
smslocate_edw-# ad_id,
smslocate_edw-# sc_name_id,
smslocate_edw-# sc_sports_league_id,
smslocate_edw-# sc_sports_alert_type,
smslocate_edw-# al_widget_id,
smslocate_edw-# keyword_id,
smslocate_edw-# cp_id
smslocate_edw-# ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------
HashAggregate (cost=5065227.32..5214455.48 rows=7461408 width=64) (actual
time=183289.883..185213.565 rows=2240716 loops=1)
-> Append (cost=0.00..2080664.40 rows=74614073 width=64) (actual
time=0.030..58952.749 rows=74614237 loops=1)
-> Seq Scan on agg_phones_monthly (cost=0.00..11.50 rows=1
width=102) (actual time=0.002..0.002 rows=0 loops=1)
Filter: (month_code = '2010M04'::bpchar)
-> Seq Scan on agg_phones_monthly_2010m04 agg_phones_monthly
(cost=0.00..2080652.90 rows=74614072 width=64) (actual time=0.027..42713.387
rows=74614237 loops=1)
Filter: (month_code = '2010M04'::bpchar)
Total runtime: 185519.997 ms
(7 rows)
Time: 185684.396 ms
Query2:
explain analyze
smslocate_edw-# SELECT
smslocate_edw-# month_code,
smslocate_edw-# gateway_carrier_id,
smslocate_edw-# sum(coalesce(message_count,0)), -- message_cnt
smslocate_edw-# sum(coalesce(message_sellable_count,0)), --
message_sellable_cnt
smslocate_edw-# sum(coalesce(ad_cost_sum,0)), -- ad_cost_sum
smslocate_edw-# count(distinct device_number), -- unique_user_cnt
smslocate_edw-# count(distinct case when message_sellable_count <> 0 then
device_number end), -- unique_user_sellable_cnt
smslocate_edw-# NULL, --
unique_user_first_time_cnt
smslocate_edw-# 15, -- CARRIER
smslocate_edw-# CURRENT_TIMESTAMP
smslocate_edw-# from staging.agg_phones_monthly_snapshot
smslocate_edw-# group by
smslocate_edw-# month_code,
smslocate_edw-# gateway_carrier_id
smslocate_edw-# ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------
GroupAggregate (cost=13877783.42..15371164.88 rows=40000 width=37) (actual
time=1689525.151..2401444.441 rows=116 loops=1)
-> Sort (cost=13877783.42..14064318.61 rows=74614073 width=37) (actual
time=1664233.243..1716472.931 rows=74614237 loops=1)
Sort Key: dw.agg_phones_monthly.month_code,
dw.agg_phones_monthly.gateway_carrier_id
Sort Method: external merge Disk: 3485424kB
-> Result (cost=0.00..2080664.40 rows=74614073 width=37) (actual
time=0.008..84421.927 rows=74614237 loops=1)
-> Append (cost=0.00..2080664.40 rows=74614073 width=37)
(actual time=0.007..64724.486 rows=74614237 loops=1)
-> Seq Scan on agg_phones_monthly (cost=0.00..11.50
rows=1 width=574) (actual time=0.000..0.000 rows=0 loops=1)
Filter: (month_code = '2010M04'::bpchar)
-> Seq Scan on agg_phones_monthly_2010m04
agg_phones_monthly (cost=0.00..2080652.90 rows=74614072 width=37) (actual
time=0.005..48199.938 rows=74614237 loops=1)
Filter: (month_code = '2010M04'::bpchar)
Total runtime: 2402137.632 ms
(11 rows)
Time: 2402139.642 ms
