explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kaZ3

Settings
# exclusive inclusive rows x rows loops node
1. 0.291 3.588 ↑ 6.5 38 1

WindowAgg (cost=584.76..69,133.43 rows=246 width=137) (actual time=1.566..3.588 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. 0.037 1.511 ↑ 6.5 38 1

Sort (cost=584.76..585.37 rows=246 width=69) (actual time=1.505..1.511 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. 0.069 1.474 ↑ 6.5 38 1

WindowAgg (cost=568.84..574.99 rows=246 width=69) (actual time=1.440..1.474 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.065 1.405 ↑ 6.5 38 1

Sort (cost=568.84..569.46 rows=246 width=61) (actual time=1.398..1.405 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.086 1.340 ↑ 6.5 38 1

Merge Join (cost=548.76..559.07 rows=246 width=61) (actual time=1.307..1.340 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. 0.108 0.108 ↑ 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=0.023..0.108 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.036 1.146 ↑ 6.5 38 1

Sort (cost=544.48..545.10 rows=246 width=57) (actual time=1.139..1.146 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. 1.110 1.110 ↑ 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=0.055..1.110 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.342 0.418 ↑ 1.0 1 38

Aggregate (cost=8.32..8.33 rows=1 width=32) (actual time=0.011..0.011 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.012 0.076 ↑ 1.0 1 38

Result (cost=0.29..8.31 rows=1 width=36) (actual time=0.002..0.002 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. 0.064 0.064 ↓ 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=0.006..0.008 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 1.368 ↑ 1.0 1 38

Aggregate (cost=270.29..270.30 rows=1 width=32) (actual time=0.035..0.036 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.152 1.292 ↑ 1.0 1 38

GroupAggregate (cost=270.25..270.28 rows=1 width=40) (actual time=0.032..0.034 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.076 1.140 ↑ 1.0 1 38

Sort (cost=270.25..270.26 rows=1 width=16) (actual time=0.029..0.030 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.040 1.064 ↑ 1.0 1 38

Result (cost=0.42..270.24 rows=1 width=16) (actual time=0.026..0.028 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. 1.024 1.024 ↓ 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=0.120..0.128 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 : 48.540 ms