explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W2Ws

Settings
# exclusive inclusive rows x rows loops node
1. 727.111 4,004.859 ↑ 1.0 73,112 1

WindowAgg (cost=32,705.76..35,447.46 rows=73,112 width=176) (actual time=3,256.101..4,004.859 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', 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)), 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=3074, temp read=2923 written=2933
2. 139.017 3,277.748 ↑ 1.0 73,112 1

Sort (cost=32,705.76..32,888.54 rows=73,112 width=132) (actual time=3,255.849..3,277.748 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.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, 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: 12056kB
  • Buffers: shared hit=3074, temp read=2923 written=2933
3. 76.236 3,138.731 ↑ 1.0 73,112 1

WindowAgg (cost=19,973.31..21,801.11 rows=73,112 width=132) (actual time=3,032.962..3,138.731 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.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, 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=3074, temp read=1416 written=1421
4. 137.493 3,062.495 ↑ 1.0 73,112 1

Sort (cost=19,973.31..20,156.09 rows=73,112 width=124) (actual time=3,032.760..3,062.495 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.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, 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: 11328kB
  • Buffers: shared hit=3074, temp read=1416 written=1421
5. 2,809.686 2,925.002 ↑ 1.0 73,112 1

Hash Left Join (cost=252.54..9,317.15 rows=73,112 width=124) (actual time=5.368..2,925.002 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.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, 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
  • Hash Cond: ((app_usage_day_summaries.carrier_account_id = roaming_days.carrier_account_id) AND (CASE WHEN (roaming_tariffs.country_id IS NULL) THEN app_usage_day_summaries.tariff_country_id ELSE roaming_tariffs.country_id END = roaming_days.country_id))
  • Join Filter: (roaming_days.filter_date = app_usage_day_summaries.filter_date)
  • Rows Removed by Join Filter: 19845382
  • Buffers: shared hit=3074
6. 25.480 110.920 ↑ 1.0 73,112 1

Hash Left Join (cost=65.54..8,746.32 rows=73,112 width=92) (actual time=0.873..110.920 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
  • Hash Cond: ((app_usage_day_summaries.plan_id = roaming_tariffs.plan_id) AND (app_usage_day_summaries.roaming_rule_id = roaming_tariffs.roaming_rule_id))
  • Buffers: shared hit=3023
7. 59.068 85.314 ↑ 1.0 73,112 1

Hash Join (cost=57.47..3,985.95 rows=73,112 width=96) (actual time=0.707..85.314 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, app_usage_day_summaries.plan_id, app_usage_day_summaries.roaming_rule_id
  • Inner Unique: true
  • Hash Cond: (app_usage_day_summaries.sim_mnc_id = au_sim_mncs.id)
  • Buffers: shared hit=3021
8. 25.563 25.563 ↑ 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=96) (actual time=0.013..25.563 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
9. 0.349 0.683 ↑ 1.0 1,843 1

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

  • Output: au_sim_mncs.id
  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
  • Buffers: shared hit=16
10. 0.334 0.334 ↑ 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.011..0.334 rows=1,843 loops=1)

  • Output: au_sim_mncs.id
  • Buffers: shared hit=16
11. 0.038 0.126 ↓ 0.0 0 1

Hash (cost=4.43..4.43 rows=243 width=12) (actual time=0.126..0.126 rows=0 loops=1)

  • Output: roaming_tariffs.plan_id, roaming_tariffs.roaming_rule_id, roaming_tariffs.country_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=2
12. 0.088 0.088 ↑ 1.0 243 1

Seq Scan on public.tariffs roaming_tariffs (cost=0.00..4.43 rows=243 width=12) (actual time=0.019..0.088 rows=243 loops=1)

  • Output: roaming_tariffs.plan_id, roaming_tariffs.roaming_rule_id, roaming_tariffs.country_id
  • Buffers: shared hit=2
13. 2.676 4.396 ↑ 1.0 5,440 1

Hash (cost=105.40..105.40 rows=5,440 width=44) (actual time=4.396..4.396 rows=5,440 loops=1)

  • 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, roaming_days.carrier_account_id, roaming_days.country_id
  • Buckets: 8192 Batches: 1 Memory Usage: 489kB
  • Buffers: shared hit=51
14. 1.720 1.720 ↑ 1.0 5,440 1

Seq Scan on public.roaming_days (cost=0.00..105.40 rows=5,440 width=44) (actual time=0.023..1.720 rows=5,440 loops=1)

  • 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, roaming_days.carrier_account_id, roaming_days.country_id
  • Buffers: shared hit=51
Planning time : 0.841 ms