explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W3ZU

Settings
# exclusive inclusive rows x rows loops node
1. 96,687.920 737,065.630 ↑ 448.0 33 1

HashAggregate (cost=2,206.86..2,391.64 rows=14,783 width=38) (actual time=737,065.358..737,065.630 rows=33 loops=1)

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

CTE object_scope

3. 0.033 0.033 ↑ 1.0 1 1

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

  • Index Cond: (projectid = 6,342)
  • Heap Fetches: 1
4. 182,145.825 640,377.710 ↓ 1,415.9 20,931,372 1

Hash Join (cost=1,654.75..2,050.73 rows=14,783 width=38) (actual time=2,704.468..640,377.710 rows=20,931,372 loops=1)

  • Hash Cond: (rpa.column_id = cm.columnsequenceid)
5. 184,600.838 458,221.767 ↓ 1,415.9 20,931,372 1

Hash Left Join (cost=1,604.81..1,961.86 rows=14,783 width=28) (actual time=2,694.331..458,221.767 rows=20,931,372 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))
  • Rows Removed by Filter: 41,679
6. 180,469.109 273,620.494 ↓ 1,330.0 20,973,051 1

Merge Right Join (cost=1,589.58..1,886.87 rows=15,769 width=108) (actual time=2,693.845..273,620.494 rows=20,973,051 loops=1)

  • Merge Cond: (ag.clientid = ucea.client_id)
7. 7.683 13.317 ↑ 1.0 503 1

Nested Loop (cost=0.12..32.46 rows=503 width=16) (actual time=0.046..13.317 rows=503 loops=1)

  • Join Filter: (a.applicationgroupid = ag.applicationgroupid)
  • Rows Removed by Join Filter: 503
8. 0.030 0.030 ↓ 2.0 2 1

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

9. 5.604 5.604 ↑ 1.0 503 2

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

10. 89,021.903 93,138.068 ↓ 3,927.5 20,973,051 1

Materialize (cost=1,589.45..1,629.97 rows=5,340 width=100) (actual time=2,693.781..93,138.068 rows=20,973,051 loops=1)

11. 895.014 4,116.165 ↓ 15.6 83,325 1

Merge Left Join (cost=1,589.45..1,616.62 rows=5,340 width=100) (actual time=2,693.770..4,116.165 rows=83,325 loops=1)

  • Merge Cond: (ucea.client_id = ns.clientid)
12. 726.637 3,045.428 ↓ 15.6 83,325 1

Sort (cost=1,587.41..1,600.76 rows=5,340 width=92) (actual time=2,693.573..3,045.428 rows=83,325 loops=1)

  • Sort Key: ucea.client_id
  • Sort Method: quicksort Memory: 14,156kB
13. 707.832 2,318.791 ↓ 15.6 83,325 1

Merge Left Join (cost=1,222.47..1,256.79 rows=5,340 width=92) (actual time=1,260.663..2,318.791 rows=83,325 loops=1)

  • Merge Cond: (ucea.project_group_uid = pppgpggroupchild.parent_project_group_uid)
14. 364.757 1,403.316 ↓ 7.8 41,745 1

Sort (cost=1,074.78..1,088.13 rows=5,340 width=100) (actual time=1,227.524..1,403.316 rows=41,745 loops=1)

  • Sort Key: ucea.project_group_uid
  • Sort Method: quicksort Memory: 7,403kB
15. 355.953 1,038.559 ↓ 7.8 41,745 1

Merge Left Join (cost=714.94..744.16 rows=5,340 width=100) (actual time=507.157..1,038.559 rows=41,745 loops=1)

  • Merge Cond: (ucea.application_group_id = agroupchild.applicationgroupid)
16. 360.226 682.606 ↓ 7.8 41,745 1

Sort (cost=674.34..687.69 rows=5,340 width=100) (actual time=507.134..682.606 rows=41,745 loops=1)

  • Sort Key: ucea.application_group_id
  • Sort Method: quicksort Memory: 7,403kB
17. 195.843 322.380 ↓ 7.8 41,745 1

Hash Join (cost=188.28..343.72 rows=5,340 width=100) (actual time=45.281..322.380 rows=41,745 loops=1)

  • Hash Cond: (ur.role_id = rpa.role_id)
18. 37.922 119.153 ↓ 4.0 3,795 1

Merge Right Join (cost=118.96..198.72 rows=946 width=80) (actual time=37.846..119.153 rows=3,795 loops=1)

  • Merge Cond: (pg.clientid = ucea.client_id)
19. 16.547 27.499 ↓ 1.0 1,262 1

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

  • Join Filter: (p.projectgroupuid = pg.projectgroupuid)
  • Rows Removed by Join Filter: 1,239
20. 0.022 0.022 ↓ 2.0 2 1

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

21. 10.930 10.930 ↑ 1.0 1,250 2

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

22. 16.019 53.732 ↓ 29.6 3,795 1

Sort (cost=118.83..119.15 rows=128 width=72) (actual time=37.791..53.732 rows=3,795 loops=1)

  • Sort Key: ucea.client_id
  • Sort Method: quicksort Memory: 25kB
23. 0.142 37.713 ↑ 8.5 15 1

Hash Left Join (cost=99.56..114.35 rows=128 width=72) (actual time=33.160..37.713 rows=15 loops=1)

  • Hash Cond: (ucea.project_id = pppg.project_id)
24. 2.264 4.523 ↑ 8.5 15 1

Hash Join (cost=1.05..15.36 rows=128 width=72) (actual time=0.095..4.523 rows=15 loops=1)

  • Hash Cond: (ucea.usergroup_id = ur.user_group_id)
25. 2.209 2.209 ↑ 1.0 511 1

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

26. 0.022 0.050 ↓ 3.0 3 1

Hash (cost=1.04..1.04 rows=1 width=16) (actual time=0.046..0.050 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 0.028 0.028 ↓ 3.0 3 1

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

  • Filter: (user_id = 3)
  • Rows Removed by Filter: 3
28. 5.547 33.048 ↓ 1.0 1,262 1

Hash (cost=82.75..82.75 rows=1,261 width=8) (actual time=33.044..33.048 rows=1,262 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 66kB
29. 10.671 27.501 ↓ 1.0 1,262 1

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

30. 11.077 16.830 ↓ 1.0 1,262 1

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

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

CTE pgparents

32. 0.026 0.126 ↑ 5.5 2 1

Recursive Union (cost=0.00..13.83 rows=11 width=40) (actual time=0.018..0.126 rows=2 loops=1)

33. 0.021 0.021 ↓ 2.0 2 1

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

34. 0.023 0.079 ↓ 0.0 0 1

Hash Join (cost=1.02..1.26 rows=1 width=40) (actual time=0.074..0.079 rows=0 loops=1)

  • Hash Cond: (pgparents_1_1.parentuid = pg2.projectgroupuid)
35. 0.018 0.018 ↑ 5.0 2 1

WorkTable Scan on pgparents pgparents_1_1 (cost=0.00..0.20 rows=10 width=32) (actual time=0.005..0.018 rows=2 loops=1)

36. 0.018 0.038 ↓ 2.0 2 1

Hash (cost=1.01..1.01 rows=1 width=40) (actual time=0.034..0.038 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
37. 0.020 0.020 ↓ 2.0 2 1

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

38. 5.577 5.577 ↓ 1.0 1,262 1

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

39. 0.019 0.176 ↑ 5.5 2 1

Hash (cost=0.22..0.22 rows=11 width=16) (actual time=0.172..0.176 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
40. 0.157 0.157 ↑ 5.5 2 1

CTE Scan on pgparents (cost=0.00..0.22 rows=11 width=16) (actual time=0.031..0.157 rows=2 loops=1)

41. 0.303 7.384 ↓ 2.9 66 1

Hash (cost=69.03..69.03 rows=23 width=36) (actual time=7.380..7.384 rows=66 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
42. 0.578 7.081 ↓ 2.9 66 1

Hash Join (cost=33.47..69.03 rows=23 width=36) (actual time=3.070..7.081 rows=66 loops=1)

  • Hash Cond: (cm_1.tableid = tm.tablesequenceid)
43. 2.734 5.799 ↓ 2.9 66 1

Hash Join (cost=29.78..65.28 rows=23 width=44) (actual time=2.350..5.799 rows=66 loops=1)

  • Hash Cond: (rpa.column_id = cm_1.columnsequenceid)
44. 2.623 2.623 ↓ 1.0 567 1

Seq Scan on role_permissions_association rpa (cost=0.00..34.01 rows=566 width=17) (actual time=0.013..2.623 rows=567 loops=1)

  • Filter: (((update_flag IS TRUE) OR (read_flag IS TRUE)) AND (module_id = 2))
  • Rows Removed by Filter: 474
45. 0.206 0.442 ↑ 1.1 42 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
46. 0.212 0.236 ↑ 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.236 rows=42 loops=1)

  • Recheck Cond: ((model)::text = 'projects'::text)
  • Heap Blocks: exact=16
47. 0.024 0.024 ↑ 1.1 43 1

Bitmap Index Scan on columnmetadata_model_index (cost=0.00..4.62 rows=46 width=0) (actual time=0.020..0.024 rows=43 loops=1)

  • Index Cond: ((model)::text = 'projects'::text)
48. 0.344 0.704 ↓ 1.0 78 1

Hash (cost=2.75..2.75 rows=75 width=8) (actual time=0.700..0.704 rows=78 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
49. 0.360 0.360 ↓ 1.0 78 1

Seq Scan on tablemetadata tm (cost=0.00..2.75 rows=75 width=8) (actual time=0.012..0.360 rows=78 loops=1)

50. 0.000 0.000 ↓ 0.0 0

Sort (cost=40.60..41.86 rows=503 width=16) (never executed)

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

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

52. 180.068 207.643 ↓ 33.0 41,614 1

Sort (cost=147.69..150.84 rows=1,261 width=24) (actual time=33.114..207.643 rows=41,614 loops=1)

  • Sort Key: pppgpggroupchild.parent_project_group_uid
  • Sort Method: quicksort Memory: 147kB
53. 10.745 27.575 ↓ 1.0 1,262 1

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

54. 11.145 16.830 ↓ 1.0 1,262 1

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

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

CTE pgparents

56. 0.027 0.131 ↑ 5.5 2 1

Recursive Union (cost=0.00..13.83 rows=11 width=40) (actual time=0.021..0.131 rows=2 loops=1)

57. 0.024 0.024 ↓ 2.0 2 1

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

58. 0.022 0.080 ↓ 0.0 0 1

Hash Join (cost=1.02..1.26 rows=1 width=40) (actual time=0.076..0.080 rows=0 loops=1)

  • Hash Cond: (pgparents_1_2.parentuid = pg2_1.projectgroupuid)
59. 0.019 0.019 ↑ 5.0 2 1

WorkTable Scan on pgparents pgparents_1_2 (cost=0.00..0.20 rows=10 width=32) (actual time=0.006..0.019 rows=2 loops=1)

60. 0.019 0.039 ↓ 2.0 2 1

Hash (cost=1.01..1.01 rows=1 width=40) (actual time=0.034..0.039 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
61. 0.020 0.020 ↓ 2.0 2 1

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

62. 5.507 5.507 ↓ 1.0 1,262 1

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

63. 0.019 0.178 ↑ 5.5 2 1

Hash (cost=0.22..0.22 rows=11 width=32) (actual time=0.174..0.178 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
64. 0.159 0.159 ↑ 5.5 2 1

CTE Scan on pgparents pgparents_1 (cost=0.00..0.22 rows=11 width=32) (actual time=0.032..0.159 rows=2 loops=1)

65. 175.551 175.723 ↓ 41,614.0 41,614 1

Materialize (cost=2.04..2.05 rows=1 width=16) (actual time=0.177..175.723 rows=41,614 loops=1)

66. 0.036 0.172 ↓ 2.0 2 1

Sort (cost=2.04..2.05 rows=1 width=16) (actual time=0.163..0.172 rows=2 loops=1)

  • Sort Key: ns.clientid
  • Sort Method: quicksort Memory: 25kB
67. 0.066 0.136 ↓ 3.0 3 1

Nested Loop (cost=0.00..2.03 rows=1 width=16) (actual time=0.047..0.136 rows=3 loops=1)

  • Join Filter: (s.networksiteid = ns.networksiteid)
  • Rows Removed by Join Filter: 3
68. 0.037 0.037 ↓ 3.0 3 1

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

69. 0.033 0.033 ↓ 2.0 2 3

Seq Scan on networksite ns (cost=0.00..1.01 rows=1 width=16) (actual time=0.006..0.011 rows=2 loops=3)

70. 0.023 0.336 ↓ 3.0 3 1

Hash (cost=15.13..15.13 rows=1 width=16) (actual time=0.332..0.336 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
71. 0.034 0.313 ↓ 3.0 3 1

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

72. 0.033 0.279 ↓ 3.0 3 1

Hash Join (cost=14.85..15.12 rows=1 width=24) (actual time=0.090..0.279 rows=3 loops=1)

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

CTE siteparents

74. 0.034 0.154 ↑ 3.7 3 1

Recursive Union (cost=0.00..13.83 rows=11 width=24) (actual time=0.017..0.154 rows=3 loops=1)

75. 0.025 0.025 ↓ 3.0 3 1

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

76. 0.026 0.095 ↓ 0.0 0 1

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

  • Hash Cond: (siteparents_1.parentnetworksiteid = ns2.networksiteid)
77. 0.023 0.023 ↑ 3.3 3 1

WorkTable Scan on siteparents siteparents_1 (cost=0.00..0.20 rows=10 width=16) (actual time=0.006..0.023 rows=3 loops=1)

78. 0.022 0.046 ↓ 3.0 3 1

Hash (cost=1.01..1.01 rows=1 width=24) (actual time=0.042..0.046 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
79. 0.024 0.024 ↓ 3.0 3 1

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

80. 0.189 0.189 ↑ 3.7 3 1

CTE Scan on siteparents (cost=0.00..0.22 rows=11 width=16) (actual time=0.026..0.189 rows=3 loops=1)

81. 0.033 0.057 ↓ 3.0 3 1

Hash (cost=1.01..1.01 rows=1 width=16) (actual time=0.043..0.057 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
82. 0.024 0.024 ↓ 3.0 3 1

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

83.          

SubPlan (for Hash Left Join)

84. 0.066 0.066 ↑ 1.0 1 1

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

  • Filter: (project_id IS NOT NULL)
85. 0.010 0.010 ↓ 0.0 0 1

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

  • Filter: (site_id IS NOT NULL)
  • Rows Removed by Filter: 1
86. 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
87. 0.014 0.014 ↑ 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.014 rows=1 loops=1)

  • Filter: (client_id IS NOT NULL)
88. 5.044 10.118 ↑ 1.0 1,124 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 84kB
89. 5.074 5.074 ↑ 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.074 rows=1,124 loops=1)

Planning time : 12.534 ms
Execution time : 737,068.237 ms