explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zwd9

Settings
# exclusive inclusive rows x rows loops node
1. 2.089 1,800.656 ↓ 111.0 111 1

Subquery Scan on temp (cost=169,356.76..214,005.59 rows=1 width=279) (actual time=1,695.362..1,800.656 rows=111 loops=1)

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

WindowAgg (cost=169,356.76..211,236.67 rows=692,230 width=203) (actual time=1,663.085..1,798.567 rows=8,582 loops=1)

3. 12.972 1,664.496 ↑ 80.7 8,582 1

Sort (cost=169,356.76..169,702.87 rows=692,230 width=203) (actual time=1,662.980..1,664.496 rows=8,582 loops=1)

  • Sort Key: roaming_days.carrier_account_id, roaming_days.filter_date, roaming_days.country_id
  • Sort Method: quicksort Memory: 2973kB
4. 15.841 1,651.524 ↑ 80.7 8,582 1

WindowAgg (cost=65,726.69..106,914.38 rows=692,230 width=203) (actual time=1,634.481..1,651.524 rows=8,582 loops=1)

5. 16.364 1,635.683 ↑ 80.7 8,582 1

Sort (cost=65,726.69..66,072.81 rows=692,230 width=203) (actual time=1,634.419..1,635.683 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: 2673kB
6. 472.800 1,619.319 ↑ 80.7 8,582 1

Merge Join (cost=1,964.35..3,284.31 rows=692,230 width=203) (actual time=52.602..1,619.319 rows=8,582 loops=1)

  • Merge Cond: (carrier_accounts.plan_id = plans.id)
7. 13.350 1,144.093 ↑ 10.3 8,582 1

Nested Loop Left Join (cost=1,960.28..10,055.57 rows=88,267 width=210) (actual time=50.991..1,144.093 rows=8,582 loops=1)

8. 832.287 1,096.415 ↑ 10.3 8,582 1

Merge Join (cost=1,960.23..3,287.03 rows=88,267 width=170) (actual time=50.960..1,096.415 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. 6.058 6.058 ↓ 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.016..6.058 rows=26,241 loops=1)

10. 215.490 258.070 ↓ 489.7 2,084,809 1

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

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

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

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

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

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

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

  • Hash Cond: (phone_calls.mnc_id = pc_mncs.id)
14. 3.763 3.763 ↑ 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.763 rows=25,508 loops=1)

15. 0.291 0.965 ↓ 1.0 1,921 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 91kB
16. 0.674 0.674 ↓ 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.674 rows=1,921 loops=1)

17. 2.443 12.098 ↓ 1.9 6,552 1

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

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

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

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

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

20. 0.017 0.107 ↑ 3.0 27 1

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

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

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

22. 0.255 0.577 ↓ 1.0 1,921 1

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

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

  • Heap Fetches: 115
24. 34.328 34.328 ↑ 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.004 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. 2.396 2.426 ↓ 79.6 8,681 1

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

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

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

Planning time : 16.719 ms
Execution time : 1,801.604 ms