explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J0OV

Settings
# exclusive inclusive rows x rows loops node
1. 777.690 5,263.601 ↑ 121.5 85,287 1

WindowAgg (cost=6,368,823.13..6,757,412.01 rows=10,362,370 width=176) (actual time=4,463.438..5,263.601 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=290333, temp read=1674 written=1680
2. 175.272 4,485.911 ↑ 121.5 85,287 1

Sort (cost=6,368,823.13..6,394,729.06 rows=10,362,370 width=128) (actual time=4,463.222..4,485.911 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=290333, temp read=1674 written=1680
3. 187.372 4,310.639 ↑ 121.5 85,287 1

WindowAgg (cost=1,001.71..3,142,510.21 rows=10,362,370 width=128) (actual time=76.709..4,310.639 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=290333
4. 0.000 4,123.267 ↑ 121.5 85,287 1

Gather Merge (cost=1,001.71..2,909,356.88 rows=10,362,370 width=120) (actual time=36.043..4,123.267 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
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=1381150
5. 144.147 11,158.221 ↑ 151.9 28,429 3

Nested Loop Left Join (cost=1.69..1,712,282.24 rows=4,317,654 width=120) (actual time=0.362..3,719.407 rows=28,429 loops=3)

  • 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=1381150
  • Worker 0: actual time=0.410..4306.549 rows=33838 loops=1
  • Buffers: shared hit=548012
  • Worker 1: actual time=0.442..4310.499 rows=33521 loops=1
  • Buffers: shared hit=542805
6. 152.673 10,672.926 ↑ 151.9 28,429 3

Nested Loop (cost=1.41..320,833.85 rows=4,317,654 width=92) (actual time=0.268..3,557.642 rows=28,429 loops=3)

  • 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
  • Buffers: shared hit=1124393
  • Worker 0: actual time=0.287..4097.523 rows=33838 loops=1
  • Buffers: shared hit=446091
  • Worker 1: actual time=0.319..4132.860 rows=33521 loops=1
  • Buffers: shared hit=441908
7. 128.133 1,394.544 ↑ 1.2 28,429 3

Nested Loop (cost=1.26..75,694.32 rows=35,536 width=104) (actual time=0.171..464.848 rows=28,429 loops=3)

  • 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, tariffs.plan_id, tariffs.roaming_rule_id, tariffs.id
  • Join Filter: (app_usage_day_summaries.plan_id = tariffs.plan_id)
  • Buffers: shared hit=868530
  • Worker 0: actual time=0.183..540.900 rows=33838 loops=1
  • Buffers: shared hit=344576
  • Worker 1: actual time=0.215..566.085 rows=33521 loops=1
  • Buffers: shared hit=341344
8. 95.733 1,095.837 ↑ 1.2 28,429 3

Nested Loop (cost=1.12..69,167.01 rows=35,536 width=96) (actual time=0.140..365.279 rows=28,429 loops=3)

  • 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, au_mncs.country_id
  • Inner Unique: true
  • Buffers: shared hit=697954
  • Worker 0: actual time=0.142..426.587 rows=33838 loops=1
  • Buffers: shared hit=276899
  • Worker 1: actual time=0.172..450.379 rows=33521 loops=1
  • Buffers: shared hit=274301
9. 72.393 829.530 ↑ 1.2 28,429 3

Nested Loop (cost=0.84..58,645.91 rows=35,536 width=96) (actual time=0.110..276.510 rows=28,429 loops=3)

  • 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, au_mncs.country_id
  • Inner Unique: true
  • Buffers: shared hit=442093
  • Worker 0: actual time=0.111..322.278 rows=33838 loops=1
  • Buffers: shared hit=175385
  • Worker 1: actual time=0.120..348.470 rows=33521 loops=1
  • Buffers: shared hit=173738
10. 188.196 501.276 ↑ 1.2 28,429 3

Nested Loop (cost=0.56..24,823.37 rows=35,536 width=96) (actual time=0.077..167.092 rows=28,429 loops=3)

  • 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, au_network_names.mnc_id
  • Inner Unique: true
  • Buffers: shared hit=186230
  • Worker 0: actual time=0.073..196.383 rows=33838 loops=1
  • Buffers: shared hit=73870
  • Worker 1: actual time=0.074..214.566 rows=33521 loops=1
  • Buffers: shared hit=73174
11. 142.506 142.506 ↑ 1.2 28,429 3

Parallel Index Scan using mv_app_day_partition_idx on public.mv_app_usage_day_summaries app_usage_day_summaries (cost=0.42..19,033.25 rows=35,536 width=92) (actual time=0.040..47.502 rows=28,429 loops=3)

  • 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=15654
  • Worker 0: actual time=0.028..52.758 rows=33838 loops=1
  • Buffers: shared hit=6193
  • Worker 1: actual time=0.026..73.027 rows=33521 loops=1
  • Buffers: shared hit=6131
12. 170.574 170.574 ↑ 1.0 1 85,287

Index Scan using network_names_pkey on public.network_names au_network_names (cost=0.14..0.16 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=85,287)

  • Output: au_network_names.id, au_network_names.user_id, au_network_names.name, au_network_names.created_at, au_network_names.updated_at, au_network_names.network_type_id, au_network_names.mnc_id
  • Index Cond: (au_network_names.id = app_usage_day_summaries.network_name_id)
  • Buffers: shared hit=170576
  • Worker 0: actual time=0.002..0.002 rows=1 loops=33838
  • Buffers: shared hit=67677
  • Worker 1: actual time=0.002..0.002 rows=1 loops=33521
  • Buffers: shared hit=67043
13. 255.861 255.861 ↑ 1.0 1 85,287

Index Scan using mncs_pkey on public.mncs au_mncs (cost=0.28..0.95 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=85,287)

  • 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
  • Index Cond: (au_mncs.id = au_network_names.mnc_id)
  • Buffers: shared hit=255863
  • Worker 0: actual time=0.002..0.002 rows=1 loops=33838
  • Buffers: shared hit=101515
  • Worker 1: actual time=0.003..0.003 rows=1 loops=33521
  • Buffers: shared hit=100564
14. 170.574 170.574 ↑ 1.0 1 85,287

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

  • Output: au_sim_mncs.id
  • Index Cond: (au_sim_mncs.id = app_usage_day_summaries.sim_mnc_id)
  • Heap Fetches: 85287
  • Buffers: shared hit=255861
  • Worker 0: actual time=0.002..0.002 rows=1 loops=33838
  • Buffers: shared hit=101514
  • Worker 1: actual time=0.002..0.002 rows=1 loops=33521
  • Buffers: shared hit=100563
15. 170.574 170.574 ↑ 1.0 1 85,287

Index Scan using index_tariffs_on_country_id on public.tariffs (cost=0.14..0.17 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=85,287)

  • Output: tariffs.id, tariffs.name, tariffs.voice_rate, tariffs.created_at, tariffs.updated_at, tariffs.bill_per_minute, tariffs.plan_id, tariffs.country_id, tariffs.text_message_rate, tariffs.roaming_voice_rate, tariffs.roaming_data_mb_rate, tariffs.roaming_text_message_rate, tariffs.roaming_rule_id
  • Index Cond: (tariffs.country_id = au_mncs.country_id)
  • Buffers: shared hit=170576
  • Worker 0: actual time=0.002..0.002 rows=1 loops=33838
  • Buffers: shared hit=67677
  • Worker 1: actual time=0.002..0.002 rows=1 loops=33521
  • Buffers: shared hit=67043
16. 9,125.709 9,125.709 ↑ 122.0 1 85,287

Index Scan using index_tariffs_on_plan_id on public.tariffs roaming_tariffs (cost=0.14..5.68 rows=122 width=16) (actual time=0.091..0.107 rows=1 loops=85,287)

  • 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
  • Index Cond: (roaming_tariffs.plan_id = tariffs.plan_id)
  • 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 Filter: 242
  • Buffers: shared hit=255863
  • Worker 0: actual time=0.088..0.104 rows=1 loops=33838
  • Buffers: shared hit=101515
  • Worker 1: actual time=0.089..0.105 rows=1 loops=33521
  • Buffers: shared hit=100564
17. 341.148 341.148 ↑ 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.004..0.004 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=256757
  • Worker 0: actual time=0.005..0.005 rows=1 loops=33838
  • Buffers: shared hit=101921
  • Worker 1: actual time=0.004..0.004 rows=1 loops=33521
  • Buffers: shared hit=100897