explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wvht

Settings
# exclusive inclusive rows x rows loops node
1. 7,306.800 7,306.800 ↓ 0.0 0 1

CTE Scan on cust (cost=7,007.36..7,007.56 rows=10 width=33) (actual time=7,306.800..7,306.800 rows=0 loops=1)

2.          

CTE cust

3. 0.001 7,306.799 ↓ 0.0 0 1

Limit (cost=7,007.33..7,007.36 rows=10 width=39) (actual time=7,306.799..7,306.799 rows=0 loops=1)

4. 0.008 7,306.798 ↓ 0.0 0 1

Sort (cost=7,007.33..7,012.88 rows=2,219 width=39) (actual time=7,306.798..7,306.798 rows=0 loops=1)

  • Sort Key: customers.firstname
  • Sort Method: quicksort Memory: 25kB
5. 1,281.202 7,306.790 ↓ 0.0 0 1

Nested Loop (cost=2.12..6,959.38 rows=2,219 width=39) (actual time=7,306.790..7,306.790 rows=0 loops=1)

6. 213.009 1,855.784 ↓ 422.9 2,084,902 1

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

7. 3.083 12.101 ↓ 12.8 153 1

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

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

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

9. 3.815 6.252 ↓ 12.8 153 1

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

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

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

11. 0.003 0.017 ↑ 1.0 1 1

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

12. 0.009 0.009 ↑ 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.009..0.009 rows=1 loops=1)

  • Index Cond: (userid = 8975)
13. 0.005 0.005 ↑ 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.004..0.005 rows=1 loops=1)

  • Index Cond: (orgunitid = uom.orgunitid)
14. 1.921 1.921 ↓ 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.921 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.039 0.039 ↑ 1.0 5 1

Seq Scan on orgunitlevel ol (cost=0.00..10.96 rows=5 width=16) (actual time=0.011..0.039 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.792 1.836 ↑ 1.0 237 153

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

19. 0.044 0.044 ↑ 1.0 237 1

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

20. 1,630.674 1,630.674 ↓ 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.658 rows=13,627 loops=153)

  • Index Cond: ((salesforceid = 313) AND (orgunitid = orgunits_1.orgunitid))
  • Heap Fetches: 2084902
21. 4,169.804 4,169.804 ↓ 0.0 0 2,084,902

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

  • Index Cond: (customerid = alignments.customerid)
  • Filter: ((NOT is_pdrp_enabled) AND (customertypeid = 1))
  • Rows Removed by Filter: 1
Planning time : 7.088 ms