explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Y5o

Settings
# exclusive inclusive rows x rows loops node
1. 841.955 1,557.108 ↓ 118.5 85,287 1

WindowAgg (cost=5,453.31..5,480.31 rows=720 width=176) (actual time=690.051..1,557.108 rows=85,287 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=260271, temp read=3245 written=3257
2. 142.323 715.153 ↓ 118.5 85,287 1

Sort (cost=5,453.31..5,455.11 rows=720 width=128) (actual time=689.825..715.153 rows=85,287 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: 13392kB
  • Buffers: shared hit=260271, temp read=3245 written=3257
3. 74.518 572.830 ↓ 118.5 85,287 1

WindowAgg (cost=5,401.14..5,419.14 rows=720 width=128) (actual time=468.875..572.830 rows=85,287 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=260271, temp read=1571 written=1577
4. 166.460 498.312 ↓ 118.5 85,287 1

Sort (cost=5,401.14..5,402.94 rows=720 width=120) (actual time=468.668..498.312 rows=85,287 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: 12568kB
  • Buffers: shared hit=260271, temp read=1571 written=1577
5. 34.304 331.852 ↓ 118.5 85,287 1

Nested Loop Left Join (cost=149.59..5,366.97 rows=720 width=120) (actual time=4.784..331.852 rows=85,287 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=260271
6. 33.807 126.974 ↓ 118.5 85,287 1

Hash Join (cost=149.31..5,134.93 rows=720 width=92) (actual time=4.737..126.974 rows=85,287 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: (app_usage_day_summaries.sim_mnc_id = au_sim_mncs.id)
  • Buffers: shared hit=3522
7. 69.410 92.509 ↓ 118.5 85,287 1

Hash Join (cost=91.84..5,075.58 rows=720 width=92) (actual time=4.042..92.509 rows=85,287 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
  • Hash Cond: ((app_usage_day_summaries.network_name_id = au_network_names.id) AND (app_usage_day_summaries.plan_id = plans.id))
  • Buffers: shared hit=3506
8. 19.094 19.094 ↑ 1.0 85,287 1

Seq Scan on public.mv_app_usage_day_summaries app_usage_day_summaries (cost=0.00..4,336.87 rows=85,287 width=92) (actual time=0.006..19.094 rows=85,287 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=3484
9. 0.023 4.005 ↑ 14.2 10 1

Hash (cost=89.71..89.71 rows=142 width=20) (actual time=4.005..4.005 rows=10 loops=1)

  • Output: au_network_names.id, tariffs.plan_id, plans.id, roaming_tariffs.plan_id, roaming_tariffs.country_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=22
10. 0.464 3.982 ↑ 14.2 10 1

Hash Join (cost=80.11..89.71 rows=142 width=20) (actual time=3.582..3.982 rows=10 loops=1)

  • Output: au_network_names.id, tariffs.plan_id, plans.id, roaming_tariffs.plan_id, roaming_tariffs.country_id
  • Hash Cond: (roaming_tariffs.plan_id = plans.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: 2420
  • Buffers: shared hit=22
11. 0.043 0.043 ↑ 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.043 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
12. 0.005 3.475 ↑ 14.0 10 1

Hash (cost=78.36..78.36 rows=140 width=20) (actual time=3.475..3.475 rows=10 loops=1)

  • Output: au_network_names.id, tariffs.plan_id, tariffs.roaming_rule_id, tariffs.id, plans.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=20
13. 0.026 3.470 ↑ 14.0 10 1

Hash Join (cost=33.32..78.36 rows=140 width=20) (actual time=2.942..3.470 rows=10 loops=1)

  • Output: au_network_names.id, tariffs.plan_id, tariffs.roaming_rule_id, tariffs.id, plans.id
  • Inner Unique: true
  • Hash Cond: (tariffs.plan_id = plans.id)
  • Buffers: shared hit=20
14. 2.586 3.437 ↑ 14.0 10 1

Hash Join (cost=20.62..65.28 rows=140 width=16) (actual time=2.913..3.437 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
15. 0.355 0.666 ↑ 14.0 10 1

Hash Join (cost=13.15..55.89 rows=140 width=8) (actual time=0.147..0.666 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
16. 0.294 0.294 ↑ 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.009..0.294 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
17. 0.006 0.017 ↑ 14.0 10 1

Hash (cost=11.40..11.40 rows=140 width=8) (actual time=0.016..0.017 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
18. 0.011 0.011 ↑ 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.009..0.011 rows=10 loops=1)

  • Output: au_network_names.id, au_network_names.mnc_id
  • Buffers: shared hit=1
19. 0.086 0.185 ↑ 1.0 243 1

Hash (cost=4.43..4.43 rows=243 width=16) (actual time=0.185..0.185 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
20. 0.099 0.099 ↑ 1.0 243 1

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

  • Output: tariffs.plan_id, tariffs.country_id, tariffs.roaming_rule_id, tariffs.id
  • Buffers: shared hit=2
21. 0.001 0.007 ↑ 120.0 1 1

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

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

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

  • Output: plans.id
  • Buffers: shared hit=1
23. 0.358 0.658 ↑ 1.0 1,843 1

Hash (cost=34.43..34.43 rows=1,843 width=4) (actual time=0.658..0.658 rows=1,843 loops=1)

  • Output: au_sim_mncs.id
  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
  • Buffers: shared hit=16
24. 0.300 0.300 ↑ 1.0 1,843 1

Seq Scan on public.mncs au_sim_mncs (cost=0.00..34.43 rows=1,843 width=4) (actual time=0.016..0.300 rows=1,843 loops=1)

  • Output: au_sim_mncs.id
  • Buffers: shared hit=16
25. 170.574 170.574 ↑ 1.0 1 85,287

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=85,287)

  • 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: 85287
  • Buffers: shared hit=256749
Planning time : 12.386 ms