explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DVvL

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.927 ↓ 0.0 0 1

WindowAgg (cost=147.05..523.33 rows=46 width=137) (actual time=0.926..0.927 rows=0 loops=1)

  • Output: app_usage_period_summaries.id, app_usage_period_summaries.company_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, (sum(CASE WHEN app_usage_period_summaries.is_roaming THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?)), sum(CASE WHEN (app_usage_period_summaries.tariff_country_id = au_sim_mncs.country_id) THEN NULL::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?), (SubPlan 1), (SubPlan 2)
  • Buffers: shared hit=30
2. 0.005 0.926 ↓ 0.0 0 1

Sort (cost=147.05..147.08 rows=46 width=69) (actual time=0.926..0.926 rows=0 loops=1)

  • Output: app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date, app_usage_period_summaries.is_roaming, app_usage_period_summaries.carrier_account_id, app_usage_period_summaries.id, app_usage_period_summaries.company_id, app_usage_period_summaries.tariff_id, app_usage_period_summaries.category_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.total_bytes, au_sim_mncs.country_id, (sum(CASE WHEN app_usage_period_summaries.is_roaming THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?))
  • Sort Key: app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.end_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=30
3. 0.001 0.921 ↓ 0.0 0 1

WindowAgg (cost=146.52..146.80 rows=46 width=69) (actual time=0.921..0.921 rows=0 loops=1)

  • Output: app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date, app_usage_period_summaries.is_roaming, app_usage_period_summaries.carrier_account_id, app_usage_period_summaries.id, app_usage_period_summaries.company_id, app_usage_period_summaries.tariff_id, app_usage_period_summaries.category_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.total_bytes, au_sim_mncs.country_id, sum(CASE WHEN app_usage_period_summaries.is_roaming THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?)
  • Buffers: shared hit=30
4. 0.014 0.920 ↓ 0.0 0 1

Sort (cost=146.52..146.55 rows=46 width=61) (actual time=0.920..0.920 rows=0 loops=1)

  • Output: app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date, app_usage_period_summaries.is_roaming, app_usage_period_summaries.carrier_account_id, app_usage_period_summaries.id, app_usage_period_summaries.company_id, app_usage_period_summaries.tariff_id, app_usage_period_summaries.category_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.total_bytes, au_sim_mncs.country_id
  • Sort Key: app_usage_period_summaries.is_roaming, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=30
5. 0.006 0.906 ↓ 0.0 0 1

Hash Join (cost=60.28..146.27 rows=46 width=61) (actual time=0.906..0.906 rows=0 loops=1)

  • Output: app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date, app_usage_period_summaries.is_roaming, app_usage_period_summaries.carrier_account_id, app_usage_period_summaries.id, app_usage_period_summaries.company_id, app_usage_period_summaries.tariff_id, app_usage_period_summaries.category_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.total_bytes, au_sim_mncs.country_id
  • Inner Unique: true
  • Hash Cond: (app_usage_period_summaries.sim_mnc_id = au_sim_mncs.id)
  • Buffers: shared hit=30
6. 0.003 0.355 ↓ 0.0 0 1

Bitmap Heap Scan on public.mv_app_usage_period_summaries app_usage_period_summaries (cost=31.80..117.76 rows=47 width=57) (actual time=0.355..0.355 rows=0 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
  • Recheck Cond: ((app_usage_period_summaries.end_date >= '2019-05-01'::date) AND (app_usage_period_summaries.end_date <= '2019-05-31'::date) AND (app_usage_period_summaries.carrier_account_id = 5))
  • Buffers: shared hit=14
7. 0.030 0.352 ↓ 0.0 0 1

BitmapAnd (cost=31.80..31.80 rows=47 width=0) (actual time=0.352..0.352 rows=0 loops=1)

  • Buffers: shared hit=14
8. 0.087 0.087 ↓ 1.1 546 1

Bitmap Index Scan on index_mv_app_usage_period_summaries_on_end_date (cost=0.00..5.04 rows=490 width=0) (actual time=0.087..0.087 rows=546 loops=1)

  • Index Cond: ((app_usage_period_summaries.end_date >= '2019-05-01'::date) AND (app_usage_period_summaries.end_date <= '2019-05-31'::date))
  • Buffers: shared hit=4
9. 0.235 0.235 ↓ 1.0 3,159 1

Bitmap Index Scan on index_mv_app_usage_period_summaries_on_carrier_account_id (cost=0.00..26.70 rows=3,098 width=0) (actual time=0.235..0.235 rows=3,159 loops=1)

  • Index Cond: (app_usage_period_summaries.carrier_account_id = 5)
  • Buffers: shared hit=10
10. 0.273 0.545 ↑ 1.0 1,921 1

Hash (cost=21.76..21.76 rows=1,921 width=8) (actual time=0.545..0.545 rows=1,921 loops=1)

  • Output: au_sim_mncs.country_id, au_sim_mncs.id
  • Buckets: 2048 Batches: 1 Memory Usage: 91kB
  • Buffers: shared hit=16
11. 0.272 0.272 ↑ 1.0 1,921 1

Seq Scan on public.mncs au_sim_mncs (cost=0.00..21.76 rows=1,921 width=8) (actual time=0.010..0.272 rows=1,921 loops=1)

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

SubPlan (forWindowAgg)

13. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=4.06..4.07 rows=1 width=32) (never executed)

  • 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))
14. 0.000 0.000 ↓ 0.0 0

Result (cost=0.06..4.06 rows=1 width=36) (never executed)

  • 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
15. 0.000 0.000 ↓ 0.0 0

Index Scan using roaming_days_uniqueness_constraint on public.roaming_days (cost=0.06..4.06 rows=1 width=36) (never executed)

  • 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))
16. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=4.10..4.11 rows=1 width=32) (never executed)

  • Output: json_agg((json_build_object('filter_date', details.filter_date, 'total_bytes', sum(details.total_bytes))))
17. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=4.09..4.10 rows=1 width=40) (never executed)

  • Output: json_build_object('filter_date', details.filter_date, 'total_bytes', sum(details.total_bytes)), details.filter_date
  • Group Key: details.filter_date
18. 0.000 0.000 ↓ 0.0 0

Sort (cost=4.09..4.10 rows=1 width=16) (never executed)

  • Output: details.filter_date, details.total_bytes
  • Sort Key: details.filter_date
19. 0.000 0.000 ↓ 0.0 0

Result (cost=0.08..4.09 rows=1 width=16) (never executed)

  • Output: details.filter_date, details.total_bytes
  • One-Time Filter: app_usage_period_summaries.is_roaming
20. 0.000 0.000 ↓ 0.0 0

Index Scan using mv_app_idx_roaming01_idx on public.mv_app_usage_day_summaries details (cost=0.08..4.09 rows=1 width=16) (never executed)

  • Output: details.id, details.filter_date, details.carrier_account_id, details.network_name_id, details.is_roaming, details.device_id, details.app_detail_parent_id, details.app_detail_parent_name, details.app_detail_parent_is_group, details.app_detail_parent_label, details.category_id, details.app_id, details.tariff_id, details.sim_mnc_id, details.category_name, details.user_id, details.user_name, details.company_id, details.device_name, details.network_name_name, details.tariff_country_id, details.country_name, details.carrier_account_phone_number, details.carrier_account_name, details.contract_start_date, details.start_date, details.end_date, details.plan_id, details.plan_name, details.plan_rate_type, details.plan_data_cost_in_cents_per_month, details.plan_data_in_bytes_per_month, details.plan_overage_data_mb_rate, details.tariff_name, details.tariff_roaming_data_mb_rate, details.total_bytes, details.network_type_code, details.is_free
  • Index Cond: ((details.end_date = app_usage_period_summaries.end_date) AND (details.category_id = app_usage_period_summaries.category_id) AND (details.network_type_code = app_usage_period_summaries.network_type_code) AND (details.carrier_account_id = app_usage_period_summaries.carrier_account_id) AND (details.network_name_id = app_usage_period_summaries.network_name_id) AND (details.sim_mnc_id = app_usage_period_summaries.sim_mnc_id))
  • Filter: ((details.tariff_id = app_usage_period_summaries.tariff_id) AND (details.tariff_country_id = app_usage_period_summaries.tariff_country_id) AND (details.is_roaming = app_usage_period_summaries.is_roaming))
Planning time : 0.769 ms
Execution time : 1.074 ms