explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6CwMb

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 0.090 ↑ 1.0 1 1

Aggregate (cost=6,930.00..6,930.01 rows=1 width=8) (actual time=0.090..0.090 rows=1 loops=1)

2. 0.000 0.088 ↓ 0.0 0 1

Unique (cost=6,929.94..6,929.99 rows=1 width=257) (actual time=0.088..0.088 rows=0 loops=1)

3. 0.058 0.088 ↓ 0.0 0 1

Sort (cost=6,929.94..6,929.95 rows=1 width=257) (actual time=0.088..0.088 rows=0 loops=1)

  • Sort Key: c.customerid, ct.customertype, c.customername, c.externalid, c.address, c.firstname, c.middlename, c.lastname, c.npi, ucp.preferencetype, speciality.specialityname, orgunits_1.orgunitid, c.header, c.subheader, orgunits.orgunitlevelid, orgunitlevel.is_leaf_level
  • Sort Method: quicksort Memory: 25kB
4. 0.000 0.030 ↓ 0.0 0 1

Nested Loop Left Join (cost=50.37..6,929.93 rows=1 width=257) (actual time=0.030..0.030 rows=0 loops=1)

5. 0.000 0.030 ↓ 0.0 0 1

Nested Loop Left Join (cost=49.95..6,929.47 rows=1 width=255) (actual time=0.030..0.030 rows=0 loops=1)

6. 0.000 0.030 ↓ 0.0 0 1

Nested Loop Left Join (cost=49.80..6,929.29 rows=1 width=231) (actual time=0.030..0.030 rows=0 loops=1)

7. 0.000 0.030 ↓ 0.0 0 1

Nested Loop (cost=49.36..6,928.61 rows=1 width=223) (actual time=0.030..0.030 rows=0 loops=1)

8. 0.016 0.030 ↓ 0.0 0 1

Hash Join (cost=49.09..6,928.29 rows=1 width=222) (actual time=0.030..0.030 rows=0 loops=1)

  • Hash Cond: (alignments.customerid = ca.parentcustomerid)
9. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=37.77..6,908.58 rows=2,235 width=230) (never executed)

  • Hash Cond: (c.customertypeid = ct.customertypeid)
10. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=13.15..6,853.28 rows=2,235 width=206) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=12.72..3,850.76 rows=4,930 width=32) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=12.16..74.40 rows=12 width=32) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=11.87..69.85 rows=12 width=16) (never executed)

  • Hash Cond: (orgunits_1.orgunitlevelid = ol.orgunitlevelid)
14. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.85..56.58 rows=566 width=16) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..16.61 rows=1 width=8) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Index Scan using userorgunitmapping_userid_idx on userorgunitmapping uom (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (userid = 8,795)
17. 0.000 0.000 ↓ 0.0 0

Index Scan using orgunits_orgunitid_orgunitlevelid on orgunits orgunits_2 (cost=0.28..8.30 rows=1 width=16) (never executed)

  • Index Cond: (orgunitid = uom.orgunitid)
18. 0.000 0.000 ↓ 0.0 0

Index Scan using orgunits_treeright_idx on orgunits orgunits_1 (cost=0.28..34.31 rows=566 width=24) (never executed)

  • Index Cond: (treeright <= orgunits_2.treeright)
  • Filter: (treeleft >= orgunits_2.treeleft)
19. 0.000 0.000 ↓ 0.0 0

Hash (cost=10.96..10.96 rows=5 width=16) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Seq Scan on orgunitlevel ol (cost=0.00..10.96 rows=5 width=16) (never executed)

  • Filter: (salesforceid = 313)
21. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_orgunits_orgunitid on orgunits (cost=0.28..0.37 rows=1 width=16) (never executed)

  • Index Cond: (orgunitid = orgunits_1.orgunitid)
22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using alignments_salesforceid_orgunitid_customerid on alignments (cost=0.56..306.41 rows=829 width=24) (never executed)

  • Index Cond: ((salesforceid = 313) AND (orgunitid = orgunits_1.orgunitid))
  • Heap Fetches: 0
23. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_customers_customerid on customers c (cost=0.43..0.60 rows=1 width=174) (never executed)

  • Index Cond: (customerid = alignments.customerid)
  • Filter: ((NOT is_pdrp_enabled) OR (is_pdrp_enabled IS NULL))
24. 0.000 0.000 ↓ 0.0 0

Hash (cost=16.50..16.50 rows=650 width=40) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Seq Scan on customertype ct (cost=0.00..16.50 rows=650 width=40) (never executed)

26. 0.002 0.014 ↓ 0.0 0 1

Hash (cost=11.28..11.28 rows=3 width=8) (actual time=0.014..0.014 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
27. 0.002 0.012 ↓ 0.0 0 1

Bitmap Heap Scan on customerassociations ca (cost=4.17..11.28 rows=3 width=8) (actual time=0.012..0.012 rows=0 loops=1)

  • Recheck Cond: (childcustomerid = 24,877,704)
28. 0.010 0.010 ↓ 0.0 0 1

Bitmap Index Scan on customerassociations_childcustomerid_idx (cost=0.00..4.17 rows=3 width=0) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (childcustomerid = 24,877,704)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_orgunitlevel_orgunitlevelid on orgunitlevel (cost=0.27..0.31 rows=1 width=9) (never executed)

  • Index Cond: (orgunitlevelid = orgunits.orgunitlevelid)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using customerspecialitymapping_customerid_idx on customerspecialitymapping (cost=0.43..0.68 rows=1 width=16) (never executed)

  • Index Cond: (c.customerid = customerid)
  • Filter: ((isprimary IS NULL) OR isprimary)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_speciality on speciality (cost=0.15..0.17 rows=1 width=40) (never executed)

  • Index Cond: (customerspecialitymapping.specialityid = specialityid)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using usercustomerpreferences_userid_customerid_idx on usercustomerpreferences ucp (cost=0.42..0.45 rows=1 width=10) (never executed)

  • Index Cond: ((userid = 8,795) AND (customerid = c.customerid))