explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mV1t

Settings
# exclusive inclusive rows x rows loops node
1. 237.068 12,235.333 ↑ 3.6 78,926 1

WindowAgg (cost=104,136.78..90,909,375.78 rows=280,991 width=168) (actual time=456.028..12,235.333 rows=78,926 loops=1)

  • Output: app_usage_period_summaries.id, app_usage_period_summaries.tariff_id, app_usage_period_summaries.category_id, app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.tariff_country_id, app_usage_period_summaries.network_name_id, app_usage_period_summaries.network_type_code, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date, app_usage_period_summaries.total_bytes, app_usage_period_summaries.is_roaming, app_usage_period_summaries.carrier_account_id, (sum(CASE WHEN app_usage_period_summaries.is_roaming THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?)), sum(CASE WHEN (app_usage_period_summaries.tariff_country_id = au_sim_mncs.country_id) THEN NULL::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?), (SubPlan 1), (SubPlan 2), app_usage_period_summaries.is_free, (sum(CASE WHEN app_usage_period_summaries.is_roaming THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?)), app_usage_period_summaries.app_detail_parent_label, app_usage_period_summaries.app_detail_parent_id, app_usage_period_summaries.company_id
  • Buffers: shared hit=1952345 read=10637, temp read=2303 written=2310
2. 109.251 475.069 ↑ 3.6 78,926 1

Sort (cost=104,136.78..104,839.26 rows=280,991 width=92) (actual time=455.910..475.069 rows=78,926 loops=1)

  • Output: app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date, app_usage_period_summaries.is_roaming, app_usage_period_summaries.carrier_account_id, app_usage_period_summaries.id, app_usage_period_summaries.tariff_id, app_usage_period_summaries.category_id, app_usage_period_summaries.tariff_country_id, app_usage_period_summaries.network_name_id, app_usage_period_summaries.network_type_code, app_usage_period_summaries.total_bytes, au_sim_mncs.country_id, app_usage_period_summaries.is_free, app_usage_period_summaries.app_detail_parent_label, app_usage_period_summaries.app_detail_parent_id, app_usage_period_summaries.company_id, (sum(CASE WHEN app_usage_period_summaries.is_roaming THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?))
  • Sort Key: app_usage_period_summaries.carrier_account_id, app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.end_date
  • Sort Method: external merge Disk: 9600kB
  • Buffers: shared hit=4168 read=10637, temp read=2303 written=2310
3. 61.607 365.818 ↑ 3.6 78,926 1

WindowAgg (cost=57,272.59..64,297.36 rows=280,991 width=92) (actual time=281.789..365.818 rows=78,926 loops=1)

  • Output: app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date, app_usage_period_summaries.is_roaming, app_usage_period_summaries.carrier_account_id, app_usage_period_summaries.id, app_usage_period_summaries.tariff_id, app_usage_period_summaries.category_id, app_usage_period_summaries.tariff_country_id, app_usage_period_summaries.network_name_id, app_usage_period_summaries.network_type_code, app_usage_period_summaries.total_bytes, au_sim_mncs.country_id, app_usage_period_summaries.is_free, app_usage_period_summaries.app_detail_parent_label, app_usage_period_summaries.app_detail_parent_id, app_usage_period_summaries.company_id, sum(CASE WHEN app_usage_period_summaries.is_roaming THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?)
  • Buffers: shared hit=4168 read=10637, temp read=1103 written=1106
4. 119.008 304.211 ↑ 3.6 78,926 1

Sort (cost=57,272.59..57,975.06 rows=280,991 width=84) (actual time=281.714..304.211 rows=78,926 loops=1)

  • Output: app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date, app_usage_period_summaries.is_roaming, app_usage_period_summaries.carrier_account_id, app_usage_period_summaries.id, app_usage_period_summaries.tariff_id, app_usage_period_summaries.category_id, app_usage_period_summaries.tariff_country_id, app_usage_period_summaries.network_name_id, app_usage_period_summaries.network_type_code, app_usage_period_summaries.total_bytes, au_sim_mncs.country_id, app_usage_period_summaries.is_free, app_usage_period_summaries.app_detail_parent_label, app_usage_period_summaries.app_detail_parent_id, app_usage_period_summaries.company_id
  • Sort Key: app_usage_period_summaries.carrier_account_id, app_usage_period_summaries.is_roaming, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date
  • Sort Method: external merge Disk: 8824kB
  • Buffers: shared hit=4168 read=10637, temp read=1103 written=1106
5. 29.167 185.203 ↑ 3.6 78,926 1

Hash Join (cost=57.47..18,395.67 rows=280,991 width=84) (actual time=3.528..185.203 rows=78,926 loops=1)

  • Output: app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date, app_usage_period_summaries.is_roaming, app_usage_period_summaries.carrier_account_id, app_usage_period_summaries.id, app_usage_period_summaries.tariff_id, app_usage_period_summaries.category_id, app_usage_period_summaries.tariff_country_id, app_usage_period_summaries.network_name_id, app_usage_period_summaries.network_type_code, app_usage_period_summaries.total_bytes, au_sim_mncs.country_id, app_usage_period_summaries.is_free, app_usage_period_summaries.app_detail_parent_label, app_usage_period_summaries.app_detail_parent_id, app_usage_period_summaries.company_id
  • Inner Unique: true
  • Hash Cond: (app_usage_period_summaries.sim_mnc_id = au_sim_mncs.id)
  • Buffers: shared hit=4168 read=10637
6. 154.807 154.807 ↑ 3.6 78,926 1

Seq Scan on public.mv_app_usage_period_summaries app_usage_period_summaries (cost=0.00..17,598.91 rows=280,991 width=80) (actual time=2.249..154.807 rows=78,926 loops=1)

  • Output: app_usage_period_summaries.id, app_usage_period_summaries.tariff_id, app_usage_period_summaries.category_id, app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.tariff_country_id, app_usage_period_summaries.network_name_id, app_usage_period_summaries.network_type_code, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date, app_usage_period_summaries.total_bytes, app_usage_period_summaries.is_roaming, app_usage_period_summaries.carrier_account_id, app_usage_period_summaries.is_free, app_usage_period_summaries.app_detail_parent_label, app_usage_period_summaries.app_detail_parent_id, app_usage_period_summaries.company_id
  • Buffers: shared hit=4152 read=10637
7. 0.646 1.229 ↑ 1.0 1,843 1

Hash (cost=34.43..34.43 rows=1,843 width=8) (actual time=1.229..1.229 rows=1,843 loops=1)

  • Output: au_sim_mncs.country_id, au_sim_mncs.id
  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
  • Buffers: shared hit=16
8. 0.583 0.583 ↑ 1.0 1,843 1

Seq Scan on public.mncs au_sim_mncs (cost=0.00..34.43 rows=1,843 width=8) (actual time=0.021..0.583 rows=1,843 loops=1)

  • Output: au_sim_mncs.country_id, au_sim_mncs.id
  • Buffers: shared hit=16
9.          

SubPlan (forWindowAgg)

10. 5,130.190 5,919.450 ↑ 1.0 1 78,926

Aggregate (cost=8.32..8.33 rows=1 width=32) (actual time=0.075..0.075 rows=1 loops=78,926)

  • Output: json_agg(json_build_object('filter_date', roaming_days.filter_date, 'phone_calls_duration_in_seconds_incoming', roaming_days.phone_calls_duration_in_seconds_incoming, 'phone_calls_duration_in_seconds_outgoing', roaming_days.phone_calls_duration_in_seconds_outgoing, 'phone_calls_duration_in_minutes_incoming', roaming_days.phone_calls_duration_in_minutes_incoming, 'phone_calls_duration_in_minutes_outgoing', roaming_days.phone_calls_duration_in_minutes_outgoing, 'text_messages_size_incoming', roaming_days.text_messages_size_incoming, 'text_messages_size_outgoing', roaming_days.text_messages_size_outgoing, 'app_usages_total_bytes', roaming_days.app_usages_total_bytes))
  • Buffers: shared hit=573569
11. 255.626 789.260 ↓ 11.0 11 78,926

Result (cost=0.29..8.31 rows=1 width=36) (actual time=0.002..0.010 rows=11 loops=78,926)

  • Output: roaming_days.filter_date, roaming_days.phone_calls_duration_in_seconds_incoming, roaming_days.phone_calls_duration_in_seconds_outgoing, roaming_days.phone_calls_duration_in_minutes_incoming, roaming_days.phone_calls_duration_in_minutes_outgoing, roaming_days.text_messages_size_incoming, roaming_days.text_messages_size_outgoing, roaming_days.app_usages_total_bytes
  • One-Time Filter: app_usage_period_summaries.is_roaming
  • Buffers: shared hit=573569
12. 533.634 533.634 ↓ 30.0 30 28,086

Index Scan using roaming_days_uniqueness_constraint on public.roaming_days (cost=0.29..8.31 rows=1 width=36) (actual time=0.005..0.019 rows=30 loops=28,086)

  • Output: roaming_days.id, roaming_days.carrier_account_id, roaming_days.country_id, roaming_days.filter_date, roaming_days.phone_calls_duration_in_seconds_incoming, roaming_days.phone_calls_duration_in_seconds_outgoing, roaming_days.phone_calls_duration_in_minutes_incoming, roaming_days.phone_calls_duration_in_minutes_outgoing, roaming_days.text_messages_size_incoming, roaming_days.text_messages_size_outgoing, roaming_days.app_usages_total_bytes
  • Index Cond: ((roaming_days.carrier_account_id = app_usage_period_summaries.carrier_account_id) AND (roaming_days.country_id = app_usage_period_summaries.tariff_country_id) AND (roaming_days.filter_date >= app_usage_period_summaries.start_date) AND (roaming_days.filter_date <= app_usage_period_summaries.end_date))
  • Buffers: shared hit=573569
13. 236.778 5,603.746 ↑ 1.0 1 78,926

Aggregate (cost=314.80..314.81 rows=1 width=32) (actual time=0.071..0.071 rows=1 loops=78,926)

  • Output: json_agg((json_build_object('filter_date', details.filter_date, 'total_bytes', sum(details.total_bytes))))
  • Buffers: shared hit=1374608
14. 1,420.668 5,366.968 ↓ 7.0 7 78,926

GroupAggregate (cost=314.76..314.78 rows=1 width=40) (actual time=0.051..0.068 rows=7 loops=78,926)

  • Output: json_build_object('filter_date', details.filter_date, 'total_bytes', sum(details.total_bytes)), details.filter_date
  • Group Key: details.filter_date
  • Buffers: shared hit=1374608
15. 394.630 3,946.300 ↓ 12.0 12 78,926

Sort (cost=314.76..314.77 rows=1 width=16) (actual time=0.049..0.050 rows=12 loops=78,926)

  • Output: details.filter_date, details.total_bytes
  • Sort Key: details.filter_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1374608
16. 209.436 3,551.670 ↓ 12.0 12 78,926

Result (cost=0.42..314.75 rows=1 width=16) (actual time=0.014..0.045 rows=12 loops=78,926)

  • Output: details.filter_date, details.total_bytes
  • One-Time Filter: app_usage_period_summaries.is_roaming
  • Buffers: shared hit=1374608
17. 3,342.234 3,342.234 ↓ 33.0 33 28,086

Index Scan using mv_app_day_partition_idx on public.mv_app_usage_day_summaries details (cost=0.42..314.75 rows=1 width=16) (actual time=0.038..0.119 rows=33 loops=28,086)

  • Output: details.id, details.filter_date, details.carrier_account_id, details.network_name_id, details.is_roaming, details.device_id, details.app_detail_parent_id, details.app_detail_parent_name, details.app_detail_parent_is_group, details.app_detail_parent_label, details.category_id, details.app_id, details.tariff_id, details.sim_mnc_id, details.category_name, details.user_id, details.user_name, details.company_id, details.device_name, details.network_name_name, details.tariff_country_id, details.country_name, details.carrier_account_phone_number, details.carrier_account_name, details.contract_start_date, details.start_date, details.end_date, details.plan_id, details.plan_name, details.plan_rate_type, details.plan_data_cost_in_cents_per_month, details.plan_data_in_bytes_per_month, details.plan_overage_data_mb_rate, details.tariff_name, details.tariff_roaming_data_mb_rate, details.total_bytes, details.network_type_code, details.is_free, details.mnc_id, details.roaming_rule_id, details.roaming_days, details.roaming_details
  • Index Cond: ((details.carrier_account_id = app_usage_period_summaries.carrier_account_id) AND (details.is_roaming = app_usage_period_summaries.is_roaming) AND (details.end_date = app_usage_period_summaries.end_date) AND (details.tariff_country_id = app_usage_period_summaries.tariff_country_id))
  • Filter: ((details.tariff_id = app_usage_period_summaries.tariff_id) AND (details.category_id = app_usage_period_summaries.category_id) AND (details.sim_mnc_id = app_usage_period_summaries.sim_mnc_id) AND (details.network_name_id = app_usage_period_summaries.network_name_id) AND (details.network_type_code = app_usage_period_summaries.network_type_code))
  • Rows Removed by Filter: 56
  • Buffers: shared hit=1374608
Planning time : 0.920 ms