explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xlx9

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 1.036 ↓ 0.0 0 1

Subquery Scan on phone_calls_with_total_roaming_country_usage (cost=26.13..26.45 rows=1 width=1,350) (actual time=1.035..1.036 rows=0 loops=1)

  • Filter: ('[2018-03-01,2018-04-01)'::daterange @> phone_calls_with_total_roaming_country_usage.filter_date)
  • Rows Removed by Filter: 5
2. 0.088 1.031 ↓ 5.0 5 1

WindowAgg (cost=26.13..26.43 rows=1 width=1,362) (actual time=0.987..1.031 rows=5 loops=1)

3. 0.025 0.943 ↓ 5.0 5 1

Sort (cost=26.13..26.13 rows=1 width=1,314) (actual time=0.942..0.943 rows=5 loops=1)

  • Sort Key: phone_calls.carrier_account_id, (period_start(phone_calls.filter_date, carrier_accounts.contract_start_date))
  • Sort Method: quicksort Memory: 26kB
4. 0.527 0.918 ↓ 5.0 5 1

GroupAggregate (cost=25.82..26.12 rows=1 width=1,314) (actual time=0.833..0.918 rows=5 loops=1)

  • Group Key: phone_calls.id, plans.rate_type, carrier_accounts.contract_start_date
5. 0.014 0.391 ↓ 5.0 5 1

Sort (cost=25.82..25.83 rows=1 width=1,262) (actual time=0.391..0.391 rows=5 loops=1)

  • Sort Key: phone_calls.id, plans.rate_type, carrier_accounts.contract_start_date
  • Sort Method: quicksort Memory: 26kB
6. 0.031 0.377 ↓ 5.0 5 1

Nested Loop Left Join (cost=4.04..25.81 rows=1 width=1,262) (actual time=0.237..0.377 rows=5 loops=1)

  • Join Filter: ((roaming_days.carrier_account_id = phone_calls.carrier_account_id) AND (roaming_days.country_id = roaming_tariffs.country_id) AND (roaming_days.filter_date = phone_calls.filter_date))
  • Rows Removed by Join Filter: 27
7. 0.010 0.326 ↓ 5.0 5 1

Nested Loop (cost=4.04..24.48 rows=1 width=1,234) (actual time=0.220..0.326 rows=5 loops=1)

8. 0.012 0.306 ↓ 5.0 5 1

Nested Loop (cost=3.76..20.18 rows=1 width=1,234) (actual time=0.212..0.306 rows=5 loops=1)

  • Join Filter: (tariffs.country_id = pc_mncs.country_id)
  • Rows Removed by Join Filter: 5
9. 0.025 0.214 ↓ 10.0 10 1

Nested Loop (cost=3.48..11.87 rows=1 width=1,238) (actual time=0.147..0.214 rows=10 loops=1)

10. 0.027 0.119 ↓ 5.0 10 1

Hash Join (cost=3.34..5.64 rows=2 width=1,245) (actual time=0.099..0.119 rows=10 loops=1)

  • Hash Cond: (carrier_accounts.plan_id = tariffs.plan_id)
11. 0.025 0.059 ↑ 1.0 5 1

Hash Join (cost=1.11..3.37 rows=5 width=1,229) (actual time=0.048..0.059 rows=5 loops=1)

  • Hash Cond: (carrier_accounts.id = phone_calls.carrier_account_id)
12. 0.019 0.019 ↑ 1.0 15 1

Seq Scan on carrier_accounts (cost=0.00..2.15 rows=15 width=12) (actual time=0.017..0.019 rows=15 loops=1)

13. 0.006 0.015 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=1,221) (actual time=0.015..0.015 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.009 0.009 ↑ 1.0 5 1

Seq Scan on phone_calls (cost=0.00..1.05 rows=5 width=1,221) (actual time=0.008..0.009 rows=5 loops=1)

15. 0.002 0.033 ↓ 2.0 4 1

Hash (cost=2.21..2.21 rows=2 width=16) (actual time=0.033..0.033 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.014 0.031 ↓ 2.0 4 1

Hash Join (cost=1.09..2.21 rows=2 width=16) (actual time=0.028..0.031 rows=4 loops=1)

  • Hash Cond: (tariffs.plan_id = roaming_tariffs.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: 4
17. 0.008 0.008 ↑ 1.0 4 1

Seq Scan on tariffs (cost=0.00..1.04 rows=4 width=16) (actual time=0.007..0.008 rows=4 loops=1)

18. 0.002 0.009 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=16) (actual time=0.009..0.009 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.007 0.007 ↑ 1.0 4 1

Seq Scan on tariffs roaming_tariffs (cost=0.00..1.04 rows=4 width=16) (actual time=0.005..0.007 rows=4 loops=1)

20. 0.070 0.070 ↑ 1.0 1 10

Index Scan using plans_pkey on plans (cost=0.14..3.09 rows=1 width=9) (actual time=0.007..0.007 rows=1 loops=10)

  • Index Cond: (id = carrier_accounts.plan_id)
21. 0.080 0.080 ↑ 1.0 1 10

Index Scan using mncs_pkey on mncs pc_mncs (cost=0.28..8.30 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=10)

  • Index Cond: (id = phone_calls.mnc_id)
22. 0.010 0.010 ↑ 1.0 1 5

Index Only Scan using mncs_pkey on mncs pc_sim_mncs (cost=0.28..4.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=5)

  • Index Cond: (id = phone_calls.sim_mnc_id)
  • Heap Fetches: 0
23. 0.020 0.020 ↑ 2.0 6 5

Seq Scan on roaming_days (cost=0.00..1.12 rows=12 width=44) (actual time=0.003..0.004 rows=6 loops=5)

Planning time : 10.446 ms