explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bv5L

Settings
# exclusive inclusive rows x rows loops node
1. 1.353 1,917.453 ↓ 111.0 111 1

Subquery Scan on temp (cost=54,501.31..96,157.93 rows=1 width=183) (actual time=1,905.871..1,917.453 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. 13.528 1,916.100 ↑ 80.9 8,582 1

WindowAgg (cost=54,501.31..93,380.82 rows=694,277 width=155) (actual time=1,901.195..1,916.100 rows=8,582 loops=1)

3. 15.577 1,902.572 ↑ 80.9 8,582 1

Sort (cost=54,501.31..54,848.44 rows=694,277 width=155) (actual time=1,901.144..1,902.572 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. 513.743 1,886.995 ↑ 80.9 8,582 1

Merge Join (cost=1,750.03..2,041.26 rows=694,277 width=155) (actual time=50.892..1,886.995 rows=8,582 loops=1)

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

Merge Join (cost=1,745.96..3,074.84 rows=88,516 width=166) (actual time=50.699..1,371.032 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
6. 7.294 7.294 ↓ 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=12) (actual time=0.007..7.294 rows=26,241 loops=1)

7. 284.687 326.205 ↓ 488.4 2,084,809 1

Sort (cost=1,745.90..1,748.04 rows=4,269 width=170) (actual time=49.715..326.205 rows=2,084,809 loops=1)

  • Sort Key: carrier_accounts.plan_id
  • Sort Method: quicksort Memory: 2664kB
8. 7.619 41.518 ↓ 2.0 8,582 1

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

  • Hash Cond: ((phone_calls.carrier_account_id = carrier_accounts.id) AND (pc_mncs.country_id = tariffs.country_id))
9. 19.458 24.018 ↑ 1.0 25,508 1

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

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

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

11. 0.243 0.664 ↓ 1.0 1,921 1

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

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

13. 2.494 9.881 ↓ 1.9 6,552 1

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

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 423kB
14. 5.087 7.387 ↓ 1.9 6,552 1

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

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

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

16. 0.007 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)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
17. 0.042 0.042 ↓ 1.4 116 1

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

18. 2.169 2.220 ↓ 79.6 8,681 1

Sort (cost=4.06..4.12 rows=109 width=5) (actual time=0.088..2.220 rows=8,681 loops=1)

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

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

Planning time : 2.993 ms
Execution time : 1,917.638 ms