explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CcXS

Settings
# exclusive inclusive rows x rows loops node
1. 58.459 3,464.604 ↓ 1.5 3 1

GroupAggregate (cost=39,088.80..39,089.02 rows=2 width=121) (actual time=3,441.616..3,464.604 rows=3 loops=1)

  • Output: app_usage_period_summaries_with_total_roaming_country_usage.carrier_account_id, app_usage_period_summaries_with_total_roaming_country_usage.end_date, app_usage_period_summaries_with_total_roaming_country_usage.network_type_code, app_usage_period_summaries_with_total_roaming_country_usage.network_name_id, app_usage_period_summaries_with_total_roaming_country_usage.is_roaming, app_usage_period_summaries_with_total_roaming_country_usage.sim_mnc_id, mncs.id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_country_id, app_usage_period_summaries_with_total_roaming_country_usage.total_billing_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_roaming_country_usage, sum(app_usage_period_summaries_with_total_roaming_country_usage.total_bytes), sum_roaming_days(app_usage_period_summaries_with_total_roaming_country_usage.roaming_days), sum_app_usage_roaming_details(app_usage_period_summaries_with_total_roaming_country_usage.roaming_details)
  • Group Key: app_usage_period_summaries_with_total_roaming_country_usage.carrier_account_id, app_usage_period_summaries_with_total_roaming_country_usage.end_date, app_usage_period_summaries_with_total_roaming_country_usage.network_type_code, app_usage_period_summaries_with_total_roaming_country_usage.network_name_id, app_usage_period_summaries_with_total_roaming_country_usage.is_roaming, app_usage_period_summaries_with_total_roaming_country_usage.sim_mnc_id, mncs.id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_country_id, app_usage_period_summaries_with_total_roaming_country_usage.total_billing_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_roaming_country_usage
  • Buffers: shared hit=3760379
2. 0.181 3,406.145 ↓ 42.0 84 1

Sort (cost=39,088.80..39,088.80 rows=2 width=121) (actual time=3,406.123..3,406.145 rows=84 loops=1)

  • Output: app_usage_period_summaries_with_total_roaming_country_usage.carrier_account_id, app_usage_period_summaries_with_total_roaming_country_usage.end_date, app_usage_period_summaries_with_total_roaming_country_usage.network_type_code, app_usage_period_summaries_with_total_roaming_country_usage.network_name_id, app_usage_period_summaries_with_total_roaming_country_usage.is_roaming, app_usage_period_summaries_with_total_roaming_country_usage.sim_mnc_id, mncs.id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_country_id, app_usage_period_summaries_with_total_roaming_country_usage.total_billing_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_roaming_country_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_bytes, app_usage_period_summaries_with_total_roaming_country_usage.roaming_days, app_usage_period_summaries_with_total_roaming_country_usage.roaming_details
  • Sort Key: app_usage_period_summaries_with_total_roaming_country_usage.carrier_account_id, app_usage_period_summaries_with_total_roaming_country_usage.end_date, app_usage_period_summaries_with_total_roaming_country_usage.network_name_id, app_usage_period_summaries_with_total_roaming_country_usage.is_roaming, app_usage_period_summaries_with_total_roaming_country_usage.sim_mnc_id, mncs.id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_country_id, app_usage_period_summaries_with_total_roaming_country_usage.total_billing_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_roaming_country_usage
  • Sort Method: quicksort Memory: 871kB
  • Buffers: shared hit=3760379
3. 0.020 3,405.964 ↓ 42.0 84 1

Merge Join (cost=676.55..39,088.80 rows=2 width=121) (actual time=3,405.436..3,405.964 rows=84 loops=1)

  • Output: app_usage_period_summaries_with_total_roaming_country_usage.carrier_account_id, app_usage_period_summaries_with_total_roaming_country_usage.end_date, app_usage_period_summaries_with_total_roaming_country_usage.network_type_code, app_usage_period_summaries_with_total_roaming_country_usage.network_name_id, app_usage_period_summaries_with_total_roaming_country_usage.is_roaming, app_usage_period_summaries_with_total_roaming_country_usage.sim_mnc_id, mncs.id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_country_id, app_usage_period_summaries_with_total_roaming_country_usage.total_billing_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_roaming_country_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_bytes, app_usage_period_summaries_with_total_roaming_country_usage.roaming_days, app_usage_period_summaries_with_total_roaming_country_usage.roaming_details
  • Inner Unique: true
  • Merge Cond: (carrier_accounts.user_id = users.id)
  • Buffers: shared hit=3760379
4. 0.817 3,405.919 ↓ 1.8 84 1

Nested Loop (cost=673.28..39,939.10 rows=46 width=125) (actual time=3,405.408..3,405.919 rows=84 loops=1)

  • Output: app_usage_period_summaries_with_total_roaming_country_usage.carrier_account_id, app_usage_period_summaries_with_total_roaming_country_usage.end_date, app_usage_period_summaries_with_total_roaming_country_usage.network_type_code, app_usage_period_summaries_with_total_roaming_country_usage.network_name_id, app_usage_period_summaries_with_total_roaming_country_usage.is_roaming, app_usage_period_summaries_with_total_roaming_country_usage.sim_mnc_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_country_id, app_usage_period_summaries_with_total_roaming_country_usage.total_billing_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_roaming_country_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_bytes, app_usage_period_summaries_with_total_roaming_country_usage.roaming_days, app_usage_period_summaries_with_total_roaming_country_usage.roaming_details, mncs.id, carrier_accounts.user_id
  • Join Filter: (app_usage_period_summaries_with_total_roaming_country_usage.carrier_account_id = carrier_accounts.id)
  • Rows Removed by Join Filter: 9744
  • Buffers: shared hit=3760376
5. 0.051 0.051 ↑ 1.0 117 1

Index Scan using index_carrier_accounts_on_user_id on public.carrier_accounts (cost=0.03..20.47 rows=117 width=8) (actual time=0.006..0.051 rows=117 loops=1)

  • Output: carrier_accounts.id, carrier_accounts.user_id, carrier_accounts.device_id, carrier_accounts.plan_id, carrier_accounts.phone_number, carrier_accounts.sim_country, carrier_accounts.sim_carrier, carrier_accounts.sim_carrier_id, carrier_accounts.sim_serial_number, carrier_accounts.sim_state, carrier_accounts.carrier_id, carrier_accounts.contract_start_date, carrier_accounts.monitor, carrier_accounts.time_zone, carrier_accounts.last_report, carrier_accounts.name
  • Buffers: shared hit=37
6. 0.611 3,405.051 ↓ 1.8 84 117

Materialize (cost=673.25..39,897.13 rows=46 width=121) (actual time=29.059..29.103 rows=84 loops=117)

  • Output: app_usage_period_summaries_with_total_roaming_country_usage.carrier_account_id, app_usage_period_summaries_with_total_roaming_country_usage.end_date, app_usage_period_summaries_with_total_roaming_country_usage.network_type_code, app_usage_period_summaries_with_total_roaming_country_usage.network_name_id, app_usage_period_summaries_with_total_roaming_country_usage.is_roaming, app_usage_period_summaries_with_total_roaming_country_usage.sim_mnc_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_country_id, app_usage_period_summaries_with_total_roaming_country_usage.total_billing_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_roaming_country_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_bytes, app_usage_period_summaries_with_total_roaming_country_usage.roaming_days, app_usage_period_summaries_with_total_roaming_country_usage.roaming_details, mncs.id
  • Buffers: shared hit=3760339
7. 0.036 3,404.440 ↓ 1.8 84 1

Merge Join (cost=673.25..39,897.08 rows=46 width=121) (actual time=3,399.842..3,404.440 rows=84 loops=1)

  • Output: app_usage_period_summaries_with_total_roaming_country_usage.carrier_account_id, app_usage_period_summaries_with_total_roaming_country_usage.end_date, app_usage_period_summaries_with_total_roaming_country_usage.network_type_code, app_usage_period_summaries_with_total_roaming_country_usage.network_name_id, app_usage_period_summaries_with_total_roaming_country_usage.is_roaming, app_usage_period_summaries_with_total_roaming_country_usage.sim_mnc_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_country_id, app_usage_period_summaries_with_total_roaming_country_usage.total_billing_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_roaming_country_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_bytes, app_usage_period_summaries_with_total_roaming_country_usage.roaming_days, app_usage_period_summaries_with_total_roaming_country_usage.roaming_details, mncs.id
  • Inner Unique: true
  • Merge Cond: (network_names.mnc_id = mncs.id)
  • Buffers: shared hit=3760339
8. 3.056 3,404.353 ↓ 1.8 84 1

Nested Loop (cost=670.55..90,795.47 rows=46 width=121) (actual time=3,399.778..3,404.353 rows=84 loops=1)

  • Output: app_usage_period_summaries_with_total_roaming_country_usage.carrier_account_id, app_usage_period_summaries_with_total_roaming_country_usage.end_date, app_usage_period_summaries_with_total_roaming_country_usage.network_type_code, app_usage_period_summaries_with_total_roaming_country_usage.network_name_id, app_usage_period_summaries_with_total_roaming_country_usage.is_roaming, app_usage_period_summaries_with_total_roaming_country_usage.sim_mnc_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_country_id, app_usage_period_summaries_with_total_roaming_country_usage.total_billing_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_roaming_country_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_bytes, app_usage_period_summaries_with_total_roaming_country_usage.roaming_days, app_usage_period_summaries_with_total_roaming_country_usage.roaming_details, network_names.mnc_id
  • Join Filter: (app_usage_period_summaries_with_total_roaming_country_usage.network_name_id = network_names.id)
  • Rows Removed by Join Filter: 33936
  • Buffers: shared hit=3760335
9. 0.107 0.107 ↑ 1.0 405 1

Index Scan using index_network_names_on_mnc_id on public.network_names (cost=0.05..17.67 rows=405 width=8) (actual time=0.007..0.107 rows=405 loops=1)

  • Output: network_names.id, network_names.user_id, network_names.name, network_names.created_at, network_names.updated_at, network_names.network_type_id, network_names.mnc_id
  • Buffers: shared hit=49
10. 2.159 3,401.190 ↓ 1.8 84 405

Materialize (cost=670.50..90,703.31 rows=46 width=117) (actual time=0.010..8.398 rows=84 loops=405)

  • Output: app_usage_period_summaries_with_total_roaming_country_usage.carrier_account_id, app_usage_period_summaries_with_total_roaming_country_usage.end_date, app_usage_period_summaries_with_total_roaming_country_usage.network_type_code, app_usage_period_summaries_with_total_roaming_country_usage.network_name_id, app_usage_period_summaries_with_total_roaming_country_usage.is_roaming, app_usage_period_summaries_with_total_roaming_country_usage.sim_mnc_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_country_id, app_usage_period_summaries_with_total_roaming_country_usage.total_billing_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_roaming_country_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_bytes, app_usage_period_summaries_with_total_roaming_country_usage.roaming_days, app_usage_period_summaries_with_total_roaming_country_usage.roaming_details
  • Buffers: shared hit=3760286
11. 0.143 3,399.031 ↓ 1.8 84 1

Subquery Scan on app_usage_period_summaries_with_total_roaming_country_usage (cost=670.50..90,703.26 rows=46 width=117) (actual time=4.209..3,399.031 rows=84 loops=1)

  • Output: app_usage_period_summaries_with_total_roaming_country_usage.carrier_account_id, app_usage_period_summaries_with_total_roaming_country_usage.end_date, app_usage_period_summaries_with_total_roaming_country_usage.network_type_code, app_usage_period_summaries_with_total_roaming_country_usage.network_name_id, app_usage_period_summaries_with_total_roaming_country_usage.is_roaming, app_usage_period_summaries_with_total_roaming_country_usage.sim_mnc_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_id, app_usage_period_summaries_with_total_roaming_country_usage.tariff_country_id, app_usage_period_summaries_with_total_roaming_country_usage.total_billing_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_roaming_country_usage, app_usage_period_summaries_with_total_roaming_country_usage.total_bytes, app_usage_period_summaries_with_total_roaming_country_usage.roaming_days, app_usage_period_summaries_with_total_roaming_country_usage.roaming_details
  • Filter: ((app_usage_period_summaries_with_total_roaming_country_usage.network_type_code = 1) AND (app_usage_period_summaries_with_total_roaming_country_usage.category_id = 199))
  • Rows Removed by Filter: 342
  • Buffers: shared hit=3760286
12. 1.234 3,398.888 ↑ 1.1 426 1

WindowAgg (cost=670.50..90,701.33 rows=483 width=137) (actual time=2.032..3,398.888 rows=426 loops=1)

  • Output: NULL::bigint, NULL::integer, 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=3760286
13. 0.124 2.008 ↑ 1.1 426 1

Sort (cost=670.50..670.74 rows=483 width=57) (actual time=1.985..2.008 rows=426 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.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.carrier_account_id, app_usage_period_summaries.sim_mnc_id, app_usage_period_summaries.end_date
  • Sort Method: quicksort Memory: 84kB
  • Buffers: shared hit=493
14. 0.249 1.884 ↑ 1.1 426 1

WindowAgg (cost=663.29..666.19 rows=483 width=57) (actual time=1.656..1.884 rows=426 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.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=493
15. 0.172 1.635 ↑ 1.1 426 1

Sort (cost=663.29..663.53 rows=483 width=49) (actual time=1.612..1.635 rows=426 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.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.carrier_account_id, app_usage_period_summaries.is_roaming, app_usage_period_summaries.start_date, app_usage_period_summaries.end_date
  • Sort Method: quicksort Memory: 84kB
  • Buffers: shared hit=493
16. 0.283 1.463 ↑ 1.1 426 1

Hash Join (cost=33.55..658.99 rows=483 width=49) (actual time=0.676..1.463 rows=426 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.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=493
17. 0.565 0.648 ↑ 1.2 426 1

Bitmap Heap Scan on public.mv_app_usage_period_summaries app_usage_period_summaries (cost=5.06..630.24 rows=490 width=45) (actual time=0.138..0.648 rows=426 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))
  • Heap Blocks: exact=473
  • Buffers: shared hit=477
18. 0.083 0.083 ↓ 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.083..0.083 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
19. 0.271 0.532 ↑ 1.0 1,921 1

Hash (cost=21.76..21.76 rows=1,921 width=8) (actual time=0.532..0.532 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
20. 0.261 0.261 ↑ 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.007..0.261 rows=1,921 loops=1)

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

SubPlan (forWindowAgg)

22. 17.466 20.874 ↑ 1.0 1 426

Aggregate (cost=4.06..4.07 rows=1 width=32) (actual time=0.048..0.049 rows=1 loops=426)

  • 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=2160
23. 0.888 3.408 ↓ 8.0 8 426

Result (cost=0.06..4.06 rows=1 width=36) (actual time=0.003..0.008 rows=8 loops=426)

  • 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=2160
24. 2.520 2.520 ↓ 28.0 28 120

Index Scan using roaming_days_uniqueness_constraint on public.roaming_days (cost=0.06..4.06 rows=1 width=36) (actual time=0.008..0.021 rows=28 loops=120)

  • 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=2160
25. 1.278 3,374.772 ↑ 1.0 1 426

Aggregate (cost=182.32..182.33 rows=1 width=32) (actual time=7.922..7.922 rows=1 loops=426)

  • Output: json_agg((json_build_object('filter_date', details.filter_date, 'total_bytes', sum(details.total_bytes))))
  • Buffers: shared hit=3757633
26. 76.680 3,373.494 ↓ 5.0 5 426

GroupAggregate (cost=182.31..182.32 rows=1 width=36) (actual time=7.689..7.919 rows=5 loops=426)

  • 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=3757633
27. 149.952 3,296.814 ↓ 1,471.0 1,471 426

Sort (cost=182.31..182.31 rows=1 width=12) (actual time=7.661..7.739 rows=1,471 loops=426)

  • Output: details.filter_date, details.total_bytes
  • Sort Key: details.filter_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3757633
28. 77.502 3,146.862 ↓ 1,471.0 1,471 426

Result (cost=110.99..182.31 rows=1 width=12) (actual time=1.435..7.387 rows=1,471 loops=426)

  • Output: details.filter_date, details.total_bytes
  • One-Time Filter: app_usage_period_summaries.is_roaming
  • Buffers: shared hit=3757633
29. 646.666 3,069.360 ↓ 5,221.0 5,221 120

Nested Loop (cost=110.99..182.31 rows=1 width=12) (actual time=5.094..25.578 rows=5,221 loops=120)

  • Output: details.filter_date, details.total_bytes
  • Inner Unique: true
  • Buffers: shared hit=3757633
30. 588.960 1,193.400 ↓ 10,244.0 10,244 120

Bitmap Heap Scan on public.app_usages details (cost=110.94..178.25 rows=1 width=16) (actual time=5.083..9.945 rows=10,244 loops=120)

  • 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
  • Recheck Cond: ((details.network_name_id = app_usage_period_summaries.network_name_id) AND (details.filter_date >= app_usage_period_summaries.start_date) AND (details.filter_date <= app_usage_period_summaries.end_date))
  • Filter: ((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_type_code = app_usage_period_summaries.network_type_code) AND (details.is_roaming = app_usage_period_summaries.is_roaming) AND (details.carrier_account_id = app_usage_period_summaries.carrier_account_id))
  • Heap Blocks: exact=33720
  • Buffers: shared hit=69751
31. 19.800 604.440 ↓ 0.0 0 120

BitmapAnd (cost=110.94..110.94 rows=34 width=0) (actual time=5.037..5.037 rows=0 loops=120)

  • Buffers: shared hit=36031
32. 400.320 400.320 ↓ 11.1 75,930 120

Bitmap Index Scan on index_app_usages_on_network_name_id (cost=0.00..48.36 rows=6,850 width=0) (actual time=3.336..3.336 rows=75,930 loops=120)

  • Index Cond: (details.network_name_id = app_usage_period_summaries.network_name_id)
  • Buffers: shared hit=25471
33. 184.320 184.320 ↓ 3.8 31,283 120

Bitmap Index Scan on index_app_usages_on_filter_date (cost=0.00..62.53 rows=8,220 width=0) (actual time=1.536..1.536 rows=31,283 loops=120)

  • Index Cond: ((details.filter_date >= app_usage_period_summaries.start_date) AND (details.filter_date <= app_usage_period_summaries.end_date))
  • Buffers: shared hit=10560
34. 1,229.294 1,229.294 ↑ 1.0 1 1,229,294

Index Scan using apps_pkey on public.apps (cost=0.06..4.06 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,229,294)

  • 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)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=3687882
35. 0.051 0.051 ↑ 9.1 212 1

Index Only Scan using mncs_pkey on public.mncs (cost=0.06..40.75 rows=1,921 width=4) (actual time=0.017..0.051 rows=212 loops=1)

  • Output: mncs.id
  • Heap Fetches: 212
  • Buffers: shared hit=4
36. 0.006 0.025 ↑ 1.5 2 1

Sort (cost=3.27..3.27 rows=3 width=4) (actual time=0.024..0.025 rows=2 loops=1)

  • Output: users.id
  • Sort Key: users.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3
37. 0.019 0.019 ↑ 1.0 3 1

Seq Scan on public.users (cost=0.00..3.26 rows=3 width=4) (actual time=0.009..0.019 rows=3 loops=1)

  • Output: users.id
  • Filter: (users.company_id = 95)
  • Rows Removed by Filter: 72
  • Buffers: shared hit=3
Planning time : 1.564 ms
Execution time : 3,464.892 ms