explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ekZo

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

HashAggregate (cost=2,206.86..2,391.64 rows=14,783 width=38) (actual rows= loops=)

  • Group Key: cm_1.columnsequenceid, cm.displaycolumnname, cm.columndisplaytype
2.          

CTE object_scope

3. 0.000 0.000 ↓ 0.0

Index Only Scan using project_projectid_clientid on project p_3 (cost=0.28..8.29 rows=1 width=60) (actual rows= loops=)

  • Index Cond: (projectid = 6,342)
4. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,654.75..2,050.73 rows=14,783 width=38) (actual rows= loops=)

  • Hash Cond: (rpa.column_id = cm.columnsequenceid)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,604.81..1,961.86 rows=14,783 width=28) (actual rows= loops=)

  • Hash Cond: (ucea.networksite_id = snetworkchild.parent_networksite_id)
  • Filter: ((hashed SubPlan 2) OR (hashed SubPlan 3) OR (hashed SubPlan 4) OR (hashed SubPlan 5))
6. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=1,589.58..1,886.87 rows=15,769 width=108) (actual rows= loops=)

  • Merge Cond: (ag.clientid = ucea.client_id)
7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.12..32.46 rows=503 width=16) (actual rows= loops=)

  • Join Filter: (a.applicationgroupid = ag.applicationgroupid)
8. 0.000 0.000 ↓ 0.0

Index Scan using applicationgroup_clientid_index on applicationgroup ag (cost=0.12..8.14 rows=1 width=16) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on application a (cost=0.00..18.03 rows=503 width=16) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Materialize (cost=1,589.45..1,629.97 rows=5,340 width=100) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1,589.45..1,616.62 rows=5,340 width=100) (actual rows= loops=)

  • Merge Cond: (ucea.client_id = ns.clientid)
12. 0.000 0.000 ↓ 0.0

Sort (cost=1,587.41..1,600.76 rows=5,340 width=92) (actual rows= loops=)

  • Sort Key: ucea.client_id
13. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1,222.47..1,256.79 rows=5,340 width=92) (actual rows= loops=)

  • Merge Cond: (ucea.project_group_uid = pppgpggroupchild.parent_project_group_uid)
14. 0.000 0.000 ↓ 0.0

Sort (cost=1,074.78..1,088.13 rows=5,340 width=100) (actual rows= loops=)

  • Sort Key: ucea.project_group_uid
15. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=714.94..744.16 rows=5,340 width=100) (actual rows= loops=)

  • Merge Cond: (ucea.application_group_id = agroupchild.applicationgroupid)
16. 0.000 0.000 ↓ 0.0

Sort (cost=674.34..687.69 rows=5,340 width=100) (actual rows= loops=)

  • Sort Key: ucea.application_group_id
17. 0.000 0.000 ↓ 0.0

Hash Join (cost=188.28..343.72 rows=5,340 width=100) (actual rows= loops=)

  • Hash Cond: (ur.role_id = rpa.role_id)
18. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=118.96..198.72 rows=946 width=80) (actual rows= loops=)

  • Merge Cond: (pg.clientid = ucea.client_id)
19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.12..62.51 rows=1,261 width=16) (actual rows= loops=)

  • Join Filter: (p.projectgroupuid = pg.projectgroupuid)
20. 0.000 0.000 ↓ 0.0

Index Scan using ix_projectgroup_clientid on projectgroup pg (cost=0.12..8.14 rows=1 width=24) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on project p (cost=0.00..38.61 rows=1,261 width=24) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Sort (cost=118.83..119.15 rows=128 width=72) (actual rows= loops=)

  • Sort Key: ucea.client_id
23. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=99.56..114.35 rows=128 width=72) (actual rows= loops=)

  • Hash Cond: (ucea.project_id = pppg.project_id)
24. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.05..15.36 rows=128 width=72) (actual rows= loops=)

  • Hash Cond: (ucea.usergroup_id = ur.user_group_id)
25. 0.000 0.000 ↓ 0.0

Seq Scan on usergroup_client_entities_association ucea (cost=0.00..11.11 rows=511 width=72) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Hash (cost=1.04..1.04 rows=1 width=16) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on users_roles ur (cost=0.00..1.04 rows=1 width=16) (actual rows= loops=)

  • Filter: (user_id = 3)
28. 0.000 0.000 ↓ 0.0

Hash (cost=82.75..82.75 rows=1,261 width=8) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Subquery Scan on pppg (cost=14.19..82.75 rows=1,261 width=8) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Hash Join (cost=14.19..70.14 rows=1,261 width=32) (actual rows= loops=)

  • Hash Cond: (p_1.projectgroupuid = pgparents.projectgroupuid)
31.          

CTE pgparents

32. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.00..13.83 rows=11 width=40) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on projectgroup pg_1 (cost=0.00..1.01 rows=1 width=40) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.02..1.26 rows=1 width=40) (actual rows= loops=)

  • Hash Cond: (pgparents_1_1.parentuid = pg2.projectgroupuid)
35. 0.000 0.000 ↓ 0.0

WorkTable Scan on pgparents pgparents_1_1 (cost=0.00..0.20 rows=10 width=32) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash (cost=1.01..1.01 rows=1 width=40) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on projectgroup pg2 (cost=0.00..1.01 rows=1 width=40) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on project p_1 (cost=0.00..38.61 rows=1,261 width=24) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=0.22..0.22 rows=11 width=16) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

CTE Scan on pgparents (cost=0.00..0.22 rows=11 width=16) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Hash (cost=69.03..69.03 rows=23 width=36) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash Join (cost=33.47..69.03 rows=23 width=36) (actual rows= loops=)

  • Hash Cond: (cm_1.tableid = tm.tablesequenceid)
43. 0.000 0.000 ↓ 0.0

Hash Join (cost=29.78..65.28 rows=23 width=44) (actual rows= loops=)

  • Hash Cond: (rpa.column_id = cm_1.columnsequenceid)
44. 0.000 0.000 ↓ 0.0

Seq Scan on role_permissions_association rpa (cost=0.00..34.01 rows=566 width=17) (actual rows= loops=)

  • Filter: (((update_flag IS TRUE) OR (read_flag IS TRUE)) AND (module_id = 2))
45. 0.000 0.000 ↓ 0.0

Hash (cost=29.21..29.21 rows=46 width=27) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on columnmetadata cm_1 (cost=4.63..29.21 rows=46 width=27) (actual rows= loops=)

  • Recheck Cond: ((model)::text = 'projects'::text)
47. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on columnmetadata_model_index (cost=0.00..4.62 rows=46 width=0) (actual rows= loops=)

  • Index Cond: ((model)::text = 'projects'::text)
48. 0.000 0.000 ↓ 0.0

Hash (cost=2.75..2.75 rows=75 width=8) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Seq Scan on tablemetadata tm (cost=0.00..2.75 rows=75 width=8) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Sort (cost=40.60..41.86 rows=503 width=16) (actual rows= loops=)

  • Sort Key: agroupchild.applicationgroupid
51. 0.000 0.000 ↓ 0.0

Seq Scan on application agroupchild (cost=0.00..18.03 rows=503 width=16) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Sort (cost=147.69..150.84 rows=1,261 width=24) (actual rows= loops=)

  • Sort Key: pppgpggroupchild.parent_project_group_uid
53. 0.000 0.000 ↓ 0.0

Subquery Scan on pppgpggroupchild (cost=14.19..82.75 rows=1,261 width=24) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Hash Join (cost=14.19..70.14 rows=1,261 width=32) (actual rows= loops=)

  • Hash Cond: (p_2.projectgroupuid = pgparents_1.projectgroupuid)
55.          

CTE pgparents

56. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.00..13.83 rows=11 width=40) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Seq Scan on projectgroup pg_2 (cost=0.00..1.01 rows=1 width=40) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.02..1.26 rows=1 width=40) (actual rows= loops=)

  • Hash Cond: (pgparents_1_2.parentuid = pg2_1.projectgroupuid)
59. 0.000 0.000 ↓ 0.0

WorkTable Scan on pgparents pgparents_1_2 (cost=0.00..0.20 rows=10 width=32) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Hash (cost=1.01..1.01 rows=1 width=40) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on projectgroup pg2_1 (cost=0.00..1.01 rows=1 width=40) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Seq Scan on project p_2 (cost=0.00..38.61 rows=1,261 width=24) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Hash (cost=0.22..0.22 rows=11 width=32) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

CTE Scan on pgparents pgparents_1 (cost=0.00..0.22 rows=11 width=32) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Materialize (cost=2.04..2.05 rows=1 width=16) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Sort (cost=2.04..2.05 rows=1 width=16) (actual rows= loops=)

  • Sort Key: ns.clientid
67. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..2.03 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (s.networksiteid = ns.networksiteid)
68. 0.000 0.000 ↓ 0.0

Seq Scan on site s (cost=0.00..1.01 rows=1 width=16) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Seq Scan on networksite ns (cost=0.00..1.01 rows=1 width=16) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Hash (cost=15.13..15.13 rows=1 width=16) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Subquery Scan on snetworkchild (cost=14.85..15.13 rows=1 width=16) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Hash Join (cost=14.85..15.12 rows=1 width=24) (actual rows= loops=)

  • Hash Cond: (siteparents.networksiteid = s_1.networksiteid)
73.          

CTE siteparents

74. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.00..13.83 rows=11 width=24) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Seq Scan on networksite ns_1 (cost=0.00..1.01 rows=1 width=24) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.02..1.26 rows=1 width=24) (actual rows= loops=)

  • Hash Cond: (siteparents_1.parentnetworksiteid = ns2.networksiteid)
77. 0.000 0.000 ↓ 0.0

WorkTable Scan on siteparents siteparents_1 (cost=0.00..0.20 rows=10 width=16) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Hash (cost=1.01..1.01 rows=1 width=24) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Seq Scan on networksite ns2 (cost=0.00..1.01 rows=1 width=24) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

CTE Scan on siteparents (cost=0.00..0.22 rows=11 width=16) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Hash (cost=1.01..1.01 rows=1 width=16) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Seq Scan on site s_1 (cost=0.00..1.01 rows=1 width=16) (actual rows= loops=)

83.          

SubPlan (for Hash Left Join)

84. 0.000 0.000 ↓ 0.0

CTE Scan on object_scope (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

  • Filter: (project_id IS NOT NULL)
85. 0.000 0.000 ↓ 0.0

CTE Scan on object_scope object_scope_1 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

  • Filter: (site_id IS NOT NULL)
86. 0.000 0.000 ↓ 0.0

CTE Scan on object_scope object_scope_2 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

  • Filter: (application_id IS NOT NULL)
87. 0.000 0.000 ↓ 0.0

CTE Scan on object_scope object_scope_3 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

  • Filter: (client_id IS NOT NULL)
88. 0.000 0.000 ↓ 0.0

Hash (cost=35.53..35.53 rows=1,153 width=26) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Seq Scan on columnmetadata cm (cost=0.00..35.53 rows=1,153 width=26) (actual rows= loops=)