explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1L6m

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=284,297.10..285,122.73 rows=11,009 width=80) (actual rows= loops=)

2.          

CTE customer_view

3. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.00..639.51 rows=8,580 width=8) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Seq Scan on customer s1 (cost=0.00..5.31 rows=130 width=4) (actual rows= loops=)

  • Filter: ((parentid IS NOT NULL) AND (NOT deleted))
5. 0.000 0.000 ↓ 0.0

Hash Join (cost=6.94..46.26 rows=845 width=8) (actual rows= loops=)

  • Hash Cond: (s1_1.parentid = s2.customerid)
6. 0.000 0.000 ↓ 0.0

WorkTable Scan on customer_view s1_1 (cost=0.00..26.00 rows=1,300 width=8) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash (cost=5.31..5.31 rows=130 width=8) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Seq Scan on customer s2 (cost=0.00..5.31 rows=130 width=8) (actual rows= loops=)

  • Filter: ((parentid IS NOT NULL) AND (NOT deleted))
9. 0.000 0.000 ↓ 0.0

Sort (cost=283,657.59..283,932.80 rows=110,084 width=80) (actual rows= loops=)

  • Sort Key: pa.deviceid, pa.patchguid, pa.approvalpriority
10. 0.000 0.000 ↓ 0.0

HashAggregate (cost=272,237.34..273,338.18 rows=110,084 width=73) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Append (cost=78,316.76..270,586.08 rows=110,084 width=73) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash Join (cost=78,316.76..94,962.03 rows=8,266 width=73) (actual rows= loops=)

  • Hash Cond: (pa.customerid = cv.parentid)
13. 0.000 0.000 ↓ 0.0

Hash Join (cost=78,037.91..94,392.90 rows=193 width=73) (actual rows= loops=)

  • Hash Cond: ((cp.deviceid = d.deviceid) AND (cp.updateid = pa.patchguid))
14. 0.000 0.000 ↓ 0.0

Seq Scan on cim_patch cp (cost=0.00..5,391.38 rows=190,638 width=41) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash (cost=77,719.28..77,719.28 rows=21,242 width=77) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash Join (cost=435.71..77,719.28 rows=21,242 width=77) (actual rows= loops=)

  • Hash Cond: (pa.deviceid = d.deviceid)
17. 0.000 0.000 ↓ 0.0

Seq Scan on patchapproval pa (cost=0.00..76,727.28 rows=91,698 width=73) (actual rows= loops=)

  • Filter: ((NOT deleted) AND (deviceid IS NOT NULL))
18. 0.000 0.000 ↓ 0.0

Hash (cost=418.35..418.35 rows=1,389 width=4) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on device d (cost=0.00..418.35 rows=1,389 width=4) (actual rows= loops=)

  • Filter: ((NOT deleted) AND patchenabled AND enabled)
20. 0.000 0.000 ↓ 0.0

Hash (cost=171.60..171.60 rows=8,580 width=4) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

CTE Scan on customer_view cv (cost=0.00..171.60 rows=8,580 width=4) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Merge Join (cost=165,621.43..170,963.02 rows=101,812 width=73) (actual rows= loops=)

  • Merge Cond: ((pa_1.customerid = cv_1.parentid) AND (d_1.customerid = cv_1.customerid))
23. 0.000 0.000 ↓ 0.0

Sort (cost=164,889.27..166,075.89 rows=474,650 width=77) (actual rows= loops=)

  • Sort Key: pa_1.customerid, d_1.customerid
24. 0.000 0.000 ↓ 0.0

Merge Join (cost=91,738.99..99,047.07 rows=474,650 width=77) (actual rows= loops=)

  • Merge Cond: (pa_1.patchguid = cp_1.updateid)
25. 0.000 0.000 ↓ 0.0

Sort (cost=79,590.83..79,685.02 rows=37,675 width=69) (actual rows= loops=)

  • Sort Key: pa_1.patchguid
26. 0.000 0.000 ↓ 0.0

Seq Scan on patchapproval pa_1 (cost=0.00..76,727.28 rows=37,675 width=69) (actual rows= loops=)

  • Filter: ((NOT deleted) AND (deviceid IS NULL) AND (folderid IS NULL))
27. 0.000 0.000 ↓ 0.0

Sort (cost=12,148.12..12,304.43 rows=62,526 width=45) (actual rows= loops=)

  • Sort Key: cp_1.updateid
28. 0.000 0.000 ↓ 0.0

Hash Join (cost=435.71..7,167.24 rows=62,526 width=45) (actual rows= loops=)

  • Hash Cond: (cp_1.deviceid = d_1.deviceid)
29. 0.000 0.000 ↓ 0.0

Seq Scan on cim_patch cp_1 (cost=0.00..5,391.38 rows=190,638 width=41) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Hash (cost=418.35..418.35 rows=1,389 width=8) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on device d_1 (cost=0.00..418.35 rows=1,389 width=8) (actual rows= loops=)

  • Filter: ((NOT deleted) AND patchenabled AND enabled)
32. 0.000 0.000 ↓ 0.0

Sort (cost=732.16..753.61 rows=8,580 width=8) (actual rows= loops=)

  • Sort Key: cv_1.parentid, cv_1.customerid
33. 0.000 0.000 ↓ 0.0

CTE Scan on customer_view cv_1 (cost=0.00..171.60 rows=8,580 width=8) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,520.05..3,560.19 rows=6 width=73) (actual rows= loops=)

  • Hash Cond: ((pa_2.customerid = cv_2.parentid) AND (d_2.customerid = cv_2.customerid))
35. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,219.75..3,201.78 rows=27 width=77) (actual rows= loops=)

  • Merge Cond: (pa_2.folderid = fd.folderid)
  • Join Filter: (cp_2.deviceid = d_2.deviceid)
36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..37,543.54 rows=59,501 width=73) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Index Scan using idx_patchapproval6 on patchapproval pa_2 (cost=0.42..6,555.11 rows=1,549 width=69) (actual rows= loops=)

  • Index Cond: (folderid IS NOT NULL)
  • Filter: (NOT deleted)
38. 0.000 0.000 ↓ 0.0

Index Scan using idx_cim_patch2 on cim_patch cp_2 (cost=0.42..19.32 rows=69 width=41) (actual rows= loops=)

  • Index Cond: (updateid = pa_2.patchguid)
39. 0.000 0.000 ↓ 0.0

Sort (cost=965.48..974.97 rows=3,796 width=16) (actual rows= loops=)

  • Sort Key: fd.folderid
40. 0.000 0.000 ↓ 0.0

Hash Join (cost=435.71..739.80 rows=3,796 width=16) (actual rows= loops=)

  • Hash Cond: (fd.deviceid = d_2.deviceid)
41. 0.000 0.000 ↓ 0.0

Seq Scan on folderdevices fd (cost=0.00..222.73 rows=11,573 width=8) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash (cost=418.35..418.35 rows=1,389 width=8) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on device d_2 (cost=0.00..418.35 rows=1,389 width=8) (actual rows= loops=)

  • Filter: ((NOT deleted) AND enabled AND patchenabled)
44. 0.000 0.000 ↓ 0.0

Hash (cost=171.60..171.60 rows=8,580 width=8) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

CTE Scan on customer_view cv_2 (cost=0.00..171.60 rows=8,580 width=8) (actual rows= loops=)