explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AILi

Settings
# exclusive inclusive rows x rows loops node
1. 2.160 1,745.440 ↓ 111.0 111 1

Subquery Scan on temp (cost=62,346.69..107,687.76 rows=1 width=279) (actual time=1,628.661..1,745.440 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. 143.401 1,743.280 ↑ 80.7 8,582 1

WindowAgg (cost=62,346.69..104,918.84 rows=692,230 width=191) (actual time=1,598.499..1,743.280 rows=8,582 loops=1)

3. 15.049 1,599.879 ↑ 80.7 8,582 1

Sort (cost=62,346.69..62,692.81 rows=692,230 width=191) (actual time=1,598.429..1,599.879 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: 2664kB
4. 465.420 1,584.830 ↑ 80.7 8,582 1

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

  • Merge Cond: (carrier_accounts.plan_id = plans.id)
5. 10.956 1,116.997 ↑ 10.3 8,582 1

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

6. 819.821 1,071.713 ↑ 10.3 8,582 1

Merge Join (cost=1,960.23..3,287.03 rows=88,267 width=170) (actual time=45.402..1,071.713 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
7. 5.744 5.744 ↓ 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.008..5.744 rows=26,241 loops=1)

8. 209.104 246.148 ↓ 489.7 2,084,809 1

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

  • Sort Key: carrier_accounts.plan_id
  • Sort Method: quicksort Memory: 2664kB
9. 2.922 37.044 ↓ 2.0 8,582 1

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

  • Hash Cond: (phone_calls.sim_mnc_id = pc_sim_mncs.id)
10. 6.291 33.640 ↓ 2.0 8,582 1

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

  • Hash Cond: ((phone_calls.carrier_account_id = carrier_accounts.id) AND (pc_mncs.country_id = tariffs.country_id))
11. 16.325 20.335 ↑ 1.0 25,508 1

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

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

13. 0.247 0.665 ↓ 1.0 1,921 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 91kB
14. 0.418 0.418 ↓ 1.0 1,921 1

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

15. 1.299 7.014 ↓ 1.9 6,552 1

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

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 423kB
16. 3.929 5.715 ↓ 1.9 6,552 1

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

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

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

18. 0.008 0.048 ↑ 3.0 27 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.040 0.040 ↓ 1.4 116 1

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

20. 0.207 0.482 ↓ 1.0 1,921 1

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

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

  • Heap Fetches: 115
22. 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))
23. 2.355 2.413 ↓ 79.6 8,681 1

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

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

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