explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UtDI

Settings
# exclusive inclusive rows x rows loops node
1. 5,912.517 8,113.529 ↓ 24.0 217,966 1

Hash Left Join (cost=6,654.90..23,219.67 rows=9,064 width=2,808) (actual time=39.068..8,113.529 rows=217,966 loops=1)

  • Output: app_usages.filter_date, app_usages.carrier_account_id, app_usages.network_name_id, app_usages.is_roaming, app_usages.device_id, app_detail_parent.id, app_detail_parent.name, app_detail_parent.is_group, app_detail_parent.label, app_parent.category_id, app_parent.id, tariffs.id, app_usages.sim_mnc_id, categories.name, apps.user_id, users.name, users.company_id, devices.name, network_names.name, countries.id, countries.name, carrier_accounts.phone_number, carrier_accounts.name, carrier_accounts.contract_start_date, plans.id, plans.name, plans.rate_type, plans.data_cost_in_cents_per_month, plans.data_in_bytes_per_month, plans.overage_data_mb_rate, tariffs.name, tariffs.roaming_data_mb_rate, period_start(app_usages.filter_date, carrier_accounts.contract_start_date), period_end(app_usages.filter_date, carrier_accounts.contract_start_date), CASE WHEN (free_app_plans.id IS NULL) THEN false ELSE true END, network_names.mnc_id, tariffs.roaming_rule_id
  • Inner Unique: true
  • Hash Cond: (app_usages.tariff_id = tariffs.id)
  • Buffers: shared hit=48463 read=5999
2. 160.751 2,200.538 ↓ 24.0 217,966 1

Hash Left Join (cost=6,633.50..18,642.34 rows=9,064 width=2,279) (actual time=38.438..2,200.538 rows=217,966 loops=1)

  • Output: app_usages.filter_date, app_usages.carrier_account_id, app_usages.network_name_id, app_usages.is_roaming, app_usages.device_id, app_usages.sim_mnc_id, app_usages.tariff_id, network_names.name, network_names.mnc_id, carrier_accounts.phone_number, carrier_accounts.name, carrier_accounts.contract_start_date, devices.name, apps.user_id, users.name, users.company_id, app_detail_parent.id, app_detail_parent.name, app_detail_parent.is_group, app_detail_parent.label, app_parent.category_id, app_parent.id, categories.name, plans.id, plans.name, plans.rate_type, plans.data_cost_in_cents_per_month, plans.data_in_bytes_per_month, plans.overage_data_mb_rate, free_app_plans.id, countries.id, countries.name
  • Inner Unique: true
  • Hash Cond: (app_usages.tariff_country_id = countries.id)
  • Buffers: shared hit=48458 read=5999
3. 143.770 2,039.673 ↓ 24.0 217,966 1

Hash Left Join (cost=6,625.34..18,609.95 rows=9,064 width=2,269) (actual time=38.318..2,039.673 rows=217,966 loops=1)

  • Output: app_usages.filter_date, app_usages.carrier_account_id, app_usages.network_name_id, app_usages.is_roaming, app_usages.device_id, app_usages.sim_mnc_id, app_usages.tariff_country_id, app_usages.tariff_id, network_names.name, network_names.mnc_id, carrier_accounts.phone_number, carrier_accounts.name, carrier_accounts.contract_start_date, devices.name, apps.user_id, users.name, users.company_id, app_detail_parent.id, app_detail_parent.name, app_detail_parent.is_group, app_detail_parent.label, app_parent.category_id, app_parent.id, categories.name, plans.id, plans.name, plans.rate_type, plans.data_cost_in_cents_per_month, plans.data_in_bytes_per_month, plans.overage_data_mb_rate, free_app_plans.id
  • Hash Cond: ((app_details.id = free_app_plans.app_detail_id) AND (plans.id = free_app_plans.plan_id))
  • Buffers: shared hit=48455 read=5999
4. 174.135 1,895.898 ↓ 24.0 217,966 1

Hash Left Join (cost=6,581.34..15,438.56 rows=9,064 width=2,265) (actual time=38.297..1,895.898 rows=217,966 loops=1)

  • Output: app_usages.filter_date, app_usages.carrier_account_id, app_usages.network_name_id, app_usages.is_roaming, app_usages.device_id, app_usages.sim_mnc_id, app_usages.tariff_country_id, app_usages.tariff_id, network_names.name, network_names.mnc_id, carrier_accounts.phone_number, carrier_accounts.name, carrier_accounts.contract_start_date, devices.name, apps.user_id, users.name, users.company_id, app_details.id, app_detail_parent.id, app_detail_parent.name, app_detail_parent.is_group, app_detail_parent.label, app_parent.category_id, app_parent.id, categories.name, plans.id, plans.name, plans.rate_type, plans.data_cost_in_cents_per_month, plans.data_in_bytes_per_month, plans.overage_data_mb_rate
  • Inner Unique: true
  • Hash Cond: (carrier_accounts.plan_id = plans.id)
  • Buffers: shared hit=48455 read=5999
5. 157.978 1,721.736 ↓ 24.0 217,966 1

Hash Join (cost=6,568.64..15,401.57 rows=9,064 width=1,725) (actual time=38.257..1,721.736 rows=217,966 loops=1)

  • Output: app_usages.filter_date, app_usages.carrier_account_id, app_usages.network_name_id, app_usages.is_roaming, app_usages.device_id, app_usages.sim_mnc_id, app_usages.tariff_country_id, app_usages.tariff_id, network_names.name, network_names.mnc_id, carrier_accounts.phone_number, carrier_accounts.name, carrier_accounts.contract_start_date, carrier_accounts.plan_id, devices.name, apps.user_id, users.name, users.company_id, app_details.id, app_detail_parent.id, app_detail_parent.name, app_detail_parent.is_group, app_detail_parent.label, app_parent.category_id, app_parent.id, categories.name
  • Inner Unique: true
  • Hash Cond: (app_parent.category_id = categories.id)
  • Buffers: shared hit=48454 read=5999
6. 177.945 1,563.727 ↓ 24.0 217,966 1

Hash Join (cost=6,555.49..15,363.90 rows=9,064 width=1,209) (actual time=38.172..1,563.727 rows=217,966 loops=1)

  • Output: app_usages.filter_date, app_usages.carrier_account_id, app_usages.network_name_id, app_usages.is_roaming, app_usages.device_id, app_usages.sim_mnc_id, app_usages.tariff_country_id, app_usages.tariff_id, network_names.name, network_names.mnc_id, carrier_accounts.phone_number, carrier_accounts.name, carrier_accounts.contract_start_date, carrier_accounts.plan_id, devices.name, apps.user_id, users.name, users.company_id, app_details.id, app_detail_parent.id, app_detail_parent.name, app_detail_parent.is_group, app_detail_parent.label, app_parent.category_id, app_parent.id
  • Inner Unique: true
  • Hash Cond: ((apps.user_id = app_parent.user_id) AND (app_detail_parent.id = app_parent.app_detail_id))
  • Buffers: shared hit=48453 read=5999
7. 189.667 1,385.122 ↑ 1.0 217,966 1

Hash Join (cost=6,518.12..14,142.12 rows=225,582 width=1,209) (actual time=37.485..1,385.122 rows=217,966 loops=1)

  • Output: app_usages.filter_date, app_usages.carrier_account_id, app_usages.network_name_id, app_usages.is_roaming, app_usages.device_id, app_usages.sim_mnc_id, app_usages.tariff_country_id, app_usages.tariff_id, network_names.name, network_names.mnc_id, carrier_accounts.phone_number, carrier_accounts.name, carrier_accounts.contract_start_date, carrier_accounts.plan_id, devices.name, apps.user_id, users.name, users.company_id, users.id, app_details.id, app_details.parent_id, app_detail_parent.id, app_detail_parent.name, app_detail_parent.is_group, app_detail_parent.label
  • Inner Unique: true
  • Hash Cond: (COALESCE(app_details.parent_id, app_details.id) = app_detail_parent.id)
  • Buffers: shared hit=48443 read=5999
8. 185.836 1,160.645 ↑ 1.0 217,966 1

Hash Join (cost=164.64..7,195.22 rows=225,582 width=1,168) (actual time=2.656..1,160.645 rows=217,966 loops=1)

  • Output: app_usages.filter_date, app_usages.carrier_account_id, app_usages.network_name_id, app_usages.is_roaming, app_usages.device_id, app_usages.sim_mnc_id, app_usages.tariff_country_id, app_usages.tariff_id, network_names.name, network_names.mnc_id, carrier_accounts.phone_number, carrier_accounts.name, carrier_accounts.contract_start_date, carrier_accounts.plan_id, devices.name, apps.user_id, users.name, users.company_id, users.id, app_details.id, app_details.parent_id
  • Inner Unique: true
  • Hash Cond: (apps.app_detail_id = app_details.id)
  • Buffers: shared hit=48133
9. 180.158 973.692 ↑ 1.0 217,966 1

Hash Join (cost=65.99..6,503.16 rows=225,582 width=1,164) (actual time=1.500..973.692 rows=217,966 loops=1)

  • Output: app_usages.filter_date, app_usages.carrier_account_id, app_usages.network_name_id, app_usages.is_roaming, app_usages.device_id, app_usages.sim_mnc_id, app_usages.tariff_country_id, app_usages.tariff_id, network_names.name, network_names.mnc_id, carrier_accounts.phone_number, carrier_accounts.name, carrier_accounts.contract_start_date, carrier_accounts.plan_id, devices.name, apps.user_id, apps.app_detail_id, users.name, users.company_id, users.id
  • Inner Unique: true
  • Hash Cond: (apps.user_id = users.id)
  • Buffers: shared hit=48122
10. 151.439 793.514 ↑ 1.0 217,966 1

Hash Join (cost=64.63..5,752.79 rows=225,582 width=1,145) (actual time=1.460..793.514 rows=217,966 loops=1)

  • Output: app_usages.filter_date, app_usages.carrier_account_id, app_usages.network_name_id, app_usages.is_roaming, app_usages.device_id, app_usages.sim_mnc_id, app_usages.tariff_country_id, app_usages.tariff_id, network_names.name, network_names.mnc_id, carrier_accounts.phone_number, carrier_accounts.name, carrier_accounts.contract_start_date, carrier_accounts.plan_id, devices.name, apps.user_id, apps.app_detail_id
  • Inner Unique: true
  • Hash Cond: (app_usages.app_id = apps.id)
  • Buffers: shared hit=48121
11. 132.656 641.580 ↑ 1.0 221,760 1

Hash Join (cost=30.00..5,123.75 rows=225,582 width=1,141) (actual time=0.150..641.580 rows=221,760 loops=1)

  • Output: app_usages.filter_date, app_usages.carrier_account_id, app_usages.network_name_id, app_usages.is_roaming, app_usages.device_id, app_usages.sim_mnc_id, app_usages.app_id, app_usages.tariff_country_id, app_usages.tariff_id, network_names.name, network_names.mnc_id, carrier_accounts.phone_number, carrier_accounts.name, carrier_accounts.contract_start_date, carrier_accounts.plan_id, devices.name
  • Inner Unique: true
  • Hash Cond: (app_usages.carrier_account_id = carrier_accounts.id)
  • Buffers: shared hit=48111
12. 126.057 508.895 ↑ 1.0 221,760 1

Hash Join (cost=13.70..4,506.33 rows=225,582 width=1,069) (actual time=0.105..508.895 rows=221,760 loops=1)

  • Output: app_usages.filter_date, app_usages.carrier_account_id, app_usages.network_name_id, app_usages.is_roaming, app_usages.device_id, app_usages.sim_mnc_id, app_usages.app_id, app_usages.tariff_country_id, app_usages.tariff_id, network_names.name, network_names.mnc_id, devices.name
  • Inner Unique: true
  • Hash Cond: (app_usages.network_name_id = network_names.id)
  • Buffers: shared hit=48110
13. 159.629 382.786 ↑ 1.0 221,760 1

Nested Loop (cost=0.54..3,883.11 rows=225,582 width=549) (actual time=0.044..382.786 rows=221,760 loops=1)

  • Output: app_usages.filter_date, app_usages.carrier_account_id, app_usages.network_name_id, app_usages.is_roaming, app_usages.device_id, app_usages.sim_mnc_id, app_usages.app_id, app_usages.tariff_country_id, app_usages.tariff_id, devices.name
  • Buffers: shared hit=48109
14. 0.047 0.047 ↓ 30.0 30 1

Index Scan using devices_pkey on public.devices (cost=0.12..8.14 rows=1 width=520) (actual time=0.009..0.047 rows=30 loops=1)

  • Output: devices.id, devices.name, devices.device_id, devices.created_at, devices.updated_at, devices.user_id, devices.brand, devices.device, devices.display, devices.fingerprint, devices.hardware, devices.build_id, devices.model, devices.manufacturer, devices.product, devices.serial, devices.tags, devices.version, devices.version_codes, devices.hardware_id, devices.radio_version, devices.app_version, devices.last_report, devices.paused, devices.android_id, devices.last_ping, devices.desktop_version, devices.last_update_notification, devices.last_desktop_sync, devices.desktop_os_version
  • Buffers: shared hit=3
15. 223.110 223.110 ↑ 1.0 7,392 30

Index Scan using index_app_usages_on_device_id on public.app_usages (cost=0.42..3,799.78 rows=7,519 width=33) (actual time=0.016..7.437 rows=7,392 loops=30)

  • Output: app_usages.id, app_usages.app_id, app_usages.network_id, app_usages.rx_delta, app_usages.tx_delta, app_usages."timestamp", app_usages.updated_at, app_usages.device_id, app_usages.network_name_id, app_usages.roaming, app_usages.rx_delta_roaming, app_usages.tx_delta_roaming, app_usages.pid, app_usages.uid, app_usages.mobile_rx_bytes, app_usages.mobile_tx_bytes, app_usages.last_mobile_rx_bytes, app_usages.last_mobile_tx_bytes, app_usages.total_rx_bytes, app_usages.total_tx_bytes, app_usages.last_total_rx_bytes, app_usages.last_total_tx_bytes, app_usages.uid_rx_bytes, app_usages.uid_tx_bytes, app_usages.last_uid_rx_bytes, app_usages.last_uid_tx_bytes, app_usages.reported_by_pid, app_usages.total_bytes, app_usages.sim_mnc_id, app_usages.tariff_country_id, app_usages.tariff_id, app_usages.app_version, app_usages.comparison_mobile_rx_total, app_usages.comparison_mobile_tx_total, app_usages.comparison_wifi_rx_total, app_usages.comparison_wifi_tx_total, app_usages.mvi_mobile_rx_total, app_usages.mvi_mobile_tx_total, app_usages.mvi_wifi_rx_total, app_usages.mvi_wifi_tx_total, app_usages.comparison_rx_total, app_usages.comparison_tx_total, app_usages.filter_date, app_usages.network_type_code, app_usages.is_roaming, app_usages.carrier_account_id, app_usages.device_usage_id
  • Index Cond: (app_usages.device_id = devices.id)
  • Filter: ((app_usages.id >= 1) AND (app_usages.id <= 742530))
  • Buffers: shared hit=48106
16. 0.011 0.052 ↑ 4.7 30 1

Hash (cost=11.40..11.40 rows=140 width=524) (actual time=0.052..0.052 rows=30 loops=1)

  • Output: network_names.name, network_names.mnc_id, network_names.id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
17. 0.041 0.041 ↑ 4.7 30 1

Seq Scan on public.network_names (cost=0.00..11.40 rows=140 width=524) (actual time=0.034..0.041 rows=30 loops=1)

  • Output: network_names.name, network_names.mnc_id, network_names.id
  • Buffers: shared hit=1
18. 0.010 0.029 ↑ 9.3 30 1

Hash (cost=12.80..12.80 rows=280 width=76) (actual time=0.028..0.029 rows=30 loops=1)

  • Output: carrier_accounts.phone_number, carrier_accounts.name, carrier_accounts.contract_start_date, carrier_accounts.id, carrier_accounts.plan_id
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
19. 0.019 0.019 ↑ 9.3 30 1

Seq Scan on public.carrier_accounts (cost=0.00..12.80 rows=280 width=76) (actual time=0.009..0.019 rows=30 loops=1)

  • Output: carrier_accounts.phone_number, carrier_accounts.name, carrier_accounts.contract_start_date, carrier_accounts.id, carrier_accounts.plan_id
  • Buffers: shared hit=1
20. 0.250 0.495 ↑ 1.0 1,095 1

Hash (cost=20.95..20.95 rows=1,095 width=12) (actual time=0.495..0.495 rows=1,095 loops=1)

  • Output: apps.user_id, apps.id, apps.app_detail_id
  • Buckets: 2048 Batches: 1 Memory Usage: 64kB
  • Buffers: shared hit=10
21. 0.245 0.245 ↑ 1.0 1,095 1

Seq Scan on public.apps (cost=0.00..20.95 rows=1,095 width=12) (actual time=0.009..0.245 rows=1,095 loops=1)

  • Output: apps.user_id, apps.id, apps.app_detail_id
  • Buffers: shared hit=10
22. 0.007 0.020 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=19) (actual time=0.019..0.020 rows=16 loops=1)

  • Output: users.name, users.company_id, users.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
23. 0.013 0.013 ↑ 1.0 16 1

Seq Scan on public.users (cost=0.00..1.16 rows=16 width=19) (actual time=0.009..0.013 rows=16 loops=1)

  • Output: users.name, users.company_id, users.id
  • Buffers: shared hit=1
24. 0.606 1.117 ↑ 1.0 1,977 1

Hash (cost=73.93..73.93 rows=1,977 width=8) (actual time=1.117..1.117 rows=1,977 loops=1)

  • Output: app_details.id, app_details.parent_id
  • Buckets: 2048 Batches: 1 Memory Usage: 86kB
  • Buffers: shared hit=11
25. 0.511 0.511 ↑ 1.0 1,977 1

Index Only Scan using idx_app_detail_parent_id_id on public.app_details (cost=0.28..73.93 rows=1,977 width=8) (actual time=0.045..0.511 rows=1,977 loops=1)

  • Output: app_details.id, app_details.parent_id
  • Heap Fetches: 24
  • Buffers: shared hit=11
26. 0.771 34.810 ↑ 1.0 1,977 1

Hash (cost=6,328.77..6,328.77 rows=1,977 width=41) (actual time=34.810..34.810 rows=1,977 loops=1)

  • Output: app_detail_parent.id, app_detail_parent.name, app_detail_parent.is_group, app_detail_parent.label
  • Buckets: 2048 Batches: 1 Memory Usage: 159kB
  • Buffers: shared hit=310 read=5999
27. 34.039 34.039 ↑ 1.0 1,977 1

Seq Scan on public.app_details app_detail_parent (cost=0.00..6,328.77 rows=1,977 width=41) (actual time=0.013..34.039 rows=1,977 loops=1)

  • Output: app_detail_parent.id, app_detail_parent.name, app_detail_parent.is_group, app_detail_parent.label
  • Buffers: shared hit=310 read=5999
28. 0.355 0.660 ↑ 1.0 1,095 1

Hash (cost=20.95..20.95 rows=1,095 width=16) (actual time=0.660..0.660 rows=1,095 loops=1)

  • Output: app_parent.category_id, app_parent.id, app_parent.user_id, app_parent.app_detail_id
  • Buckets: 2048 Batches: 1 Memory Usage: 68kB
  • Buffers: shared hit=10
29. 0.305 0.305 ↑ 1.0 1,095 1

Seq Scan on public.apps app_parent (cost=0.00..20.95 rows=1,095 width=16) (actual time=0.011..0.305 rows=1,095 loops=1)

  • Output: app_parent.category_id, app_parent.id, app_parent.user_id, app_parent.app_detail_id
  • Buffers: shared hit=10
30. 0.006 0.031 ↑ 23.3 6 1

Hash (cost=11.40..11.40 rows=140 width=520) (actual time=0.031..0.031 rows=6 loops=1)

  • Output: categories.name, categories.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
31. 0.025 0.025 ↑ 23.3 6 1

Seq Scan on public.categories (cost=0.00..11.40 rows=140 width=520) (actual time=0.023..0.025 rows=6 loops=1)

  • Output: categories.name, categories.id
  • Buffers: shared hit=1
32. 0.016 0.027 ↑ 40.0 3 1

Hash (cost=11.20..11.20 rows=120 width=544) (actual time=0.027..0.027 rows=3 loops=1)

  • Output: plans.id, plans.name, plans.rate_type, plans.data_cost_in_cents_per_month, plans.data_in_bytes_per_month, plans.overage_data_mb_rate
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
33. 0.011 0.011 ↑ 40.0 3 1

Seq Scan on public.plans (cost=0.00..11.20 rows=120 width=544) (actual time=0.009..0.011 rows=3 loops=1)

  • Output: plans.id, plans.name, plans.rate_type, plans.data_cost_in_cents_per_month, plans.data_in_bytes_per_month, plans.overage_data_mb_rate
  • Buffers: shared hit=1
34. 0.001 0.005 ↓ 0.0 0 1

Hash (cost=23.60..23.60 rows=1,360 width=24) (actual time=0.005..0.005 rows=0 loops=1)

  • Output: free_app_plans.id, free_app_plans.app_detail_id, free_app_plans.plan_id
  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
35. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on public.free_app_plans (cost=0.00..23.60 rows=1,360 width=24) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: free_app_plans.id, free_app_plans.app_detail_id, free_app_plans.plan_id
36. 0.068 0.114 ↓ 1.1 243 1

Hash (cost=5.29..5.29 rows=229 width=14) (actual time=0.114..0.114 rows=243 loops=1)

  • Output: countries.id, countries.name
  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
  • Buffers: shared hit=3
37. 0.046 0.046 ↓ 1.1 243 1

Seq Scan on public.countries (cost=0.00..5.29 rows=229 width=14) (actual time=0.008..0.046 rows=243 loops=1)

  • Output: countries.id, countries.name
  • Buffers: shared hit=3
38. 0.236 0.474 ↑ 1.0 729 1

Hash (cost=12.29..12.29 rows=729 width=532) (actual time=0.473..0.474 rows=729 loops=1)

  • Output: tariffs.id, tariffs.name, tariffs.roaming_data_mb_rate, tariffs.roaming_rule_id
  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
  • Buffers: shared hit=5
39. 0.238 0.238 ↑ 1.0 729 1

Seq Scan on public.tariffs (cost=0.00..12.29 rows=729 width=532) (actual time=0.008..0.238 rows=729 loops=1)

  • Output: tariffs.id, tariffs.name, tariffs.roaming_data_mb_rate, tariffs.roaming_rule_id
  • Buffers: shared hit=5
Planning time : 14.760 ms