explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wmgW

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

Sort (cost=4,444.54..4,444.56 rows=10 width=240) (actual time=8,053.341..8,053.341 rows=10 loops=1)

  • Sort Key: customers.firstname
  • Sort Method: quicksort Memory: 26kB
2.          

CTE cust

3. 0.005 8,053.137 ↑ 1.0 10 1

Limit (cost=4,345.64..4,345.67 rows=10 width=39) (actual time=8,053.129..8,053.137 rows=10 loops=1)

4. 671.245 8,053.132 ↑ 223.0 10 1

Sort (cost=4,345.64..4,351.22 rows=2,230 width=39) (actual time=8,053.127..8,053.132 rows=10 loops=1)

  • Sort Key: customers_1.firstname
  • Sort Method: top-N heapsort Memory: 25kB
5. 278.151 7,381.887 ↓ 914.7 2,039,674 1

Nested Loop (cost=2.40..4,297.45 rows=2,230 width=39) (actual time=2.571..7,381.887 rows=2,039,674 loops=1)

6. 211.109 849.030 ↓ 422.9 2,084,902 1

Nested Loop (cost=1.96..646.11 rows=4,930 width=33) (actual time=2.559..849.030 rows=2,084,902 loops=1)

7. 0.257 9.856 ↓ 12.8 153 1

Nested Loop (cost=1.40..117.81 rows=12 width=33) (actual time=2.526..9.856 rows=153 loops=1)

8. 0.380 8.987 ↓ 12.8 153 1

Nested Loop (cost=1.13..114.01 rows=12 width=32) (actual time=2.522..8.987 rows=153 loops=1)

9. 5.105 8.148 ↓ 12.8 153 1

Nested Loop (cost=0.85..110.08 rows=12 width=16) (actual time=2.512..8.148 rows=153 loops=1)

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

Nested Loop (cost=0.85..56.66 rows=566 width=16) (actual time=0.040..3.043 rows=5,013 loops=1)

11. 0.001 0.018 ↑ 1.0 1 1

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

12. 0.011 0.011 ↑ 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.010..0.011 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. 2.469 2.469 ↓ 8.9 5,013 1

Index Scan using orgunits_treeright_idx on orgunits orgunits_1 (cost=0.28..34.39 rows=566 width=24) (actual time=0.023..2.469 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.013..0.041 rows=5 loops=1)

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

Index Only Scan using orgunits_orgunitid_orgunitlevelid on orgunits (cost=0.28..0.32 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=153)

  • Index Cond: (orgunitid = orgunits_1.orgunitid)
  • Heap Fetches: 0
18. 0.612 0.612 ↑ 1.0 1 153

Index Scan using pk_orgunitlevel_orgunitlevelid on orgunitlevel (cost=0.27..0.31 rows=1 width=9) (actual time=0.003..0.004 rows=1 loops=153)

  • Index Cond: (orgunitlevelid = orgunits.orgunitlevelid)
19. 628.065 628.065 ↓ 16.4 13,627 153

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

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

Index Scan using pk_customers_customerid on customers customers_1 (cost=0.43..0.73 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
21. 0.003 8,053.325 ↑ 1.0 10 1

Nested Loop Left Join (cost=1.58..98.70 rows=10 width=240) (actual time=8,053.183..8,053.325 rows=10 loops=1)

22. 0.002 8,053.292 ↑ 1.0 10 1

Nested Loop Left Join (cost=1.16..94.14 rows=10 width=238) (actual time=8,053.169..8,053.292 rows=10 loops=1)

23. 0.011 8,053.270 ↑ 1.0 10 1

Nested Loop Left Join (cost=1.01..92.37 rows=10 width=214) (actual time=8,053.162..8,053.270 rows=10 loops=1)

24. 0.008 8,053.209 ↑ 1.0 10 1

Nested Loop (cost=0.58..86.55 rows=10 width=206) (actual time=8,053.149..8,053.209 rows=10 loops=1)

25. 0.004 8,053.191 ↑ 1.0 10 1

Nested Loop (cost=0.43..84.78 rows=10 width=182) (actual time=8,053.143..8,053.191 rows=10 loops=1)

26. 8,053.147 8,053.147 ↑ 1.0 10 1

CTE Scan on cust (cost=0.00..0.20 rows=10 width=25) (actual time=8,053.134..8,053.147 rows=10 loops=1)

27. 0.040 0.040 ↑ 1.0 1 10

Index Scan using pk_customers_customerid on customers (cost=0.43..8.45 rows=1 width=165) (actual time=0.004..0.004 rows=1 loops=10)

  • Index Cond: (customerid = cust.customerid)
28. 0.010 0.010 ↑ 1.0 1 10

Index Scan using pk_customertype on customertype ct (cost=0.15..0.17 rows=1 width=40) (actual time=0.001..0.001 rows=1 loops=10)

  • Index Cond: (customertypeid = customers.customertypeid)
29. 0.050 0.050 ↑ 1.0 1 10

Index Scan using customerspecialitymapping_customerid_idx on customerspecialitymapping (cost=0.43..0.57 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=10)

  • Index Cond: (customers.customerid = customerid)
  • Filter: ((isprimary IS NULL) OR isprimary)
  • Rows Removed by Filter: 1
30. 0.020 0.020 ↑ 1.0 1 10

Index Scan using pk_speciality on speciality (cost=0.15..0.17 rows=1 width=40) (actual time=0.001..0.002 rows=1 loops=10)

  • Index Cond: (customerspecialitymapping.specialityid = specialityid)
31. 0.030 0.030 ↓ 0.0 0 10

Index Scan using usercustomerpreferences_userid_customerid_idx on usercustomerpreferences ucp (cost=0.42..0.45 rows=1 width=10) (actual time=0.003..0.003 rows=0 loops=10)

  • Index Cond: ((userid = 8975) AND (customerid = customers.customerid))
Planning time : 7.673 ms