explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Orcv

Settings
# exclusive inclusive rows x rows loops node
1. 10.603 3,383.651 ↑ 31.2 111 1

Subquery Scan on phone_calls_with_total_roaming_country_usage (cost=173,672.44..218,667.39 rows=3,461 width=279) (actual time=3,200.458..3,383.651 rows=111 loops=1)

  • Filter: ('[2018-03-01,2018-04-01)'::daterange @> phone_calls_with_total_roaming_country_usage.filter_date)
  • Rows Removed by Filter: 8471
2. 241.083 3,373.048 ↑ 80.7 8,582 1

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

3. 49.402 3,131.965 ↑ 80.7 8,582 1

Sort (cost=173,672.44..174,018.55 rows=692,230 width=191) (actual time=3,129.581..3,131.965 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
4. 1,007.453 3,082.563 ↑ 80.7 8,582 1

GroupAggregate (cost=62,346.69..114,610.06 rows=692,230 width=191) (actual time=2,081.402..3,082.563 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
5. 37.069 2,075.110 ↑ 80.7 8,582 1

Sort (cost=62,346.69..62,692.81 rows=692,230 width=191) (actual time=2,067.067..2,075.110 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
6. 6.890 2,038.041 ↑ 80.7 8,582 1

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

  • Merge Cond: (carrier_accounts.plan_id = plans.id)
7. 11.373 2,029.171 ↑ 10.3 8,582 1

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

8. 1,457.001 1,974.888 ↑ 10.3 8,582 1

Merge Join (cost=1,960.23..3,287.03 rows=88,267 width=170) (actual time=112.260..1,974.888 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
9. 10.893 10.893 ↓ 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.028..10.893 rows=26,241 loops=1)

10. 422.904 506.994 ↓ 489.7 2,084,809 1

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

  • Sort Key: carrier_accounts.plan_id
  • Sort Method: quicksort Memory: 2664kB
11. 8.542 84.090 ↓ 2.0 8,582 1

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

  • Hash Cond: (phone_calls.sim_mnc_id = pc_sim_mncs.id)
12. 9.585 74.435 ↓ 2.0 8,582 1

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

  • Hash Cond: ((phone_calls.carrier_account_id = carrier_accounts.id) AND (pc_mncs.country_id = tariffs.country_id))
13. 41.161 49.809 ↑ 1.0 25,508 1

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

  • Hash Cond: (phone_calls.mnc_id = pc_mncs.id)
14. 7.042 7.042 ↑ 1.0 25,508 1

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

15. 0.495 1.606 ↓ 1.0 1,921 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 91kB
16. 1.111 1.111 ↓ 1.0 1,921 1

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

17. 2.644 15.041 ↓ 1.9 6,552 1

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

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 423kB
18. 8.209 12.397 ↓ 1.9 6,552 1

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

  • Hash Cond: (tariffs.plan_id = carrier_accounts.plan_id)
19. 4.046 4.046 ↓ 1.0 26,726 1

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

20. 0.025 0.142 ↑ 3.0 27 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
21. 0.117 0.117 ↓ 1.4 116 1

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

22. 0.536 1.113 ↓ 1.0 1,921 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 84kB
23. 0.577 0.577 ↓ 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.053..0.577 rows=1,921 loops=1)

  • Heap Fetches: 115
24. 42.910 42.910 ↑ 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.004..0.005 rows=1 loops=8,582)

  • Index Cond: ((carrier_account_id = phone_calls.carrier_account_id) AND (country_id = roaming_tariffs.country_id) AND (filter_date = phone_calls.filter_date))
25. 1.918 1.980 ↓ 79.6 8,681 1

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

  • Sort Key: plans.id
  • Sort Method: quicksort Memory: 30kB
26. 0.062 0.062 ↓ 1.0 110 1

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

Planning time : 36.264 ms
Execution time : 3,385.415 ms