explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0wW4

Settings
# exclusive inclusive rows x rows loops node
1. 705.181 2,257.995 ↑ 1.0 206,452 1

WindowAgg (cost=74,807.99..1,800,230.58 rows=206,452 width=180) (actual time=883.134..2,257.995 rows=206,452 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 (?), (SubPlan 1), 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.company_id, app_usage_day_summaries.start_date
  • Buffers: shared hit=137664, temp read=6222 written=6244
2. 271.408 933.458 ↑ 1.0 206,452 1

Sort (cost=74,807.99..75,324.12 rows=206,452 width=100) (actual time=882.814..933.458 rows=206,452 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, 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.company_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: 25888kB
  • Buffers: shared hit=8669, temp read=6222 written=6244
3. 156.660 662.050 ↑ 1.0 206,452 1

WindowAgg (cost=40,130.68..45,291.98 rows=206,452 width=100) (actual time=448.602..662.050 rows=206,452 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, 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.company_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=8669, temp read=2986 written=2996
4. 331.676 505.390 ↑ 1.0 206,452 1

Sort (cost=40,130.68..40,646.81 rows=206,452 width=92) (actual time=448.433..505.390 rows=206,452 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, 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.company_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: 23888kB
  • Buffers: shared hit=8669, temp read=2986 written=2996
5. 132.985 173.714 ↑ 1.0 206,452 1

Hash Join (cost=57.47..11,318.17 rows=206,452 width=92) (actual time=0.885..173.714 rows=206,452 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, 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.company_id
  • Inner Unique: true
  • Hash Cond: (app_usage_day_summaries.sim_mnc_id = au_sim_mncs.id)
  • Buffers: shared hit=8669
6. 39.874 39.874 ↑ 1.0 206,452 1

Seq Scan on public.mv_app_usage_day_summaries app_usage_day_summaries (cost=0.00..10,717.52 rows=206,452 width=92) (actual time=0.017..39.874 rows=206,452 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, app_usage_day_summaries.roaming_days, app_usage_day_summaries.roaming_details
  • Buffers: shared hit=8653
7. 0.454 0.855 ↑ 1.0 1,843 1

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

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

  • Output: au_sim_mncs.id
  • Buffers: shared hit=16
9.          

SubPlan (forWindowAgg)

10. 412.904 619.356 ↑ 1.0 1 206,452

Aggregate (cost=8.31..8.32 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=206,452)

  • 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=128995
11. 120.742 206.452 ↓ 0.0 0 206,452

Result (cost=0.29..8.31 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=206,452)

  • 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_day_summaries.is_roaming
  • Buffers: shared hit=128995
12. 85.710 85.710 ↑ 1.0 1 42,855

Index Scan using roaming_days_uniqueness_constraint on public.roaming_days (cost=0.29..8.31 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=42,855)

  • 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_day_summaries.carrier_account_id) AND (roaming_days.country_id = app_usage_day_summaries.tariff_country_id) AND (roaming_days.filter_date = app_usage_day_summaries.filter_date))
  • Buffers: shared hit=128995
Planning time : 0.758 ms