explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 27BX

Settings
# exclusive inclusive rows x rows loops node
1. 96,612.426 559,962.922 ↑ 745.8 33 1

HashAggregate (cost=18,429.97..18,737.60 rows=24,610 width=38) (actual time=559,962.650..559,962.922 rows=33 loops=1)

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

CTE object_scope

3. 0.030 0.030 ↑ 1.0 1 1

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

  • Index Cond: (projectid = 6,342)
  • Heap Fetches: 1
4. 182,446.697 463,350.496 ↓ 41.0 21,047,532 1

Hash Join (cost=1,824.10..13,297.44 rows=512,824 width=38) (actual time=1,210.488..463,350.496 rows=21,047,532 loops=1)

  • Hash Cond: (rpa.column_id = cm.columnsequenceid)
5. 186,489.195 280,893.498 ↓ 41.0 21,047,532 1

Hash Left Join (cost=1,774.16..11,897.04 rows=512,824 width=28) (actual time=1,200.170..280,893.498 rows=21,047,532 loops=1)

  • 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. 92,183.710 94,403.782 ↓ 38.5 21,047,532 1

Hash Left Join (cost=1,758.92..8,736.49 rows=547,012 width=108) (actual time=1,199.614..94,403.782 rows=21,047,532 loops=1)

  • Hash Cond: (ucea.client_id = ag.clientid)
7. 404.290 2,211.167 ↓ 6.4 41,844 1

Hash Left Join (cost=1,724.56..1,845.45 rows=6,525 width=100) (actual time=1,190.692..2,211.167 rows=41,844 loops=1)

  • Hash Cond: (ucea.client_id = ns.clientid)
8. 410.931 1,806.665 ↓ 6.4 41,844 1

Merge Right Join (cost=1,722.52..1,802.62 rows=6,525 width=92) (actual time=1,190.453..1,806.665 rows=41,844 loops=1)

  • Merge Cond: (pppgpggroupchild.parent_project_group_uid = ucea.project_group_uid)
9. 5.661 33.931 ↑ 1,262.0 1 1

Sort (cost=277.86..281.01 rows=1,262 width=24) (actual time=33.927..33.931 rows=1 loops=1)

  • Sort Key: pppgpggroupchild.parent_project_group_uid
  • Sort Method: quicksort Memory: 148kB
10. 10.878 28.270 ↓ 1.0 1,274 1

Subquery Scan on pppgpggroupchild (cost=17.26..212.85 rows=1,262 width=24) (actual time=0.264..28.270 rows=1,274 loops=1)

11. 11.525 17.392 ↓ 1.0 1,274 1

Hash Join (cost=17.26..200.23 rows=1,262 width=32) (actual time=0.255..17.392 rows=1,274 loops=1)

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

CTE pgparents

13. 0.035 0.162 ↑ 7.3 3 1

Recursive Union (cost=0.00..16.55 rows=22 width=40) (actual time=0.021..0.162 rows=3 loops=1)

14. 0.028 0.028 ↓ 1.5 3 1

Seq Scan on projectgroup pg_2 (cost=0.00..1.02 rows=2 width=40) (actual time=0.010..0.028 rows=3 loops=1)

15. 0.025 0.099 ↓ 0.0 0 1

Hash Join (cost=1.04..1.51 rows=2 width=40) (actual time=0.094..0.099 rows=0 loops=1)

  • Hash Cond: (pgparents_1_2.parentuid = pg2_1.projectgroupuid)
16. 0.023 0.023 ↑ 6.7 3 1

WorkTable Scan on pgparents pgparents_1_2 (cost=0.00..0.40 rows=20 width=32) (actual time=0.005..0.023 rows=3 loops=1)

17. 0.024 0.051 ↓ 1.5 3 1

Hash (cost=1.02..1.02 rows=2 width=40) (actual time=0.046..0.051 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.027 0.027 ↓ 1.5 3 1

Seq Scan on projectgroup pg2_1 (cost=0.00..1.02 rows=2 width=40) (actual time=0.007..0.027 rows=3 loops=1)

19. 5.644 5.644 ↓ 1.0 1,274 1

Seq Scan on project p_1 (cost=0.00..165.62 rows=1,262 width=24) (actual time=0.012..5.644 rows=1,274 loops=1)

20. 0.023 0.223 ↑ 7.3 3 1

Hash (cost=0.44..0.44 rows=22 width=32) (actual time=0.219..0.223 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.200 0.200 ↑ 7.3 3 1

CTE Scan on pgparents (cost=0.00..0.44 rows=22 width=32) (actual time=0.032..0.200 rows=3 loops=1)

22. 395.848 1,361.803 ↓ 6.4 41,844 1

Sort (cost=1,444.66..1,460.97 rows=6,525 width=100) (actual time=1,156.503..1,361.803 rows=41,844 loops=1)

  • Sort Key: ucea.project_group_uid
  • Sort Method: quicksort Memory: 7,421kB
23. 361.205 965.955 ↓ 6.4 41,844 1

Merge Left Join (cost=973.81..1,031.24 rows=6,525 width=100) (actual time=426.882..965.955 rows=41,844 loops=1)

  • Merge Cond: (ucea.application_group_id = agroupchild.applicationgroupid)
24. 364.734 604.750 ↓ 6.4 41,844 1

Sort (cost=973.53..989.85 rows=6,525 width=100) (actual time=426.866..604.750 rows=41,844 loops=1)

  • Sort Key: ucea.application_group_id
  • Sort Method: quicksort Memory: 7,421kB
25. 188.148 240.016 ↓ 6.4 41,844 1

Hash Right Join (cost=294.24..560.12 rows=6,525 width=100) (actual time=34.418..240.016 rows=41,844 loops=1)

  • Hash Cond: (pg.clientid = ucea.client_id)
26. 11.713 17.586 ↓ 1.0 1,274 1

Hash Join (cost=1.04..177.00 rows=1,262 width=16) (actual time=0.116..17.586 rows=1,274 loops=1)

  • Hash Cond: (p.projectgroupuid = pg.projectgroupuid)
27. 5.820 5.820 ↓ 1.0 1,274 1

Seq Scan on project p (cost=0.00..165.62 rows=1,262 width=24) (actual time=0.012..5.820 rows=1,274 loops=1)

28. 0.025 0.053 ↓ 1.5 3 1

Hash (cost=1.02..1.02 rows=2 width=24) (actual time=0.049..0.053 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
29. 0.028 0.028 ↓ 1.5 3 1

Seq Scan on projectgroup pg (cost=0.00..1.02 rows=2 width=24) (actual time=0.010..0.028 rows=3 loops=1)

30. 0.181 34.282 ↓ 1.6 33 1

Hash (cost=292.94..292.94 rows=21 width=92) (actual time=34.278..34.282 rows=33 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
31. 1.949 34.101 ↓ 1.6 33 1

Nested Loop (cost=55.69..292.94 rows=21 width=92) (actual time=33.377..34.101 rows=33 loops=1)

  • Join Filter: (ucea.usergroup_id = ur.user_group_id)
  • Rows Removed by Join Filter: 363
32. 5.614 23.044 ↓ 1.2 12 1

Hash Right Join (cost=25.49..225.82 rows=10 width=72) (actual time=7.819..23.044 rows=12 loops=1)

  • Hash Cond: (p_2.projectid = ucea.project_id)
33. 11.446 17.285 ↓ 1.0 1,274 1

Hash Join (cost=17.26..200.23 rows=1,262 width=32) (actual time=0.254..17.285 rows=1,274 loops=1)

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

CTE pgparents

35. 0.046 0.168 ↑ 7.3 3 1

Recursive Union (cost=0.00..16.55 rows=22 width=40) (actual time=0.019..0.168 rows=3 loops=1)

36. 0.027 0.027 ↓ 1.5 3 1

Seq Scan on projectgroup pg_1 (cost=0.00..1.02 rows=2 width=40) (actual time=0.009..0.027 rows=3 loops=1)

37. 0.026 0.095 ↓ 0.0 0 1

Hash Join (cost=1.04..1.51 rows=2 width=40) (actual time=0.091..0.095 rows=0 loops=1)

  • Hash Cond: (pgparents_1_1.parentuid = pg2.projectgroupuid)
38. 0.022 0.022 ↑ 6.7 3 1

WorkTable Scan on pgparents pgparents_1_1 (cost=0.00..0.40 rows=20 width=32) (actual time=0.005..0.022 rows=3 loops=1)

39. 0.023 0.047 ↓ 1.5 3 1

Hash (cost=1.02..1.02 rows=2 width=40) (actual time=0.042..0.047 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
40. 0.024 0.024 ↓ 1.5 3 1

Seq Scan on projectgroup pg2 (cost=0.00..1.02 rows=2 width=40) (actual time=0.007..0.024 rows=3 loops=1)

41. 5.612 5.612 ↓ 1.0 1,274 1

Seq Scan on project p_2 (cost=0.00..165.62 rows=1,262 width=24) (actual time=0.008..5.612 rows=1,274 loops=1)

42. 0.022 0.227 ↑ 7.3 3 1

Hash (cost=0.44..0.44 rows=22 width=16) (actual time=0.223..0.227 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
43. 0.205 0.205 ↑ 7.3 3 1

CTE Scan on pgparents pgparents_1 (cost=0.00..0.44 rows=22 width=16) (actual time=0.029..0.205 rows=3 loops=1)

44. 0.064 0.145 ↓ 1.2 12 1

Hash (cost=8.10..8.10 rows=10 width=72) (actual time=0.141..0.145 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
45. 0.081 0.081 ↓ 1.2 12 1

Seq Scan on usergroup_client_entities_association ucea (cost=0.00..8.10 rows=10 width=72) (actual time=0.013..0.081 rows=12 loops=1)

46. 1.888 9.108 ↓ 5.5 33 12

Materialize (cost=30.20..66.23 rows=6 width=36) (actual time=0.181..0.759 rows=33 loops=12)

47. 0.428 7.220 ↓ 5.5 33 1

Nested Loop (cost=30.20..66.20 rows=6 width=36) (actual time=2.109..7.220 rows=33 loops=1)

48. 1.623 6.594 ↓ 5.5 33 1

Hash Join (cost=30.06..63.14 rows=6 width=44) (actual time=2.068..6.594 rows=33 loops=1)

  • Hash Cond: (rpa.column_id = cm_1.columnsequenceid)
49. 2.905 4.519 ↓ 2.3 338 1

Nested Loop (cost=0.27..32.97 rows=148 width=17) (actual time=0.079..4.519 rows=338 loops=1)

50. 0.028 0.028 ↑ 1.0 2 1

Seq Scan on users_roles ur (cost=0.00..1.07 rows=2 width=16) (actual time=0.013..0.028 rows=2 loops=1)

  • Filter: (user_id = 71)
  • Rows Removed by Filter: 9
51. 1.586 1.586 ↓ 2.9 169 2

Index Only Scan using role_permissions_association_role_id_incl_workbench on role_permissions_association rpa (cost=0.27..15.36 rows=59 width=17) (actual time=0.018..0.793 rows=169 loops=2)

  • Index Cond: (role_id = ur.role_id)
  • Filter: ((update_flag IS TRUE) OR (read_flag IS TRUE))
  • Rows Removed by Filter: 14
  • Heap Fetches: 196
52. 0.202 0.452 ↑ 1.1 42 1

Hash (cost=29.21..29.21 rows=46 width=27) (actual time=0.448..0.452 rows=42 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
53. 0.227 0.250 ↑ 1.1 42 1

Bitmap Heap Scan on columnmetadata cm_1 (cost=4.63..29.21 rows=46 width=27) (actual time=0.035..0.250 rows=42 loops=1)

  • Recheck Cond: ((model)::text = 'projects'::text)
  • Heap Blocks: exact=16
54. 0.023 0.023 ↓ 1.1 49 1

Bitmap Index Scan on columnmetadata_model_index (cost=0.00..4.62 rows=46 width=0) (actual time=0.019..0.023 rows=49 loops=1)

  • Index Cond: ((model)::text = 'projects'::text)
55. 0.198 0.198 ↑ 1.0 1 33

Index Only Scan using tablemetadata_pkey on tablemetadata tm (cost=0.14..0.51 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=33)

  • Index Cond: (tablesequenceid = cm_1.tableid)
  • Heap Fetches: 33
56. 0.000 0.000 ↓ 0.0 0

Index Only Scan using application_applicationgroupid_incl on application agroupchild (cost=0.27..23.82 rows=503 width=16) (never executed)

  • Heap Fetches: 0
57. 0.029 0.212 ↓ 3.0 3 1

Hash (cost=2.03..2.03 rows=1 width=16) (actual time=0.208..0.212 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
58. 0.090 0.183 ↓ 4.0 4 1

Nested Loop (cost=0.00..2.03 rows=1 width=16) (actual time=0.046..0.183 rows=4 loops=1)

  • Join Filter: (s.networksiteid = ns.networksiteid)
  • Rows Removed by Join Filter: 6
59. 0.041 0.041 ↓ 4.0 4 1

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

60. 0.052 0.052 ↓ 2.0 2 4

Seq Scan on networksite ns (cost=0.00..1.01 rows=1 width=16) (actual time=0.005..0.013 rows=2 loops=4)

61. 2.232 8.905 ↑ 1.0 503 1

Hash (cost=28.07..28.07 rows=503 width=16) (actual time=8.901..8.905 rows=503 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 32kB
62. 4.368 6.673 ↑ 1.0 503 1

Hash Join (cost=1.34..28.07 rows=503 width=16) (actual time=0.099..6.673 rows=503 loops=1)

  • Hash Cond: (a.applicationgroupid = ag.applicationgroupid)
63. 2.254 2.254 ↑ 1.0 503 1

Index Only Scan using application_applicationgroupid_incl on application a (cost=0.27..23.82 rows=503 width=16) (actual time=0.032..2.254 rows=503 loops=1)

  • Heap Fetches: 0
64. 0.023 0.051 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=16) (actual time=0.047..0.051 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
65. 0.028 0.028 ↑ 1.0 3 1

Seq Scan on applicationgroup ag (cost=0.00..1.03 rows=3 width=16) (actual time=0.011..0.028 rows=3 loops=1)

66. 0.027 0.424 ↓ 4.0 4 1

Hash (cost=15.13..15.13 rows=1 width=16) (actual time=0.420..0.424 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
67. 0.043 0.397 ↓ 4.0 4 1

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

68. 0.057 0.354 ↓ 4.0 4 1

Hash Join (cost=14.85..15.12 rows=1 width=24) (actual time=0.109..0.354 rows=4 loops=1)

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

CTE siteparents

70. 0.044 0.193 ↑ 2.8 4 1

Recursive Union (cost=0.00..13.83 rows=11 width=24) (actual time=0.023..0.193 rows=4 loops=1)

71. 0.034 0.034 ↓ 4.0 4 1

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

72. 0.031 0.115 ↓ 0.0 0 1

Hash Join (cost=1.02..1.26 rows=1 width=24) (actual time=0.110..0.115 rows=0 loops=1)

  • Hash Cond: (siteparents_1.parentnetworksiteid = ns2.networksiteid)
73. 0.028 0.028 ↑ 2.5 4 1

WorkTable Scan on siteparents siteparents_1 (cost=0.00..0.20 rows=10 width=16) (actual time=0.007..0.028 rows=4 loops=1)

74. 0.028 0.056 ↓ 4.0 4 1

Hash (cost=1.01..1.01 rows=1 width=24) (actual time=0.052..0.056 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
75. 0.028 0.028 ↓ 4.0 4 1

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

76. 0.237 0.237 ↑ 2.8 4 1

CTE Scan on siteparents (cost=0.00..0.22 rows=11 width=16) (actual time=0.033..0.237 rows=4 loops=1)

77. 0.030 0.060 ↓ 4.0 4 1

Hash (cost=1.01..1.01 rows=1 width=16) (actual time=0.055..0.060 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
78. 0.030 0.030 ↓ 4.0 4 1

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

79.          

SubPlan (for Hash Left Join)

80. 0.049 0.049 ↑ 1.0 1 1

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

  • Filter: (project_id IS NOT NULL)
81. 0.009 0.009 ↓ 0.0 0 1

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

  • Filter: (site_id IS NOT NULL)
  • Rows Removed by Filter: 1
82. 0.009 0.009 ↓ 0.0 0 1

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

  • Filter: (application_id IS NOT NULL)
  • Rows Removed by Filter: 1
83. 0.030 0.030 ↑ 1.0 1 1

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

  • Filter: (client_id IS NOT NULL)
84. 5.137 10.301 ↑ 1.0 1,124 1

Hash (cost=35.53..35.53 rows=1,153 width=26) (actual time=10.297..10.301 rows=1,124 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 84kB
85. 5.164 5.164 ↑ 1.0 1,124 1

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

Planning time : 13.123 ms
Execution time : 559,964.283 ms