explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6XbA

Settings
# exclusive inclusive rows x rows loops node
1. 8,951.193 8,951.193 ↑ 1.0 10 1

CTE Scan on cust (cost=7,007.59..7,007.79 rows=10 width=33) (actual time=8,951.185..8,951.193 rows=10 loops=1)

2.          

CTE cust

3. 0.003 8,951.185 ↑ 1.0 10 1

Limit (cost=7,007.57..7,007.59 rows=10 width=39) (actual time=8,951.182..8,951.185 rows=10 loops=1)

4. 662.335 8,951.182 ↑ 223.0 10 1

Sort (cost=7,007.57..7,013.14 rows=2,230 width=39) (actual time=8,951.182..8,951.182 rows=10 loops=1)

  • Sort Key: customers.firstname
  • Sort Method: top-N heapsort Memory: 25kB
5. 131.553 8,288.847 ↓ 914.7 2,039,674 1

Nested Loop (cost=2.12..6,959.38 rows=2,230 width=39) (actual time=2.577..8,288.847 rows=2,039,674 loops=1)

6. 218.293 1,902.588 ↓ 422.9 2,084,902 1

Nested Loop (cost=1.69..3,944.54 rows=4,930 width=33) (actual time=2.567..1,902.588 rows=2,084,902 loops=1)

7. 2.927 12.158 ↓ 12.8 153 1

Nested Loop (cost=1.13..168.18 rows=12 width=33) (actual time=2.532..12.158 rows=153 loops=1)

  • Join Filter: (orgunits.orgunitlevelid = orgunitlevel.orgunitlevelid)
  • Rows Removed by Join Filter: 36108
8. 0.341 7.242 ↓ 12.8 153 1

Nested Loop (cost=1.13..114.56 rows=12 width=32) (actual time=2.512..7.242 rows=153 loops=1)

9. 3.890 6.289 ↓ 12.8 153 1

Nested Loop (cost=0.85..110.01 rows=12 width=16) (actual time=2.506..6.289 rows=153 loops=1)

  • Join Filter: (orgunits_1.orgunitlevelid = ol.orgunitlevelid)
  • Rows Removed by Join Filter: 24912
10. 0.481 2.399 ↓ 8.9 5,013 1

Nested Loop (cost=0.85..56.58 rows=566 width=16) (actual time=0.052..2.399 rows=5,013 loops=1)

11. 0.002 0.020 ↑ 1.0 1 1

Nested Loop (cost=0.56..16.61 rows=1 width=8) (actual time=0.017..0.020 rows=1 loops=1)

12. 0.012 0.012 ↑ 1.0 1 1

Index Scan using userorgunitmapping_userid_idx on userorgunitmapping uom (cost=0.28..8.30 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: (userid = 8975)
13. 0.006 0.006 ↑ 1.0 1 1

Index Scan using orgunits_orgunitid_orgunitlevelid on orgunits orgunits_2 (cost=0.28..8.30 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: (orgunitid = uom.orgunitid)
14. 1.898 1.898 ↓ 8.9 5,013 1

Index Scan using orgunits_treeright_idx on orgunits orgunits_1 (cost=0.28..34.31 rows=566 width=24) (actual time=0.034..1.898 rows=5,013 loops=1)

  • Index Cond: (treeright <= orgunits_2.treeright)
  • Filter: (treeleft >= orgunits_2.treeleft)
15. 0.000 0.000 ↑ 1.0 5 5,013

Materialize (cost=0.00..10.99 rows=5 width=16) (actual time=0.000..0.000 rows=5 loops=5,013)

16. 0.041 0.041 ↑ 1.0 5 1

Seq Scan on orgunitlevel ol (cost=0.00..10.96 rows=5 width=16) (actual time=0.012..0.041 rows=5 loops=1)

  • Filter: (salesforceid = 313)
  • Rows Removed by Filter: 232
17. 0.612 0.612 ↑ 1.0 1 153

Index Scan using pk_orgunits_orgunitid on orgunits (cost=0.28..0.37 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=153)

  • Index Cond: (orgunitid = orgunits_1.orgunitid)
18. 1.937 1.989 ↑ 1.0 237 153

Materialize (cost=0.00..11.56 rows=237 width=9) (actual time=0.000..0.013 rows=237 loops=153)

19. 0.052 0.052 ↑ 1.0 237 1

Seq Scan on orgunitlevel (cost=0.00..10.37 rows=237 width=9) (actual time=0.002..0.052 rows=237 loops=1)

20. 1,672.137 1,672.137 ↓ 16.4 13,627 153

Index Only Scan using alignments_salesforceid_orgunitid_customerid on alignments (cost=0.56..306.41 rows=829 width=24) (actual time=0.016..10.929 rows=13,627 loops=153)

  • Index Cond: ((salesforceid = 313) AND (orgunitid = orgunits_1.orgunitid))
  • Heap Fetches: 2084902
21. 6,254.706 6,254.706 ↑ 1.0 1 2,084,902

Index Scan using pk_customers_customerid on customers (cost=0.43..0.60 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=2,084,902)

  • Index Cond: (customerid = alignments.customerid)
  • Filter: (((NOT is_pdrp_enabled) OR (is_pdrp_enabled IS NULL)) AND (customertypeid = 1))
  • Rows Removed by Filter: 0
Planning time : 7.391 ms