explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XOJz

Settings
# exclusive inclusive rows x rows loops node
1. 2.324 1,870.611 ↑ 31.2 111 1

Subquery Scan on phone_calls_with_total_roaming_country_usage (cost=173,672.44..219,013.50 rows=3,461 width=279) (actual time=1,759.711..1,870.611 rows=111 loops=1)

  • Filter: ((phone_calls_with_total_roaming_country_usage.filter_date >= '2018-03-01'::date) AND (phone_calls_with_total_roaming_country_usage.filter_date <= '2018-03-31'::date))
  • Rows Removed by Filter: 8471
2. 149.822 1,868.287 ↑ 80.7 8,582 1

WindowAgg (cost=173,672.44..216,244.58 rows=692,230 width=191) (actual time=1,716.857..1,868.287 rows=8,582 loops=1)

  • Sort Key: phone_calls.carrier_account_id, (period_start(phone_calls.filter_date, carrier_accounts.contract_start_date))
  • Sort Method: quicksort Memory: 3997kB
3. 35.758 1,718.465 ↑ 80.7 8,582 1

Sort (cost=173,672.44..174,018.55 rows=692,230 width=191) (actual time=1,716.748..1,718.465 rows=8,582 loops=1)

  • Group Key: phone_calls.id, phone_calls.started, phone_calls.outgoing, phone_calls.duration_in_seconds, phone_calls.category_id, phone_calls.created_at, phone_calls.updated_at, phone_calls.contact_id, phone_calls.phone_number, phone_calls.device_id, phone_calls.roaming, phone_calls.country_id, phone_calls.country_code_id, phone_calls.internationalized_number, phone_calls.duration_in_minutes, phone_calls.tariff_country_id, phone_calls.mnc_id, phone_calls.sim_mnc_id, phone_calls.tariff_id, phone_calls.app_version, phone_calls.toll_free, phone_calls.filter_date, phone_calls.contact_phone_number_id, phone_calls.reverse_digits, phone_calls.extension, phone_calls.is_long_distance, phone_calls.is_roaming, phone_calls.carrier_account_id, phone_calls.activity_id, plans.rate_type, carrier_accounts.contract_start_date
4. 554.965 1,682.707 ↑ 80.7 8,582 1

GroupAggregate (cost=62,346.69..114,610.06 rows=692,230 width=191) (actual time=1,124.983..1,682.707 rows=8,582 loops=1)

  • Sort Key: phone_calls.id, phone_calls.started, phone_calls.outgoing, phone_calls.duration_in_seconds, phone_calls.category_id, phone_calls.created_at, phone_calls.updated_at, phone_calls.contact_id, phone_calls.phone_number, phone_calls.device_id, phone_calls.roaming, phone_calls.country_id, phone_calls.country_code_id, phone_calls.internationalized_number, phone_calls.duration_in_minutes, phone_calls.tariff_country_id, phone_calls.mnc_id, phone_calls.sim_mnc_id, phone_calls.tariff_id, phone_calls.app_version, phone_calls.toll_free, phone_calls.filter_date, phone_calls.contact_phone_number_id, phone_calls.reverse_digits, phone_calls.extension, phone_calls.is_long_distance, phone_calls.is_roaming, phone_calls.carrier_account_id, phone_calls.activity_id, plans.rate_type, carrier_accounts.contract_start_date
  • Sort Method: quicksort Memory: 2664kB
5. 12.451 1,127.742 ↑ 80.7 8,582 1

Sort (cost=62,346.69..62,692.81 rows=692,230 width=191) (actual time=1,124.814..1,127.742 rows=8,582 loops=1)

  • Merge Cond: (carrier_accounts.plan_id = plans.id)
6. 4.908 1,115.291 ↑ 80.7 8,582 1

Merge Join (cost=1,964.35..3,284.31 rows=692,230 width=191) (actual time=46.878..1,115.291 rows=8,582 loops=1)

  • Sort Key: plans.id
  • Sort Method: quicksort Memory: 30kB
7. 8.188 1,108.971 ↑ 10.3 8,582 1

Nested Loop Left Join (cost=1,960.28..10,055.57 rows=88,267 width=198) (actual time=46.814..1,108.971 rows=8,582 loops=1)

  • Merge Cond: (roaming_tariffs.plan_id = carrier_accounts.plan_id)
  • 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: 2076469
  • Index Cond: ((carrier_account_id = phone_calls.carrier_account_id) AND (country_id = roaming_tariffs.country_id) AND (filter_date = phone_calls.filter_date))
8. 820.202 1,075.037 ↑ 10.3 8,582 1

Merge Join (cost=1,960.23..3,287.03 rows=88,267 width=170) (actual time=46.799..1,075.037 rows=8,582 loops=1)

  • Sort Key: carrier_accounts.plan_id
  • Sort Method: quicksort Memory: 2664kB
9. 6.008 6.008 ↓ 1.0 26,241 1

Index Scan using index_tariffs_on_plan_id on tariffs roaming_tariffs (cost=0.06..580.81 rows=26,240 width=16) (actual time=0.009..6.008 rows=26,241 loops=1)

10. 209.845 248.827 ↓ 489.7 2,084,809 1

Sort (cost=1,960.17..1,962.30 rows=4,257 width=170) (actual time=46.182..248.827 rows=2,084,809 loops=1)

  • Hash Cond: (phone_calls.sim_mnc_id = pc_sim_mncs.id)
11. 3.245 38.982 ↓ 2.0 8,582 1

Hash Join (cost=817.83..1,908.85 rows=4,257 width=170) (actual time=8.283..38.982 rows=8,582 loops=1)

  • Hash Cond: ((phone_calls.carrier_account_id = carrier_accounts.id) AND (pc_mncs.country_id = tariffs.country_id))
  • Buckets: 2048 Batches: 1 Memory Usage: 84kB
12. 6.770 35.230 ↓ 2.0 8,582 1

Hash Join (cost=619.37..1,694.42 rows=4,269 width=170) (actual time=7.769..35.230 rows=8,582 loops=1)

  • Hash Cond: (phone_calls.mnc_id = pc_mncs.id)
  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 423kB
13. 17.176 21.375 ↑ 1.0 25,508 1

Hash Join (cost=227.48..1,085.66 rows=25,508 width=154) (actual time=0.671..21.375 rows=25,508 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 91kB
14. 3.543 3.543 ↑ 1.0 25,508 1

Seq Scan on phone_calls (cost=0.00..762.52 rows=25,508 width=150) (actual time=0.003..3.543 rows=25,508 loops=1)

15. 0.231 0.656 ↓ 1.0 1,921 1

Hash (cost=220.76..220.76 rows=1,920 width=8) (actual time=0.656..0.656 rows=1,921 loops=1)

16. 0.425 0.425 ↓ 1.0 1,921 1

Seq Scan on mncs pc_mncs (cost=0.00..220.76 rows=1,920 width=8) (actual time=0.003..0.425 rows=1,921 loops=1)

17. 1.322 7.085 ↓ 1.9 6,552 1

Hash (cost=378.29..378.29 rows=3,400 width=28) (actual time=7.085..7.085 rows=6,552 loops=1)

  • Hash Cond: (tariffs.plan_id = carrier_accounts.plan_id)
18. 3.953 5.763 ↓ 1.9 6,552 1

Hash Join (cost=12.53..378.29 rows=3,400 width=28) (actual time=0.057..5.763 rows=6,552 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 1.761 1.761 ↓ 1.0 26,726 1

Seq Scan on tariffs (cost=0.00..263.72 rows=26,240 width=16) (actual time=0.003..1.761 rows=26,726 loops=1)

20. 0.008 0.049 ↑ 3.0 27 1

Hash (cost=12.25..12.25 rows=82 width=12) (actual time=0.049..0.049 rows=27 loops=1)

21. 0.041 0.041 ↓ 1.4 116 1

Seq Scan on carrier_accounts (cost=0.00..12.25 rows=82 width=12) (actual time=0.002..0.041 rows=116 loops=1)

22. 0.257 0.507 ↓ 1.0 1,921 1

Hash (cost=191.74..191.74 rows=1,920 width=4) (actual time=0.507..0.507 rows=1,921 loops=1)

  • Heap Fetches: 115
23. 0.250 0.250 ↓ 1.0 1,921 1

Index Only Scan using mncs_pkey on mncs pc_sim_mncs (cost=0.06..191.74 rows=1,920 width=4) (actual time=0.011..0.250 rows=1,921 loops=1)

24. 25.746 25.746 ↑ 1.0 1 8,582

Index Scan using roaming_days_uniqueness_constraint on roaming_days (cost=0.06..0.07 rows=1 width=44) (actual time=0.003..0.003 rows=1 loops=8,582)

25. 1.383 1.412 ↓ 79.6 8,681 1

Sort (cost=4.06..4.12 rows=109 width=9) (actual time=0.060..1.412 rows=8,681 loops=1)

26. 0.029 0.029 ↓ 1.0 110 1

Seq Scan on plans (cost=0.00..3.33 rows=109 width=9) (actual time=0.005..0.029 rows=110 loops=1)

Planning time : 11.587 ms
Execution time : 1,871.129 ms