explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MWDi

Settings
# exclusive inclusive rows x rows loops node
1. 22.317 1,513.002 ↓ 118.5 73,112 1

Subquery Scan on app_usage_day_summaries_with_total_roaming_country_usage (cost=4,743.73..4,773.04 rows=617 width=172) (actual time=761.557..1,513.002 rows=73,112 loops=1)

  • Output: app_usage_day_summaries_with_total_roaming_country_usage.id, app_usage_day_summaries_with_total_roaming_country_usage.tariff_id, app_usage_day_summaries_with_total_roaming_country_usage.app_id, app_usage_day_summaries_with_total_roaming_country_usage.sim_mnc_id, app_usage_day_summaries_with_total_roaming_country_usage.tariff_country_id, app_usage_day_summaries_with_total_roaming_country_usage.network_name_id, app_usage_day_summaries_with_total_roaming_country_usage.network_type_code, app_usage_day_summaries_with_total_roaming_country_usage.filter_date, app_usage_day_summaries_with_total_roaming_country_usage.total_bytes, app_usage_day_summaries_with_total_roaming_country_usage.is_roaming, app_usage_day_summaries_with_total_roaming_country_usage.end_date, app_usage_day_summaries_with_total_roaming_country_usage.carrier_account_id, app_usage_day_summaries_with_total_roaming_country_usage.total_billing_usage, app_usage_day_summaries_with_total_roaming_country_usage.total_roaming_country_usage, app_usage_day_summaries_with_total_roaming_country_usage.roaming_days, app_usage_day_summaries_with_total_roaming_country_usage.roaming_details, app_usage_day_summaries_with_total_roaming_country_usage.is_free, app_usage_day_summaries_with_total_roaming_country_usage.total_really_usaged, app_usage_day_summaries_with_total_roaming_country_usage.category_id, app_usage_day_summaries_with_total_roaming_country_usage.label, app_usage_day_summaries_with_total_roaming_country_usage.app_detail_id
  • Buffers: shared hit=442297, temp read=2783 written=2793
2. 709.301 1,490.685 ↓ 118.5 73,112 1

WindowAgg (cost=4,743.73..4,766.87 rows=617 width=176) (actual time=761.555..1,490.685 rows=73,112 loops=1)

  • Output: app_usage_day_summaries.id, app_usage_day_summaries.tariff_id, app_usage_day_summaries.app_id, app_usage_day_summaries.sim_mnc_id, app_usage_day_summaries.tariff_country_id, app_usage_day_summaries.network_name_id, app_usage_day_summaries.network_type_code, app_usage_day_summaries.filter_date, app_usage_day_summaries.total_bytes, app_usage_day_summaries.is_roaming, app_usage_day_summaries.end_date, app_usage_day_summaries.carrier_account_id, (sum(CASE WHEN (app_usage_day_summaries.is_roaming OR app_usage_day_summaries.is_free) THEN '0'::double precision ELSE app_usage_day_summaries.total_bytes END) OVER (?)), sum(CASE WHEN app_usage_day_summaries.is_roaming THEN app_usage_day_summaries.total_bytes ELSE '0'::double precision END) OVER (?), json_build_array(json_build_object('filter_date', app_usage_day_summaries.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)), json_build_array(json_build_object('filter_date', app_usage_day_summaries.filter_date, 'total_bytes', app_usage_day_summaries.total_bytes)), app_usage_day_summaries.is_free, (sum(CASE WHEN (app_usage_day_summaries.is_roaming OR app_usage_day_summaries.is_free) THEN '0'::double precision ELSE app_usage_day_summaries.total_bytes END) OVER (?)), app_usage_day_summaries.category_id, app_usage_day_summaries.app_detail_parent_label, app_usage_day_summaries.app_detail_parent_id, app_usage_day_summaries.start_date
  • Buffers: shared hit=442297, temp read=2783 written=2793
3. 129.231 781.384 ↓ 118.5 73,112 1

Sort (cost=4,743.73..4,745.28 rows=617 width=128) (actual time=761.295..781.384 rows=73,112 loops=1)

  • Output: app_usage_day_summaries.tariff_country_id, app_usage_day_summaries.is_roaming, app_usage_day_summaries.end_date, app_usage_day_summaries.carrier_account_id, app_usage_day_summaries.start_date, app_usage_day_summaries.id, app_usage_day_summaries.tariff_id, app_usage_day_summaries.app_id, app_usage_day_summaries.sim_mnc_id, app_usage_day_summaries.network_name_id, app_usage_day_summaries.network_type_code, app_usage_day_summaries.filter_date, app_usage_day_summaries.total_bytes, app_usage_day_summaries.is_free, 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, app_usage_day_summaries.category_id, app_usage_day_summaries.app_detail_parent_label, app_usage_day_summaries.app_detail_parent_id, (sum(CASE WHEN (app_usage_day_summaries.is_roaming OR app_usage_day_summaries.is_free) THEN '0'::double precision ELSE app_usage_day_summaries.total_bytes END) OVER (?))
  • Sort Key: app_usage_day_summaries.carrier_account_id, app_usage_day_summaries.is_roaming, app_usage_day_summaries.tariff_country_id, app_usage_day_summaries.start_date, app_usage_day_summaries.end_date
  • Sort Method: external merge Disk: 11496kB
  • Buffers: shared hit=442297, temp read=2783 written=2793
4. 72.134 652.153 ↓ 118.5 73,112 1

WindowAgg (cost=4,699.71..4,715.14 rows=617 width=128) (actual time=549.328..652.153 rows=73,112 loops=1)

  • Output: app_usage_day_summaries.tariff_country_id, app_usage_day_summaries.is_roaming, app_usage_day_summaries.end_date, app_usage_day_summaries.carrier_account_id, app_usage_day_summaries.start_date, app_usage_day_summaries.id, app_usage_day_summaries.tariff_id, app_usage_day_summaries.app_id, app_usage_day_summaries.sim_mnc_id, app_usage_day_summaries.network_name_id, app_usage_day_summaries.network_type_code, app_usage_day_summaries.filter_date, app_usage_day_summaries.total_bytes, app_usage_day_summaries.is_free, 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, app_usage_day_summaries.category_id, app_usage_day_summaries.app_detail_parent_label, app_usage_day_summaries.app_detail_parent_id, sum(CASE WHEN (app_usage_day_summaries.is_roaming OR app_usage_day_summaries.is_free) THEN '0'::double precision ELSE app_usage_day_summaries.total_bytes END) OVER (?)
  • Buffers: shared hit=442297, temp read=1346 written=1351
5. 143.551 580.019 ↓ 118.5 73,112 1

Sort (cost=4,699.71..4,701.26 rows=617 width=120) (actual time=549.124..580.019 rows=73,112 loops=1)

  • Output: app_usage_day_summaries.tariff_country_id, app_usage_day_summaries.is_roaming, app_usage_day_summaries.end_date, app_usage_day_summaries.carrier_account_id, app_usage_day_summaries.start_date, app_usage_day_summaries.id, app_usage_day_summaries.tariff_id, app_usage_day_summaries.app_id, app_usage_day_summaries.sim_mnc_id, app_usage_day_summaries.network_name_id, app_usage_day_summaries.network_type_code, app_usage_day_summaries.filter_date, app_usage_day_summaries.total_bytes, app_usage_day_summaries.is_free, 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, app_usage_day_summaries.category_id, app_usage_day_summaries.app_detail_parent_label, app_usage_day_summaries.app_detail_parent_id
  • Sort Key: app_usage_day_summaries.carrier_account_id, app_usage_day_summaries.is_roaming, app_usage_day_summaries.start_date, app_usage_day_summaries.end_date
  • Sort Method: external merge Disk: 10768kB
  • Buffers: shared hit=442297, temp read=1346 written=1351
6. 31.818 436.468 ↓ 118.5 73,112 1

Nested Loop Left Join (cost=120.97..4,671.12 rows=617 width=120) (actual time=3.660..436.468 rows=73,112 loops=1)

  • Output: app_usage_day_summaries.tariff_country_id, app_usage_day_summaries.is_roaming, app_usage_day_summaries.end_date, app_usage_day_summaries.carrier_account_id, app_usage_day_summaries.start_date, app_usage_day_summaries.id, app_usage_day_summaries.tariff_id, app_usage_day_summaries.app_id, app_usage_day_summaries.sim_mnc_id, app_usage_day_summaries.network_name_id, app_usage_day_summaries.network_type_code, app_usage_day_summaries.filter_date, app_usage_day_summaries.total_bytes, app_usage_day_summaries.is_free, 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, app_usage_day_summaries.category_id, app_usage_day_summaries.app_detail_parent_label, app_usage_day_summaries.app_detail_parent_id
  • Inner Unique: true
  • Join Filter: (roaming_days.country_id = roaming_tariffs.country_id)
  • Buffers: shared hit=442297
7. 29.726 258.426 ↓ 118.5 73,112 1

Hash Join (cost=120.69..4,473.45 rows=617 width=92) (actual time=3.600..258.426 rows=73,112 loops=1)

  • Output: app_usage_day_summaries.id, app_usage_day_summaries.tariff_id, app_usage_day_summaries.app_id, app_usage_day_summaries.sim_mnc_id, app_usage_day_summaries.tariff_country_id, app_usage_day_summaries.network_name_id, app_usage_day_summaries.network_type_code, app_usage_day_summaries.filter_date, app_usage_day_summaries.total_bytes, app_usage_day_summaries.is_roaming, app_usage_day_summaries.end_date, app_usage_day_summaries.carrier_account_id, app_usage_day_summaries.is_free, app_usage_day_summaries.category_id, app_usage_day_summaries.app_detail_parent_label, app_usage_day_summaries.app_detail_parent_id, app_usage_day_summaries.start_date, roaming_tariffs.country_id
  • Inner Unique: true
  • Hash Cond: (carrier_accounts.plan_id = plans.id)
  • Buffers: shared hit=222364
8. 75.645 228.685 ↓ 329.3 73,112 1

Nested Loop (cost=107.99..4,460.16 rows=222 width=104) (actual time=3.541..228.685 rows=73,112 loops=1)

  • Output: app_usage_day_summaries.id, app_usage_day_summaries.tariff_id, app_usage_day_summaries.app_id, app_usage_day_summaries.sim_mnc_id, app_usage_day_summaries.tariff_country_id, app_usage_day_summaries.network_name_id, app_usage_day_summaries.network_type_code, app_usage_day_summaries.filter_date, app_usage_day_summaries.total_bytes, app_usage_day_summaries.is_roaming, app_usage_day_summaries.end_date, app_usage_day_summaries.carrier_account_id, app_usage_day_summaries.is_free, app_usage_day_summaries.category_id, app_usage_day_summaries.app_detail_parent_label, app_usage_day_summaries.app_detail_parent_id, app_usage_day_summaries.start_date, carrier_accounts.plan_id, tariffs.plan_id, roaming_tariffs.plan_id, roaming_tariffs.country_id
  • Inner Unique: true
  • Buffers: shared hit=222363
9. 60.637 79.928 ↓ 329.3 73,112 1

Hash Join (cost=107.71..4,394.39 rows=222 width=104) (actual time=3.510..79.928 rows=73,112 loops=1)

  • Output: app_usage_day_summaries.id, app_usage_day_summaries.tariff_id, app_usage_day_summaries.app_id, app_usage_day_summaries.sim_mnc_id, app_usage_day_summaries.tariff_country_id, app_usage_day_summaries.network_name_id, app_usage_day_summaries.network_type_code, app_usage_day_summaries.filter_date, app_usage_day_summaries.total_bytes, app_usage_day_summaries.is_roaming, app_usage_day_summaries.end_date, app_usage_day_summaries.carrier_account_id, app_usage_day_summaries.is_free, app_usage_day_summaries.category_id, app_usage_day_summaries.app_detail_parent_label, app_usage_day_summaries.app_detail_parent_id, app_usage_day_summaries.start_date, carrier_accounts.plan_id, tariffs.plan_id, roaming_tariffs.plan_id, roaming_tariffs.country_id
  • Hash Cond: ((app_usage_day_summaries.network_name_id = au_network_names.id) AND (app_usage_day_summaries.carrier_account_id = carrier_accounts.id))
  • Buffers: shared hit=3027
10. 15.818 15.818 ↑ 1.0 73,112 1

Seq Scan on public.mv_app_usage_day_summaries app_usage_day_summaries (cost=0.00..3,736.12 rows=73,112 width=88) (actual time=0.006..15.818 rows=73,112 loops=1)

  • Output: app_usage_day_summaries.id, app_usage_day_summaries.filter_date, app_usage_day_summaries.carrier_account_id, app_usage_day_summaries.network_name_id, app_usage_day_summaries.is_roaming, app_usage_day_summaries.device_id, app_usage_day_summaries.app_detail_parent_id, app_usage_day_summaries.app_detail_parent_name, app_usage_day_summaries.app_detail_parent_is_group, app_usage_day_summaries.app_detail_parent_label, app_usage_day_summaries.category_id, app_usage_day_summaries.app_id, app_usage_day_summaries.tariff_id, app_usage_day_summaries.sim_mnc_id, app_usage_day_summaries.category_name, app_usage_day_summaries.user_id, app_usage_day_summaries.user_name, app_usage_day_summaries.company_id, app_usage_day_summaries.device_name, app_usage_day_summaries.network_name_name, app_usage_day_summaries.tariff_country_id, app_usage_day_summaries.country_name, app_usage_day_summaries.carrier_account_phone_number, app_usage_day_summaries.carrier_account_name, app_usage_day_summaries.contract_start_date, app_usage_day_summaries.start_date, app_usage_day_summaries.end_date, app_usage_day_summaries.plan_id, app_usage_day_summaries.plan_name, app_usage_day_summaries.plan_rate_type, app_usage_day_summaries.plan_data_cost_in_cents_per_month, app_usage_day_summaries.plan_data_in_bytes_per_month, app_usage_day_summaries.plan_overage_data_mb_rate, app_usage_day_summaries.tariff_name, app_usage_day_summaries.tariff_roaming_data_mb_rate, app_usage_day_summaries.total_bytes, app_usage_day_summaries.network_type_code, app_usage_day_summaries.is_free, app_usage_day_summaries.mnc_id, app_usage_day_summaries.roaming_rule_id
  • Buffers: shared hit=3005
11. 0.029 3.473 ↑ 1.2 100 1

Hash (cost=105.93..105.93 rows=119 width=24) (actual time=3.473..3.473 rows=100 loops=1)

  • Output: au_network_names.id, tariffs.plan_id, carrier_accounts.id, carrier_accounts.plan_id, roaming_tariffs.plan_id, roaming_tariffs.country_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=22
12. 2.595 3.444 ↑ 1.2 100 1

Hash Join (cost=91.24..105.93 rows=119 width=24) (actual time=1.341..3.444 rows=100 loops=1)

  • Output: au_network_names.id, tariffs.plan_id, carrier_accounts.id, carrier_accounts.plan_id, roaming_tariffs.plan_id, roaming_tariffs.country_id
  • Hash Cond: (roaming_tariffs.plan_id = carrier_accounts.plan_id)
  • Join Filter: CASE WHEN (tariffs.roaming_rule_id IS NULL) THEN (roaming_tariffs.id = tariffs.id) ELSE (roaming_tariffs.roaming_rule_id = tariffs.roaming_rule_id) END
  • Rows Removed by Join Filter: 24200
  • Buffers: shared hit=22
13. 0.064 0.064 ↑ 1.0 243 1

Seq Scan on public.tariffs roaming_tariffs (cost=0.00..4.43 rows=243 width=16) (actual time=0.008..0.064 rows=243 loops=1)

  • Output: roaming_tariffs.id, roaming_tariffs.name, roaming_tariffs.voice_rate, roaming_tariffs.created_at, roaming_tariffs.updated_at, roaming_tariffs.bill_per_minute, roaming_tariffs.plan_id, roaming_tariffs.country_id, roaming_tariffs.text_message_rate, roaming_tariffs.roaming_voice_rate, roaming_tariffs.roaming_data_mb_rate, roaming_tariffs.roaming_text_message_rate, roaming_tariffs.roaming_rule_id
  • Buffers: shared hit=2
14. 0.024 0.785 ↑ 2.0 100 1

Hash (cost=88.79..88.79 rows=196 width=24) (actual time=0.785..0.785 rows=100 loops=1)

  • Output: au_network_names.id, tariffs.plan_id, tariffs.roaming_rule_id, tariffs.id, carrier_accounts.id, carrier_accounts.plan_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=20
15. 0.064 0.761 ↑ 2.0 100 1

Hash Join (cost=36.92..88.79 rows=196 width=24) (actual time=0.304..0.761 rows=100 loops=1)

  • Output: au_network_names.id, tariffs.plan_id, tariffs.roaming_rule_id, tariffs.id, carrier_accounts.id, carrier_accounts.plan_id
  • Hash Cond: (tariffs.plan_id = carrier_accounts.plan_id)
  • Buffers: shared hit=20
16. 0.040 0.685 ↑ 14.0 10 1

Hash Join (cost=20.62..65.28 rows=140 width=16) (actual time=0.246..0.685 rows=10 loops=1)

  • Output: au_network_names.id, tariffs.plan_id, tariffs.roaming_rule_id, tariffs.id
  • Hash Cond: (au_mncs.country_id = tariffs.country_id)
  • Buffers: shared hit=19
17. 0.300 0.539 ↑ 14.0 10 1

Hash Join (cost=13.15..55.89 rows=140 width=8) (actual time=0.102..0.539 rows=10 loops=1)

  • Output: au_network_names.id, au_mncs.country_id
  • Hash Cond: (au_mncs.id = au_network_names.mnc_id)
  • Buffers: shared hit=17
18. 0.228 0.228 ↑ 1.0 1,843 1

Seq Scan on public.mncs au_mncs (cost=0.00..34.43 rows=1,843 width=8) (actual time=0.008..0.228 rows=1,843 loops=1)

  • Output: au_mncs.id, au_mncs.country_id, au_mncs.carrier_id, au_mncs.mcc, au_mncs.mnc, au_mncs.created_at, au_mncs.updated_at
  • Buffers: shared hit=16
19. 0.003 0.011 ↑ 14.0 10 1

Hash (cost=11.40..11.40 rows=140 width=8) (actual time=0.010..0.011 rows=10 loops=1)

  • Output: au_network_names.id, au_network_names.mnc_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
20. 0.008 0.008 ↑ 14.0 10 1

Seq Scan on public.network_names au_network_names (cost=0.00..11.40 rows=140 width=8) (actual time=0.006..0.008 rows=10 loops=1)

  • Output: au_network_names.id, au_network_names.mnc_id
  • Buffers: shared hit=1
21. 0.051 0.106 ↑ 1.0 243 1

Hash (cost=4.43..4.43 rows=243 width=16) (actual time=0.106..0.106 rows=243 loops=1)

  • Output: tariffs.plan_id, tariffs.country_id, tariffs.roaming_rule_id, tariffs.id
  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=2
22. 0.055 0.055 ↑ 1.0 243 1

Seq Scan on public.tariffs (cost=0.00..4.43 rows=243 width=16) (actual time=0.008..0.055 rows=243 loops=1)

  • Output: tariffs.plan_id, tariffs.country_id, tariffs.roaming_rule_id, tariffs.id
  • Buffers: shared hit=2
23. 0.003 0.012 ↑ 28.0 10 1

Hash (cost=12.80..12.80 rows=280 width=8) (actual time=0.012..0.012 rows=10 loops=1)

  • Output: carrier_accounts.id, carrier_accounts.plan_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
24. 0.009 0.009 ↑ 28.0 10 1

Seq Scan on public.carrier_accounts (cost=0.00..12.80 rows=280 width=8) (actual time=0.007..0.009 rows=10 loops=1)

  • Output: carrier_accounts.id, carrier_accounts.plan_id
  • Buffers: shared hit=1
25. 73.112 73.112 ↑ 1.0 1 73,112

Index Only Scan using mncs_pkey on public.mncs au_sim_mncs (cost=0.28..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=73,112)

  • Output: au_sim_mncs.id
  • Index Cond: (au_sim_mncs.id = app_usage_day_summaries.sim_mnc_id)
  • Heap Fetches: 73112
  • Buffers: shared hit=219336
26. 0.002 0.015 ↑ 120.0 1 1

Hash (cost=11.20..11.20 rows=120 width=4) (actual time=0.015..0.015 rows=1 loops=1)

  • Output: plans.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
27. 0.013 0.013 ↑ 120.0 1 1

Seq Scan on public.plans (cost=0.00..11.20 rows=120 width=4) (actual time=0.013..0.013 rows=1 loops=1)

  • Output: plans.id
  • Buffers: shared hit=1
28. 146.224 146.224 ↑ 1.0 1 73,112

Index Only Scan using roaming_days_json on public.roaming_days (cost=0.28..0.31 rows=1 width=44) (actual time=0.002..0.002 rows=1 loops=73,112)

  • Output: roaming_days.filter_date, roaming_days.carrier_account_id, roaming_days.country_id, 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.filter_date = app_usage_day_summaries.filter_date) AND (roaming_days.carrier_account_id = app_usage_day_summaries.carrier_account_id))
  • Heap Fetches: 73112
  • Buffers: shared hit=219933