explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BDnr

Settings
# exclusive inclusive rows x rows loops node
1. 105.575 2,901.721 ↑ 1.0 35,573 1

WindowAgg (cost=8,721.81..1,026,287.48 rows=35,573 width=141) (actual time=229.242..2,901.721 rows=35,573 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.plan_id, au_sim_mncs.country_id
  • Buffers: shared hit=67952, temp read=733 written=735
2. 44.654 234.890 ↑ 1.0 35,573 1

Sort (cost=8,721.81..8,810.74 rows=35,573 width=69) (actual time=228.917..234.890 rows=35,573 loops=1)

  • Output: app_usage_period_summaries.id, 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, au_sim_mncs.country_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, app_usage_period_summaries.plan_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: 3136kB
  • Buffers: shared hit=1430, temp read=733 written=735
3. 36.786 190.236 ↑ 1.0 35,573 1

WindowAgg (cost=5,143.43..6,032.76 rows=35,573 width=69) (actual time=144.920..190.236 rows=35,573 loops=1)

  • Output: app_usage_period_summaries.id, 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, au_sim_mncs.country_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, app_usage_period_summaries.plan_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=1430, temp read=341 written=342
4. 55.009 153.450 ↑ 1.0 35,573 1

Sort (cost=5,143.43..5,232.37 rows=35,573 width=61) (actual time=144.849..153.450 rows=35,573 loops=1)

  • Output: app_usage_period_summaries.id, 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, au_sim_mncs.country_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, app_usage_period_summaries.plan_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: 2728kB
  • Buffers: shared hit=1430, temp read=341 written=342
5. 48.534 98.441 ↑ 1.0 35,573 1

HashAggregate (cost=2,098.65..2,454.38 rows=35,573 width=61) (actual time=81.605..98.441 rows=35,573 loops=1)

  • Output: app_usage_period_summaries.id, 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, au_sim_mncs.country_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, app_usage_period_summaries.plan_id
  • Group Key: app_usage_period_summaries.id, au_sim_mncs.country_id
  • Buffers: shared hit=1430
6. 38.563 49.907 ↑ 1.0 35,573 1

Hash Join (cost=57.47..1,920.79 rows=35,573 width=61) (actual time=1.441..49.907 rows=35,573 loops=1)

  • Output: app_usage_period_summaries.id, au_sim_mncs.country_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.plan_id
  • Inner Unique: true
  • Hash Cond: (app_usage_period_summaries.sim_mnc_id = au_sim_mncs.id)
  • Buffers: shared hit=1430
7. 9.945 9.945 ↑ 1.0 35,573 1

Seq Scan on public.mv_app_usage_period_summaries app_usage_period_summaries (cost=0.00..1,769.73 rows=35,573 width=57) (actual time=0.026..9.945 rows=35,573 loops=1)

  • Output: app_usage_period_summaries.id, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date, app_usage_period_summaries.carrier_account_id, app_usage_period_summaries.network_name_id, app_usage_period_summaries.is_roaming, app_usage_period_summaries.device_id, app_usage_period_summaries.app_detail_parent_id, app_usage_period_summaries.app_detail_parent_name, app_usage_period_summaries.app_detail_parent_is_group, app_usage_period_summaries.app_detail_parent_label, app_usage_period_summaries.category_id, app_usage_period_summaries.app_id, app_usage_period_summaries.tariff_id, app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.category_name, app_usage_period_summaries.user_id, app_usage_period_summaries.user_name, app_usage_period_summaries.company_id, app_usage_period_summaries.device_name, app_usage_period_summaries.network_name_name, app_usage_period_summaries.tariff_country_id, app_usage_period_summaries.country_name, app_usage_period_summaries.carrier_account_phone_number, app_usage_period_summaries.carrier_account_name, app_usage_period_summaries.contract_start_date, app_usage_period_summaries.plan_id, app_usage_period_summaries.plan_name, app_usage_period_summaries.plan_rate_type, app_usage_period_summaries.plan_data_cost_in_cents_per_month, app_usage_period_summaries.plan_data_in_bytes_per_month, app_usage_period_summaries.plan_overage_data_mb_rate, app_usage_period_summaries.tariff_name, app_usage_period_summaries.tariff_roaming_data_mb_rate, app_usage_period_summaries.total_bytes, app_usage_period_summaries.network_type_code, app_usage_period_summaries.is_free, app_usage_period_summaries.roaming_details, app_usage_period_summaries.roaming_days, app_usage_period_summaries.mnc_id, app_usage_period_summaries.roaming_rule_id
  • Buffers: shared hit=1414
8. 0.703 1.399 ↑ 1.0 1,843 1

Hash (cost=34.43..34.43 rows=1,843 width=8) (actual time=1.398..1.399 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
9. 0.696 0.696 ↑ 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.014..0.696 rows=1,843 loops=1)

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

SubPlan (forWindowAgg)

11. 2,205.526 2,454.537 ↑ 1.0 1 35,573

Aggregate (cost=11.88..11.88 rows=1 width=32) (actual time=0.069..0.069 rows=1 loops=35,573)

  • 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=41858
12. 101.027 249.011 ↓ 3.3 10 35,573

Result (cost=0.28..11.86 rows=3 width=36) (actual time=0.002..0.007 rows=10 loops=35,573)

  • 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=41858
13. 147.984 147.984 ↓ 10.0 30 12,332

Index Scan using roaming_days_uniqueness_constraint on public.roaming_days (cost=0.28..11.86 rows=3 width=36) (actual time=0.004..0.012 rows=30 loops=12,332)

  • 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=41858
14. 35.573 106.719 ↑ 1.0 1 35,573

Aggregate (cost=16.69..16.70 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=35,573)

  • Output: json_agg((json_build_object('filter_date', details.filter_date, 'total_bytes', sum(details.total_bytes))))
  • Buffers: shared hit=24664
15. 0.000 71.146 ↓ 0.0 0 35,573

GroupAggregate (cost=16.64..16.67 rows=1 width=36) (actual time=0.002..0.002 rows=0 loops=35,573)

  • 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=24664
16. 35.573 71.146 ↓ 0.0 0 35,573

Sort (cost=16.64..16.65 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=35,573)

  • Output: details.filter_date, details.total_bytes
  • Sort Key: details.filter_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=24664
17. 0.000 35.573 ↓ 0.0 0 35,573

Result (cost=0.57..16.63 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=35,573)

  • Output: details.filter_date, details.total_bytes
  • One-Time Filter: app_usage_period_summaries.is_roaming
  • Buffers: shared hit=24664
18. 12.332 36.996 ↓ 0.0 0 12,332

Nested Loop (cost=0.57..16.63 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=12,332)

  • Output: details.filter_date, details.total_bytes
  • Inner Unique: true
  • Buffers: shared hit=24664
19. 24.664 24.664 ↓ 0.0 0 12,332

Index Scan using index_app_usages_on_network_type_code on public.app_usages details (cost=0.29..8.33 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=12,332)

  • Output: details.id, details.app_id, details.network_id, details.rx_delta, details.tx_delta, details."timestamp", details.updated_at, details.device_id, details.network_name_id, details.roaming, details.rx_delta_roaming, details.tx_delta_roaming, details.pid, details.uid, details.mobile_rx_bytes, details.mobile_tx_bytes, details.last_mobile_rx_bytes, details.last_mobile_tx_bytes, details.total_rx_bytes, details.total_tx_bytes, details.last_total_rx_bytes, details.last_total_tx_bytes, details.uid_rx_bytes, details.uid_tx_bytes, details.last_uid_rx_bytes, details.last_uid_tx_bytes, details.reported_by_pid, details.total_bytes, details.sim_mnc_id, details.tariff_country_id, details.tariff_id, details.app_version, details.comparison_mobile_rx_total, details.comparison_mobile_tx_total, details.comparison_wifi_rx_total, details.comparison_wifi_tx_total, details.mvi_mobile_rx_total, details.mvi_mobile_tx_total, details.mvi_wifi_rx_total, details.mvi_wifi_tx_total, details.comparison_rx_total, details.comparison_tx_total, details.filter_date, details.network_type_code, details.is_roaming, details.carrier_account_id, details.device_usage_id
  • Index Cond: (details.network_type_code = app_usage_period_summaries.network_type_code)
  • Filter: ((details.filter_date >= app_usage_period_summaries.start_date) AND (details.filter_date <= app_usage_period_summaries.end_date) AND (details.tariff_id = app_usage_period_summaries.tariff_id) AND (details.sim_mnc_id = app_usage_period_summaries.sim_mnc_id) AND (details.tariff_country_id = app_usage_period_summaries.tariff_country_id) AND (details.network_name_id = app_usage_period_summaries.network_name_id) AND (details.is_roaming = app_usage_period_summaries.is_roaming) AND (details.carrier_account_id = app_usage_period_summaries.carrier_account_id))
  • Buffers: shared hit=24664
20. 0.000 0.000 ↓ 0.0 0

Index Scan using apps_pkey on public.apps (cost=0.28..8.29 rows=1 width=4) (never executed)

  • Output: apps.id, apps.created_at, apps.updated_at, apps.user_id, apps.category_id, apps.app_detail_id, apps.system, apps.uid
  • Index Cond: (apps.id = details.app_id)
  • Filter: (apps.category_id = app_usage_period_summaries.category_id)
Planning time : 1.955 ms