explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3Fpv

Settings
# exclusive inclusive rows x rows loops node
1. 100.977 2,620.119 ↑ 1.0 35,573 1

HashAggregate (cost=2,098.65..425,239.49 rows=35,573 width=97) (actual time=47.934..2,620.119 rows=35,573 loops=1)

  • Output: app_usage_period_summaries.id, app_usage_period_summaries.tariff_id, app_usage_period_summaries.category_id, app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.tariff_country_id, app_usage_period_summaries.network_name_id, app_usage_period_summaries.network_type_code, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date, app_usage_period_summaries.total_bytes, app_usage_period_summaries.is_roaming, app_usage_period_summaries.carrier_account_id, (SubPlan 1), 1, app_usage_period_summaries.plan_id, au_sim_mncs.country_id
  • Group Key: app_usage_period_summaries.id, au_sim_mncs.country_id
  • Buffers: shared hit=43288
2. 21.628 29.032 ↑ 1.0 35,573 1

Hash Join (cost=57.47..1,920.79 rows=35,573 width=61) (actual time=0.671..29.032 rows=35,573 loops=1)

  • Output: app_usage_period_summaries.id, au_sim_mncs.country_id, app_usage_period_summaries.tariff_id, app_usage_period_summaries.category_id, app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.tariff_country_id, app_usage_period_summaries.network_name_id, app_usage_period_summaries.network_type_code, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date, app_usage_period_summaries.total_bytes, app_usage_period_summaries.is_roaming, app_usage_period_summaries.carrier_account_id, app_usage_period_summaries.plan_id
  • Inner Unique: true
  • Hash Cond: (app_usage_period_summaries.sim_mnc_id = au_sim_mncs.id)
  • Buffers: shared hit=1430
3. 6.751 6.751 ↑ 1.0 35,573 1

Seq Scan on public.mv_app_usage_period_summaries app_usage_period_summaries (cost=0.00..1,769.73 rows=35,573 width=57) (actual time=0.009..6.751 rows=35,573 loops=1)

  • Output: app_usage_period_summaries.id, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date, app_usage_period_summaries.carrier_account_id, app_usage_period_summaries.network_name_id, app_usage_period_summaries.is_roaming, app_usage_period_summaries.device_id, app_usage_period_summaries.app_detail_parent_id, app_usage_period_summaries.app_detail_parent_name, app_usage_period_summaries.app_detail_parent_is_group, app_usage_period_summaries.app_detail_parent_label, app_usage_period_summaries.category_id, app_usage_period_summaries.app_id, app_usage_period_summaries.tariff_id, app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.category_name, app_usage_period_summaries.user_id, app_usage_period_summaries.user_name, app_usage_period_summaries.company_id, app_usage_period_summaries.device_name, app_usage_period_summaries.network_name_name, app_usage_period_summaries.tariff_country_id, app_usage_period_summaries.country_name, app_usage_period_summaries.carrier_account_phone_number, app_usage_period_summaries.carrier_account_name, app_usage_period_summaries.contract_start_date, app_usage_period_summaries.plan_id, app_usage_period_summaries.plan_name, app_usage_period_summaries.plan_rate_type, app_usage_period_summaries.plan_data_cost_in_cents_per_month, app_usage_period_summaries.plan_data_in_bytes_per_month, app_usage_period_summaries.plan_overage_data_mb_rate, app_usage_period_summaries.tariff_name, app_usage_period_summaries.tariff_roaming_data_mb_rate, app_usage_period_summaries.total_bytes, app_usage_period_summaries.network_type_code, app_usage_period_summaries.is_free, app_usage_period_summaries.roaming_details, app_usage_period_summaries.roaming_days, app_usage_period_summaries.mnc_id, app_usage_period_summaries.roaming_rule_id
  • Buffers: shared hit=1414
4. 0.333 0.653 ↑ 1.0 1,843 1

Hash (cost=34.43..34.43 rows=1,843 width=8) (actual time=0.653..0.653 rows=1,843 loops=1)

  • Output: au_sim_mncs.country_id, au_sim_mncs.id
  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
  • Buffers: shared hit=16
5. 0.320 0.320 ↑ 1.0 1,843 1

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

  • Output: au_sim_mncs.country_id, au_sim_mncs.id
  • Buffers: shared hit=16
6.          

SubPlan (forHashAggregate)

7. 2,205.526 2,490.110 ↑ 1.0 1 35,573

Aggregate (cost=11.88..11.88 rows=1 width=32) (actual time=0.070..0.070 rows=1 loops=35,573)

  • 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=41858
8. 111.936 284.584 ↓ 3.3 10 35,573

Result (cost=0.28..11.86 rows=3 width=36) (actual time=0.002..0.008 rows=10 loops=35,573)

  • 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_period_summaries.is_roaming
  • Buffers: shared hit=41858
9. 172.648 172.648 ↓ 10.0 30 12,332

Index Scan using roaming_days_uniqueness_constraint on public.roaming_days (cost=0.28..11.86 rows=3 width=36) (actual time=0.006..0.014 rows=30 loops=12,332)

  • 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_period_summaries.carrier_account_id) AND (roaming_days.country_id = app_usage_period_summaries.tariff_country_id) AND (roaming_days.filter_date >= app_usage_period_summaries.start_date) AND (roaming_days.filter_date <= app_usage_period_summaries.end_date))
  • Buffers: shared hit=41858
Planning time : 0.362 ms