explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E7Ab

Settings
# exclusive inclusive rows x rows loops node
1. 14.813 7,544.049 ↑ 1.0 35,573 1

Subquery Scan on app_period_roaming_original (cost=9,023.51..6,846,791.19 rows=35,573 width=133) (actual time=190.134..7,544.049 rows=35,573 loops=1)

  • Output: app_period_roaming_original.id, app_period_roaming_original.tariff_id, app_period_roaming_original.category_id, app_period_roaming_original.sim_mnc_id, app_period_roaming_original.tariff_country_id, app_period_roaming_original.network_name_id, app_period_roaming_original.network_type_code, app_period_roaming_original.start_date, app_period_roaming_original.end_date, app_period_roaming_original.total_bytes, app_period_roaming_original.is_roaming, app_period_roaming_original.carrier_account_id, app_period_roaming_original.total_billing_usage, app_period_roaming_original.total_roaming_country_usage, app_period_roaming_original.roaming_days, app_period_roaming_original.roaming_details
  • Buffers: shared hit=309006, temp read=698 written=700
2. 174.824 7,529.236 ↑ 1.0 35,573 1

Group (cost=9,023.51..6,846,435.46 rows=35,573 width=145) (actual time=190.130..7,529.236 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, (sum(CASE WHEN (app_usage_period_summaries.is_roaming OR app_usage_period_summaries.is_free) THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?)), (SubPlan 1), (SubPlan 2), (SubPlan 3), au_mncs.country_id, au_sim_mncs.country_id, plans.id
  • Group Key: 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, (sum(CASE WHEN (app_usage_period_summaries.is_roaming OR app_usage_period_summaries.is_free) THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?)), au_mncs.country_id, au_sim_mncs.country_id, plans.id
  • Buffers: shared hit=309006, temp read=698 written=700
3. 43.187 204.239 ↑ 1.0 35,573 1

Sort (cost=9,023.51..9,112.44 rows=35,573 width=73) (actual time=189.952..204.239 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, (sum(CASE WHEN (app_usage_period_summaries.is_roaming OR app_usage_period_summaries.is_free) THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?)), au_mncs.country_id, au_sim_mncs.country_id, plans.id
  • Sort Key: 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, (sum(CASE WHEN (app_usage_period_summaries.is_roaming OR app_usage_period_summaries.is_free) THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?)), au_mncs.country_id, au_sim_mncs.country_id, plans.id
  • Sort Method: external merge Disk: 3000kB
  • Buffers: shared hit=1449, temp read=698 written=700
4. 12.086 161.052 ↑ 1.0 35,573 1

Hash Join (cost=4,615.87..6,334.46 rows=35,573 width=73) (actual time=65.904..161.052 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, (sum(CASE WHEN (app_usage_period_summaries.is_roaming OR app_usage_period_summaries.is_free) THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?)), au_mncs.country_id, au_sim_mncs.country_id, plans.id
  • Inner Unique: true
  • Hash Cond: (carrier_accounts.plan_id = plans.id)
  • Buffers: shared hit=1449, temp read=323 written=324
5. 12.336 148.851 ↑ 1.0 35,573 1

Hash Join (cost=4,603.17..6,226.40 rows=35,573 width=73) (actual time=65.714..148.851 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, (sum(CASE WHEN (app_usage_period_summaries.is_roaming OR app_usage_period_summaries.is_free) THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?)), au_mncs.country_id, au_sim_mncs.country_id, carrier_accounts.plan_id
  • Inner Unique: true
  • Hash Cond: (app_usage_period_summaries.carrier_account_id = carrier_accounts.id)
  • Buffers: shared hit=1448, temp read=323 written=324
6. 12.076 136.412 ↑ 1.0 35,573 1

Hash Join (cost=4,586.87..6,115.31 rows=35,573 width=69) (actual time=65.511..136.412 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, (sum(CASE WHEN (app_usage_period_summaries.is_roaming OR app_usage_period_summaries.is_free) THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?)), au_mncs.country_id, au_sim_mncs.country_id
  • Inner Unique: true
  • Hash Cond: (app_usage_period_summaries.sim_mnc_id = au_sim_mncs.id)
  • Buffers: shared hit=1447, temp read=323 written=324
7. 11.782 123.394 ↑ 1.0 35,573 1

Hash Join (cost=4,529.40..5,964.25 rows=35,573 width=65) (actual time=64.312..123.394 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, (sum(CASE WHEN (app_usage_period_summaries.is_roaming OR app_usage_period_summaries.is_free) THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?)), au_mncs.country_id
  • Inner Unique: true
  • Hash Cond: (au_network_names.mnc_id = au_mncs.id)
  • Buffers: shared hit=1431, temp read=323 written=324
8. 12.424 110.657 ↑ 1.0 35,573 1

Hash Join (cost=4,471.93..5,813.19 rows=35,573 width=65) (actual time=63.302..110.657 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, (sum(CASE WHEN (app_usage_period_summaries.is_roaming OR app_usage_period_summaries.is_free) THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?)), au_network_names.mnc_id
  • Inner Unique: true
  • Hash Cond: (app_usage_period_summaries.network_name_id = au_network_names.id)
  • Buffers: shared hit=1415, temp read=323 written=324
9. 27.633 98.192 ↑ 1.0 35,573 1

WindowAgg (cost=4,458.78..5,348.11 rows=35,573 width=174) (actual time=63.222..98.192 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, sum(CASE WHEN (app_usage_period_summaries.is_roaming OR app_usage_period_summaries.is_free) THEN '0'::double precision ELSE app_usage_period_summaries.total_bytes END) OVER (?), NULL::boolean, NULL::double precision, NULL::character varying, NULL::integer, NULL::integer, NULL::json, NULL::json
  • Buffers: shared hit=1414, temp read=323 written=324
10. 49.132 70.559 ↑ 1.0 35,573 1

Sort (cost=4,458.78..4,547.71 rows=35,573 width=54) (actual time=63.147..70.559 rows=35,573 loops=1)

  • Output: 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.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.total_bytes, app_usage_period_summaries.is_free
  • Sort Key: app_usage_period_summaries.carrier_account_id, app_usage_period_summaries.is_roaming, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date
  • Sort Method: external merge Disk: 2584kB
  • Buffers: shared hit=1414, temp read=323 written=324
11. 21.427 21.427 ↑ 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=54) (actual time=0.009..21.427 rows=35,573 loops=1)

  • Output: 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.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.total_bytes, app_usage_period_summaries.is_free
  • Buffers: shared hit=1414
12. 0.011 0.041 ↑ 14.0 10 1

Hash (cost=11.40..11.40 rows=140 width=8) (actual time=0.041..0.041 rows=10 loops=1)

  • Output: au_network_names.id, au_network_names.mnc_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
13. 0.030 0.030 ↑ 14.0 10 1

Seq Scan on public.network_names au_network_names (cost=0.00..11.40 rows=140 width=8) (actual time=0.027..0.030 rows=10 loops=1)

  • Output: au_network_names.id, au_network_names.mnc_id
  • Buffers: shared hit=1
14. 0.567 0.955 ↑ 1.0 1,843 1

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

  • Output: au_mncs.country_id, au_mncs.id
  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
  • Buffers: shared hit=16
15. 0.388 0.388 ↑ 1.0 1,843 1

Seq Scan on public.mncs au_mncs (cost=0.00..34.43 rows=1,843 width=8) (actual time=0.014..0.388 rows=1,843 loops=1)

  • Output: au_mncs.country_id, au_mncs.id
  • Buffers: shared hit=16
16. 0.510 0.942 ↑ 1.0 1,843 1

Hash (cost=34.43..34.43 rows=1,843 width=8) (actual time=0.941..0.942 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
17. 0.432 0.432 ↑ 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.048..0.432 rows=1,843 loops=1)

  • Output: au_sim_mncs.country_id, au_sim_mncs.id
  • Buffers: shared hit=16
18. 0.009 0.103 ↑ 28.0 10 1

Hash (cost=12.80..12.80 rows=280 width=8) (actual time=0.103..0.103 rows=10 loops=1)

  • Output: carrier_accounts.id, carrier_accounts.plan_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
19. 0.094 0.094 ↑ 28.0 10 1

Seq Scan on public.carrier_accounts (cost=0.00..12.80 rows=280 width=8) (actual time=0.091..0.094 rows=10 loops=1)

  • Output: carrier_accounts.id, carrier_accounts.plan_id
  • Buffers: shared hit=1
20. 0.010 0.115 ↑ 120.0 1 1

Hash (cost=11.20..11.20 rows=120 width=4) (actual time=0.115..0.115 rows=1 loops=1)

  • Output: plans.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
21. 0.105 0.105 ↑ 120.0 1 1

Seq Scan on public.plans (cost=0.00..11.20 rows=120 width=4) (actual time=0.103..0.105 rows=1 loops=1)

  • Output: plans.id
  • Buffers: shared hit=1
22.          

SubPlan (forGroup)

23. 0.000 569.168 ↑ 1.0 1 35,573

Aggregate (cost=156.37..156.38 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=35,573)

  • Output: sum(ps.total_bytes)
  • Buffers: shared hit=142293
24. 35.573 569.168 ↓ 0.0 0 35,573

Nested Loop (cost=9.10..156.37 rows=1 width=8) (actual time=0.016..0.016 rows=0 loops=35,573)

  • Output: ps.total_bytes
  • Inner Unique: true
  • Buffers: shared hit=142293
25. 142.292 533.595 ↓ 0.0 0 35,573

Nested Loop (cost=8.83..155.46 rows=1 width=12) (actual time=0.015..0.015 rows=0 loops=35,573)

  • Output: ps.total_bytes, ps.sim_mnc_id
  • Buffers: shared hit=142293
26. 71.132 391.303 ↓ 0.0 0 35,573

Hash Join (cost=8.54..20.30 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=35,573)

  • Output: ps_network_names.id
  • Inner Unique: true
  • Hash Cond: (ps_network_names.mnc_id = ps_mncs.id)
  • Buffers: shared hit=142293
27. 0.014 0.014 ↑ 140.0 1 1

Seq Scan on public.network_names ps_network_names (cost=0.00..11.40 rows=140 width=8) (actual time=0.014..0.014 rows=1 loops=1)

  • Output: ps_network_names.id, ps_network_names.user_id, ps_network_names.name, ps_network_names.created_at, ps_network_names.updated_at, ps_network_names.network_type_id, ps_network_names.mnc_id
  • Buffers: shared hit=1
28. 35.573 320.157 ↓ 0.0 0 35,573

Hash (cost=8.44..8.44 rows=8 width=4) (actual time=0.009..0.009 rows=0 loops=35,573)

  • Output: ps_mncs.id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=142292
29. 284.584 284.584 ↓ 0.0 0 35,573

Index Scan using index_mncs_on_country_id on public.mncs ps_mncs (cost=0.28..8.44 rows=8 width=4) (actual time=0.008..0.008 rows=0 loops=35,573)

  • Output: ps_mncs.id
  • Index Cond: (ps_mncs.country_id = au_mncs.country_id)
  • Filter: (ps_mncs.country_id <> au_sim_mncs.country_id)
  • Rows Removed by Filter: 28
  • Buffers: shared hit=142292
30. 0.000 0.000 ↓ 0.0 0

Index Scan using index_mv_app_usage_period_summaries_on_network_name_id on public.mv_app_usage_period_summaries ps (cost=0.29..134.95 rows=21 width=16) (never executed)

  • Output: ps.id, ps.start_date, ps.end_date, ps.carrier_account_id, ps.network_name_id, ps.is_roaming, ps.device_id, ps.app_detail_parent_id, ps.app_detail_parent_name, ps.app_detail_parent_is_group, ps.app_detail_parent_label, ps.category_id, ps.app_id, ps.tariff_id, ps.sim_mnc_id, ps.category_name, ps.user_id, ps.user_name, ps.company_id, ps.device_name, ps.network_name_name, ps.tariff_country_id, ps.country_name, ps.carrier_account_phone_number, ps.carrier_account_name, ps.contract_start_date, ps.plan_id, ps.plan_name, ps.plan_rate_type, ps.plan_data_cost_in_cents_per_month, ps.plan_data_in_bytes_per_month, ps.plan_overage_data_mb_rate, ps.tariff_name, ps.tariff_roaming_data_mb_rate, ps.total_bytes, ps.network_type_code, ps.is_free, ps.roaming_details, ps.roaming_days, ps.mnc_id, ps.roaming_rule_id
  • Index Cond: (ps.network_name_id = ps_network_names.id)
  • Filter: ((ps.carrier_account_id = app_usage_period_summaries.carrier_account_id) AND (ps.end_date = app_usage_period_summaries.end_date))
31. 0.000 0.000 ↓ 0.0 0

Index Scan using mncs_pkey on public.mncs ps_sim_mncs (cost=0.28..0.79 rows=1 width=4) (never executed)

  • Output: ps_sim_mncs.id, ps_sim_mncs.country_id, ps_sim_mncs.carrier_id, ps_sim_mncs.mcc, ps_sim_mncs.mnc, ps_sim_mncs.created_at, ps_sim_mncs.updated_at
  • Index Cond: (ps_sim_mncs.id = ps.sim_mnc_id)
  • Filter: (ps_sim_mncs.country_id = au_sim_mncs.country_id)
32. 177.865 6,438.713 ↑ 1.0 1 35,573

Aggregate (cost=19.08..19.09 rows=1 width=32) (actual time=0.181..0.181 rows=1 loops=35,573)

  • Output: json_agg((json_build_object('filter_date', roaming_days.filter_date, 'phone_calls_duration_in_seconds_incoming', sum(roaming_days.phone_calls_duration_in_seconds_incoming), 'phone_calls_duration_in_seconds_outgoing', sum(roaming_days.phone_calls_duration_in_seconds_outgoing), 'phone_calls_duration_in_minutes_incoming', sum(roaming_days.phone_calls_duration_in_minutes_incoming), 'phone_calls_duration_in_minutes_outgoing', sum(roaming_days.phone_calls_duration_in_minutes_outgoing), 'text_messages_size_incoming', sum(roaming_days.text_messages_size_incoming), 'text_messages_size_outgoing', sum(roaming_days.text_messages_size_outgoing), 'app_usages_total_bytes', sum(roaming_days.app_usages_total_bytes))))
  • Buffers: shared hit=140600
33. 2,490.110 6,260.848 ↓ 5.0 10 35,573

GroupAggregate (cost=18.98..19.06 rows=2 width=36) (actual time=0.110..0.176 rows=10 loops=35,573)

  • Output: json_build_object('filter_date', roaming_days.filter_date, 'phone_calls_duration_in_seconds_incoming', sum(roaming_days.phone_calls_duration_in_seconds_incoming), 'phone_calls_duration_in_seconds_outgoing', sum(roaming_days.phone_calls_duration_in_seconds_outgoing), 'phone_calls_duration_in_minutes_incoming', sum(roaming_days.phone_calls_duration_in_minutes_incoming), 'phone_calls_duration_in_minutes_outgoing', sum(roaming_days.phone_calls_duration_in_minutes_outgoing), 'text_messages_size_incoming', sum(roaming_days.text_messages_size_incoming), 'text_messages_size_outgoing', sum(roaming_days.text_messages_size_outgoing), 'app_usages_total_bytes', sum(roaming_days.app_usages_total_bytes)), roaming_days.filter_date
  • Group Key: roaming_days.filter_date
  • Buffers: shared hit=140600
34. 177.865 3,770.738 ↓ 5.0 10 35,573

Sort (cost=18.98..18.99 rows=2 width=36) (actual time=0.105..0.106 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
  • Sort Key: roaming_days.filter_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=140600
35. 65.921 3,592.873 ↓ 5.0 10 35,573

Result (cost=18.71..18.97 rows=2 width=36) (actual time=0.040..0.101 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=140600
36. 135.652 3,526.952 ↓ 15.0 30 12,332

Merge Join (cost=18.71..18.97 rows=2 width=36) (actual time=0.114..0.286 rows=30 loops=12,332)

  • 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
  • Merge Cond: (roaming_tariffs.country_id = roaming_days.country_id)
  • Buffers: shared hit=140600
37. 1,997.784 2,848.692 ↑ 122.0 1 12,332

Nested Loop (cost=0.14..27.30 rows=122 width=4) (actual time=0.071..0.231 rows=1 loops=12,332)

  • Output: roaming_tariffs.country_id
  • Inner Unique: true
  • Join 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 Join Filter: 242
  • Buffers: shared hit=61660
38. 850.908 850.908 ↑ 1.0 243 12,332

Index Scan using index_tariffs_on_country_id on public.tariffs roaming_tariffs (cost=0.14..17.40 rows=243 width=12) (actual time=0.004..0.069 rows=243 loops=12,332)

  • 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
  • Filter: (roaming_tariffs.plan_id = plans.id)
  • Buffers: shared hit=36996
39. 0.000 0.000 ↑ 1.0 1 2,996,676

Materialize (cost=0.00..5.65 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=2,996,676)

  • Output: tariffs.roaming_rule_id, tariffs.id
  • Buffers: shared hit=24664
40. 394.624 394.624 ↑ 1.0 1 12,332

Seq Scan on public.tariffs (cost=0.00..5.64 rows=1 width=8) (actual time=0.008..0.032 rows=1 loops=12,332)

  • Output: tariffs.roaming_rule_id, tariffs.id
  • Filter: ((tariffs.plan_id = plans.id) AND (tariffs.country_id = au_mncs.country_id))
  • Rows Removed by Filter: 242
  • Buffers: shared hit=24664
41. 197.312 542.608 ↓ 10.0 30 12,332

Sort (cost=14.51..14.52 rows=3 width=40) (actual time=0.041..0.044 rows=30 loops=12,332)

  • 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.country_id
  • Sort Key: roaming_days.country_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=78940
42. 123.320 345.296 ↓ 10.0 30 12,332

Bitmap Heap Scan on public.roaming_days (cost=4.62..14.49 rows=3 width=40) (actual time=0.022..0.028 rows=30 loops=12,332)

  • 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.country_id
  • Recheck Cond: ((roaming_days.filter_date >= app_usage_period_summaries.start_date) AND (roaming_days.filter_date <= app_usage_period_summaries.end_date) AND (roaming_days.carrier_account_id = app_usage_period_summaries.carrier_account_id))
  • Heap Blocks: exact=15755
  • Buffers: shared hit=78940
43. 221.976 221.976 ↓ 10.0 30 12,332

Bitmap Index Scan on roaming_days_json (cost=0.00..4.62 rows=3 width=0) (actual time=0.018..0.018 rows=30 loops=12,332)

  • Index Cond: ((roaming_days.filter_date >= app_usage_period_summaries.start_date) AND (roaming_days.filter_date <= app_usage_period_summaries.end_date) AND (roaming_days.carrier_account_id = app_usage_period_summaries.carrier_account_id))
  • Buffers: shared hit=63185
44. 35.573 142.292 ↑ 1.0 1 35,573

Aggregate (cost=16.69..16.70 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=35,573)

  • Output: json_agg((json_build_object('filter_date', details.filter_date, 'total_bytes', sum(details.total_bytes))))
  • Buffers: shared hit=24664
45. 0.000 106.719 ↓ 0.0 0 35,573

GroupAggregate (cost=16.64..16.67 rows=1 width=36) (actual time=0.003..0.003 rows=0 loops=35,573)

  • Output: json_build_object('filter_date', details.filter_date, 'total_bytes', sum(details.total_bytes)), details.filter_date
  • Group Key: details.filter_date
  • Buffers: shared hit=24664
46. 35.573 106.719 ↓ 0.0 0 35,573

Sort (cost=16.64..16.65 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=35,573)

  • Output: details.filter_date, details.total_bytes
  • Sort Key: details.filter_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=24664
47. 21.818 71.146 ↓ 0.0 0 35,573

Result (cost=0.57..16.63 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=35,573)

  • Output: details.filter_date, details.total_bytes
  • One-Time Filter: app_usage_period_summaries.is_roaming
  • Buffers: shared hit=24664
48. 12.332 49.328 ↓ 0.0 0 12,332

Nested Loop (cost=0.57..16.63 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=12,332)

  • Output: details.filter_date, details.total_bytes
  • Inner Unique: true
  • Buffers: shared hit=24664
49. 36.996 36.996 ↓ 0.0 0 12,332

Index Scan using index_app_usages_on_network_type_code on public.app_usages details (cost=0.29..8.33 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=12,332)

  • Output: details.id, details.app_id, details.network_id, details.rx_delta, details.tx_delta, details."timestamp", details.updated_at, details.device_id, details.network_name_id, details.roaming, details.rx_delta_roaming, details.tx_delta_roaming, details.pid, details.uid, details.mobile_rx_bytes, details.mobile_tx_bytes, details.last_mobile_rx_bytes, details.last_mobile_tx_bytes, details.total_rx_bytes, details.total_tx_bytes, details.last_total_rx_bytes, details.last_total_tx_bytes, details.uid_rx_bytes, details.uid_tx_bytes, details.last_uid_rx_bytes, details.last_uid_tx_bytes, details.reported_by_pid, details.total_bytes, details.sim_mnc_id, details.tariff_country_id, details.tariff_id, details.app_version, details.comparison_mobile_rx_total, details.comparison_mobile_tx_total, details.comparison_wifi_rx_total, details.comparison_wifi_tx_total, details.mvi_mobile_rx_total, details.mvi_mobile_tx_total, details.mvi_wifi_rx_total, details.mvi_wifi_tx_total, details.comparison_rx_total, details.comparison_tx_total, details.filter_date, details.network_type_code, details.is_roaming, details.carrier_account_id, details.device_usage_id
  • Index Cond: (details.network_type_code = app_usage_period_summaries.network_type_code)
  • Filter: ((details.filter_date >= app_usage_period_summaries.start_date) AND (details.filter_date <= app_usage_period_summaries.end_date) AND (details.tariff_id = app_usage_period_summaries.tariff_id) AND (details.sim_mnc_id = app_usage_period_summaries.sim_mnc_id) AND (details.tariff_country_id = app_usage_period_summaries.tariff_country_id) AND (details.network_name_id = app_usage_period_summaries.network_name_id) AND (details.is_roaming = app_usage_period_summaries.is_roaming) AND (details.carrier_account_id = app_usage_period_summaries.carrier_account_id))
  • Buffers: shared hit=24664
50. 0.000 0.000 ↓ 0.0 0

Index Scan using apps_pkey on public.apps (cost=0.28..8.29 rows=1 width=4) (never executed)

  • Output: apps.id, apps.created_at, apps.updated_at, apps.user_id, apps.category_id, apps.app_detail_id, apps.system, apps.uid
  • Index Cond: (apps.id = details.app_id)
  • Filter: (apps.category_id = app_usage_period_summaries.category_id)
Planning time : 4.931 ms