explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dtnh

Settings
# exclusive inclusive rows x rows loops node
1. 0.161 248.064 ↑ 6.5 38 1

WindowAgg (cost=584.76..69,133.43 rows=246 width=137) (actual time=207.243..248.064 rows=38 loops=1)

  • Output: app_usage_period_summaries.id, app_usage_period_summaries.company_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)
  • Buffers: shared hit=473
2. 3.055 207.091 ↑ 6.5 38 1

Sort (cost=584.76..585.37 rows=246 width=69) (actual time=207.085..207.091 rows=38 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.company_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, (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.sim_mnc_id, app_usage_period_summaries.end_date
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=289
3. 3.361 204.036 ↑ 6.5 38 1

WindowAgg (cost=568.84..574.99 rows=246 width=69) (actual time=203.926..204.036 rows=38 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.company_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, 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=289
4. 0.204 200.675 ↑ 6.5 38 1

Sort (cost=568.84..569.46 rows=246 width=61) (actual time=200.667..200.675 rows=38 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.company_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
  • Sort Key: app_usage_period_summaries.is_roaming, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=289
5. 0.165 200.471 ↑ 6.5 38 1

Merge Join (cost=548.76..559.07 rows=246 width=61) (actual time=200.426..200.471 rows=38 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.company_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
  • Merge Cond: (au_sim_mncs.id = app_usage_period_summaries.sim_mnc_id)
  • Buffers: shared hit=289
6. 7.785 7.785 ↑ 7.7 240 1

Index Scan using mncs_pkey on public.mncs au_sim_mncs (cost=0.28..74.92 rows=1,843 width=8) (actual time=7.582..7.785 rows=240 loops=1)

  • Output: au_sim_mncs.id, au_sim_mncs.country_id, au_sim_mncs.carrier_id, au_sim_mncs.mcc, au_sim_mncs.mnc, au_sim_mncs.created_at, au_sim_mncs.updated_at
  • Buffers: shared hit=4
7. 0.119 192.521 ↑ 6.5 38 1

Sort (cost=544.48..545.10 rows=246 width=57) (actual time=192.512..192.521 rows=38 loops=1)

  • Output: app_usage_period_summaries.id, app_usage_period_summaries.company_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
  • Sort Key: app_usage_period_summaries.sim_mnc_id
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=285
8. 192.402 192.402 ↑ 6.5 38 1

Index Scan using index_mv_app_usage_period_summaries_on_end_date on public.mv_app_usage_period_summaries app_usage_period_summaries (cost=0.42..534.71 rows=246 width=57) (actual time=19.150..192.402 rows=38 loops=1)

  • Output: app_usage_period_summaries.id, app_usage_period_summaries.company_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
  • Index Cond: ((app_usage_period_summaries.end_date >= '2019-05-01'::date) AND (app_usage_period_summaries.end_date <= '2019-05-31'::date))
  • Filter: (app_usage_period_summaries.carrier_account_id = 5)
  • Rows Removed by Filter: 1823
  • Buffers: shared hit=285
9.          

SubPlan (forWindowAgg)

10. 0.304 11.514 ↑ 1.0 1 38

Aggregate (cost=8.32..8.33 rows=1 width=32) (actual time=0.303..0.303 rows=1 loops=38)

  • 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=24
11. 0.034 11.210 ↑ 1.0 1 38

Result (cost=0.29..8.31 rows=1 width=36) (actual time=0.294..0.295 rows=1 loops=38)

  • 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=24
12. 11.176 11.176 ↓ 3.0 3 8

Index Scan using roaming_days_uniqueness_constraint on public.roaming_days (cost=0.29..8.31 rows=1 width=36) (actual time=1.396..1.397 rows=3 loops=8)

  • 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=24
13. 0.076 29.298 ↑ 1.0 1 38

Aggregate (cost=270.29..270.30 rows=1 width=32) (actual time=0.770..0.771 rows=1 loops=38)

  • Output: json_agg((json_build_object('filter_date', details.filter_date, 'total_bytes', sum(details.total_bytes))))
  • Buffers: shared hit=160
14. 0.684 29.222 ↑ 1.0 1 38

GroupAggregate (cost=270.25..270.28 rows=1 width=40) (actual time=0.768..0.769 rows=1 loops=38)

  • 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=160
15. 0.190 28.538 ↑ 1.0 1 38

Sort (cost=270.25..270.26 rows=1 width=16) (actual time=0.751..0.751 rows=1 loops=38)

  • Output: details.filter_date, details.total_bytes
  • Sort Key: details.filter_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=160
16. 0.020 28.348 ↑ 1.0 1 38

Result (cost=0.42..270.24 rows=1 width=16) (actual time=0.647..0.746 rows=1 loops=38)

  • Output: details.filter_date, details.total_bytes
  • One-Time Filter: app_usage_period_summaries.is_roaming
  • Buffers: shared hit=160
17. 28.328 28.328 ↓ 4.0 4 8

Index Scan using mv_app_day_partition_idx on public.mv_app_usage_day_summaries details (cost=0.42..270.24 rows=1 width=16) (actual time=3.071..3.541 rows=4 loops=8)

  • 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: 4
  • Buffers: shared hit=160
Planning time : 34.886 ms