explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cn35

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 36,880.951 ↑ 1.7 6 1

Limit (cost=579,824.37..579,847.50 rows=10 width=99) (actual time=36,872.946..36,880.951 rows=6 loops=1)

2. 6.554 36,880.948 ↑ 116.3 6 1

Unique (cost=579,824.37..581,438.52 rows=698 width=99) (actual time=36,872.945..36,880.948 rows=6 loops=1)

3. 28.888 36,874.394 ↑ 2.7 26,880 1

Sort (cost=579,824.37..580,003.72 rows=71,740 width=99) (actual time=36,872.943..36,874.394 rows=26,880 loops=1)

  • Sort Key: u.id, u.first_name, u.last_name, u.email, (concat(u.first_name, ' ', u.last_name, ' - ', u.email)), u.color, u.profile_image, u.job_title
  • Sort Method: quicksort Memory: 5,029kB
4. 17.027 36,845.506 ↑ 2.7 26,880 1

Hash Join (cost=480,328.14..574,038.37 rows=71,740 width=99) (actual time=36,814.901..36,845.506 rows=26,880 loops=1)

  • Hash Cond: (ur.user_id = u_1.user_id)
5. 9.798 36,823.418 ↑ 52.7 82,656 1

Merge Right Join (cost=479,850.59..545,442.40 rows=4,352,225 width=8) (actual time=36,809.513..36,823.418 rows=82,656 loops=1)

  • Merge Cond: (snetworkchild.parent_networksite_id = ucea.networksite_id)
6. 0.074 0.844 ↑ 61,698.0 1 1

Sort (cost=10,554.20..10,708.45 rows=61,698 width=8) (actual time=0.844..0.844 rows=1 loops=1)

  • Sort Key: snetworkchild.parent_networksite_id
  • Sort Method: quicksort Memory: 50kB
7. 0.049 0.770 ↑ 112.4 549 1

Subquery Scan on snetworkchild (cost=2,637.45..5,645.22 rows=61,698 width=8) (actual time=0.163..0.770 rows=549 loops=1)

8. 0.124 0.721 ↑ 112.4 549 1

Hash Join (cost=2,637.45..5,028.24 rows=61,698 width=24) (actual time=0.163..0.721 rows=549 loops=1)

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

CTE siteparents

10. 0.065 0.350 ↑ 109.8 517 1

Recursive Union (cost=0.00..2,616.95 rows=56,762 width=24) (actual time=0.008..0.350 rows=517 loops=1)

11. 0.083 0.083 ↑ 1.1 517 1

Seq Scan on networksite ns_1 (cost=0.00..12.62 rows=562 width=24) (actual time=0.006..0.083 rows=517 loops=1)

12. 0.041 0.202 ↓ 0.0 0 1

Hash Join (cost=19.65..146.91 rows=5,620 width=24) (actual time=0.201..0.202 rows=0 loops=1)

  • Hash Cond: (siteparents_1.parentnetworksiteid = ns2.networksiteid)
13. 0.027 0.027 ↑ 10.9 517 1

WorkTable Scan on siteparents siteparents_1 (cost=0.00..112.40 rows=5,620 width=16) (actual time=0.001..0.027 rows=517 loops=1)

14. 0.070 0.134 ↑ 1.1 517 1

Hash (cost=12.62..12.62 rows=562 width=24) (actual time=0.134..0.134 rows=517 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 33kB
15. 0.064 0.064 ↑ 1.1 517 1

Seq Scan on networksite ns2 (cost=0.00..12.62 rows=562 width=24) (actual time=0.004..0.064 rows=517 loops=1)

16. 0.453 0.453 ↑ 109.8 517 1

CTE Scan on siteparents (cost=0.00..1,135.24 rows=56,762 width=16) (actual time=0.009..0.453 rows=517 loops=1)

17. 0.059 0.144 ↑ 1.1 549 1

Hash (cost=13.00..13.00 rows=600 width=8) (actual time=0.144..0.144 rows=549 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
18. 0.085 0.085 ↑ 1.1 549 1

Seq Scan on site s_1 (cost=0.00..13.00 rows=600 width=8) (actual time=0.005..0.085 rows=549 loops=1)

19. 12.325 36,812.776 ↑ 1.3 82,656 1

Sort (cost=469,296.39..469,563.83 rows=106,975 width=16) (actual time=36,808.666..36,812.776 rows=82,656 loops=1)

  • Sort Key: ucea.networksite_id
  • Sort Method: quicksort Memory: 6,947kB
20. 9.483 36,800.451 ↑ 1.3 82,656 1

Hash Left Join (cost=84,828.01..460,360.28 rows=106,975 width=16) (actual time=26,686.638..36,800.451 rows=82,656 loops=1)

  • Hash Cond: (ucea.project_id = pppg.project_id)
21. 4,761.556 36,786.858 ↑ 1.3 82,656 1

Merge Left Join (cost=60,913.23..435,647.55 rows=106,975 width=24) (actual time=26,682.123..36,786.858 rows=82,656 loops=1)

  • Merge Cond: (ucea.project_group_uid = pppgpggroupchild.parent_project_group_uid)
  • Filter: (COALESCE(ucea.project_id, p.projectid, pppgpggroupchild.project_id) = 409)
  • Rows Removed by Filter: 50,209,742
22. 13,994.743 32,019.316 ↓ 782.0 50,269,334 1

Sort (cost=9,844.71..10,005.41 rows=64,280 width=48) (actual time=26,567.382..32,019.316 rows=50,269,334 loops=1)

  • Sort Key: ucea.project_group_uid
  • Sort Method: external merge Disk: 1,279,032kB
23. 6,374.788 18,024.573 ↓ 782.0 50,269,334 1

Hash Left Join (cost=2,364.23..4,711.28 rows=64,280 width=48) (actual time=603.062..18,024.573 rows=50,269,334 loops=1)

  • Hash Cond: (ucea.client_id = ag.clientid)
24. 6,518.683 11,649.484 ↓ 561.5 36,092,924 1

Hash Left Join (cost=2,332.82..4,349.31 rows=64,280 width=56) (actual time=602.756..11,649.484 rows=36,092,924 loops=1)

  • Hash Cond: (ucea.client_id = ns.clientid)
25. 3,787.968 5,130.398 ↓ 558.9 35,927,422 1

Hash Join (cost=2,291.09..3,815.72 rows=64,280 width=56) (actual time=602.339..5,130.398 rows=35,927,422 loops=1)

  • Hash Cond: (ur.role_id = rpa.role_id)
26. 425.558 1,341.140 ↓ 109.0 2,582,775 1

Merge Join (cost=1,533.52..2,088.98 rows=23,702 width=64) (actual time=601.040..1,341.140 rows=2,582,775 loops=1)

  • Merge Cond: (ur.user_group_id = ucea.usergroup_id)
27. 6.501 6.501 ↑ 1.1 4,750 1

Index Scan using users_roles_user_group_id_index on users_roles ur (cost=0.28..215.35 rows=5,134 width=24) (actual time=0.009..6.501 rows=4,750 loops=1)

28. 647.865 909.081 ↓ 349.8 2,587,913 1

Sort (cost=1,533.23..1,551.73 rows=7,399 width=56) (actual time=601.024..909.081 rows=2,587,913 loops=1)

  • Sort Key: ucea.usergroup_id
  • Sort Method: external sort Disk: 31,704kB
29. 100.595 261.216 ↓ 115.3 853,304 1

Hash Left Join (cost=328.40..1,057.73 rows=7,399 width=56) (actual time=2.012..261.216 rows=853,304 loops=1)

  • Hash Cond: (ucea.application_group_id = agroupchild.applicationgroupid)
30. 115.355 160.521 ↓ 115.3 853,178 1

Merge Left Join (cost=315.40..718.38 rows=7,399 width=64) (actual time=1.907..160.521 rows=853,178 loops=1)

  • Merge Cond: (ucea.client_id = pg.clientid)
31. 1.700 1.700 ↑ 1.0 6,293 1

Index Scan using usergroup_client_entities_association_client_id_index on usergroup_client_entities_association ucea (cost=0.28..276.01 rows=6,506 width=56) (actual time=0.006..1.700 rows=6,293 loops=1)

32. 42.004 43.466 ↓ 297.6 849,037 1

Sort (cost=315.11..322.25 rows=2,853 width=16) (actual time=1.896..43.466 rows=849,037 loops=1)

  • Sort Key: pg.clientid
  • Sort Method: quicksort Memory: 228kB
33. 0.932 1.462 ↑ 1.0 2,800 1

Hash Join (cost=29.31..151.38 rows=2,853 width=16) (actual time=0.214..1.462 rows=2,800 loops=1)

  • Hash Cond: (p.projectgroupuid = pg.projectgroupuid)
34. 0.330 0.330 ↑ 1.0 2,800 1

Seq Scan on project p (cost=0.00..114.53 rows=2,853 width=24) (actual time=0.006..0.330 rows=2,800 loops=1)

35. 0.091 0.200 ↑ 1.0 713 1

Hash (cost=20.25..20.25 rows=725 width=24) (actual time=0.200..0.200 rows=713 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
36. 0.109 0.109 ↑ 1.0 713 1

Seq Scan on projectgroup pg (cost=0.00..20.25 rows=725 width=24) (actual time=0.005..0.109 rows=713 loops=1)

37. 0.032 0.100 ↓ 1.1 284 1

Hash (cost=9.67..9.67 rows=267 width=8) (actual time=0.100..0.100 rows=284 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
38. 0.068 0.068 ↓ 1.1 284 1

Seq Scan on application agroupchild (cost=0.00..9.67 rows=267 width=8) (actual time=0.008..0.068 rows=284 loops=1)

39. 0.258 1.290 ↓ 4.3 2,117 1

Hash (cost=751.42..751.42 rows=492 width=8) (actual time=1.290..1.290 rows=2,117 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 115kB
40. 0.202 1.032 ↓ 4.4 2,159 1

Nested Loop (cost=23.85..751.42 rows=492 width=8) (actual time=0.174..1.032 rows=2,159 loops=1)

41. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on tablemetadata t (cost=0.00..2.98 rows=1 width=8) (actual time=0.011..0.016 rows=1 loops=1)

  • Filter: ((modelname)::text = 'projects'::text)
  • Rows Removed by Filter: 77
42. 0.679 0.814 ↓ 5.0 2,159 1

Bitmap Heap Scan on role_permissions_association rpa (cost=23.85..744.11 rows=434 width=16) (actual time=0.159..0.814 rows=2,159 loops=1)

  • Recheck Cond: ((entity_id = t.tablesequenceid) AND (module_id = 1) AND ((update_flag IS TRUE) OR (read_flag IS TRUE)))
  • Filter: (read_flag IS TRUE)
  • Heap Blocks: exact=188
43. 0.135 0.135 ↓ 5.3 2,369 1

Bitmap Index Scan on role_permissions_association_entity_id_role_id_column_workbench (cost=0.00..23.74 rows=444 width=0) (actual time=0.135..0.135 rows=2,369 loops=1)

  • Index Cond: (entity_id = t.tablesequenceid)
44. 0.062 0.403 ↑ 1.1 548 1

Hash (cost=34.23..34.23 rows=600 width=8) (actual time=0.403..0.403 rows=548 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
45. 0.129 0.341 ↑ 1.1 549 1

Hash Join (cost=19.65..34.23 rows=600 width=8) (actual time=0.175..0.341 rows=549 loops=1)

  • Hash Cond: (s.networksiteid = ns.networksiteid)
46. 0.056 0.056 ↑ 1.1 549 1

Seq Scan on site s (cost=0.00..13.00 rows=600 width=8) (actual time=0.013..0.056 rows=549 loops=1)

47. 0.063 0.156 ↑ 1.1 517 1

Hash (cost=12.62..12.62 rows=562 width=16) (actual time=0.156..0.156 rows=517 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 33kB
48. 0.093 0.093 ↑ 1.1 517 1

Seq Scan on networksite ns (cost=0.00..12.62 rows=562 width=16) (actual time=0.007..0.093 rows=517 loops=1)

49. 0.032 0.301 ↓ 1.1 284 1

Hash (cost=28.07..28.07 rows=267 width=8) (actual time=0.301..0.301 rows=284 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
50. 0.078 0.269 ↓ 1.1 284 1

Hash Join (cost=17.69..28.07 rows=267 width=8) (actual time=0.169..0.269 rows=284 loops=1)

  • Hash Cond: (a.applicationgroupid = ag.applicationgroupid)
51. 0.032 0.032 ↓ 1.1 284 1

Seq Scan on application a (cost=0.00..9.67 rows=267 width=8) (actual time=0.005..0.032 rows=284 loops=1)

52. 0.061 0.159 ↓ 1.1 510 1

Hash (cost=11.75..11.75 rows=475 width=16) (actual time=0.159..0.159 rows=510 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 32kB
53. 0.098 0.098 ↓ 1.1 510 1

Seq Scan on applicationgroup ag (cost=0.00..11.75 rows=475 width=16) (actual time=0.005..0.098 rows=510 loops=1)

54. 2.366 5.986 ↑ 9.4 36,335 1

Sort (cost=51,068.52..51,923.31 rows=341,916 width=24) (actual time=4.205..5.986 rows=36,335 loops=1)

  • Sort Key: pppgpggroupchild.parent_project_group_uid
  • Sort Method: quicksort Memory: 319kB
55. 0.240 3.620 ↑ 120.3 2,842 1

Subquery Scan on pppgpggroupchild (cost=11,078.67..19,640.83 rows=341,916 width=24) (actual time=2.351..3.620 rows=2,842 loops=1)

56. 0.726 3.380 ↑ 120.3 2,842 1

Merge Join (cost=11,078.67..16,221.67 rows=341,916 width=32) (actual time=2.350..3.380 rows=2,842 loops=1)

  • Merge Cond: (p_1.projectgroupuid = pgparents.projectgroupuid)
57.          

CTE pgparents

58. 0.118 0.589 ↑ 85.5 856 1

Recursive Union (cost=0.00..3,419.30 rows=73,225 width=40) (actual time=0.009..0.589 rows=856 loops=1)

59. 0.138 0.138 ↑ 1.0 713 1

Seq Scan on projectgroup pg_1 (cost=0.00..20.25 rows=725 width=40) (actual time=0.007..0.138 rows=713 loops=1)

60. 0.081 0.333 ↑ 151.0 48 3

Hash Join (cost=29.31..193.45 rows=7,250 width=40) (actual time=0.074..0.111 rows=48 loops=3)

  • Hash Cond: (pgparents_1_1.parentuid = pg2.projectgroupuid)
61. 0.045 0.045 ↑ 25.4 285 3

WorkTable Scan on pgparents pgparents_1_1 (cost=0.00..145.00 rows=7,250 width=32) (actual time=0.000..0.015 rows=285 loops=3)

62. 0.098 0.207 ↑ 1.0 713 1

Hash (cost=20.25..20.25 rows=725 width=40) (actual time=0.207..0.207 rows=713 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 49kB
63. 0.109 0.109 ↑ 1.0 713 1

Seq Scan on projectgroup pg2 (cost=0.00..20.25 rows=725 width=40) (actual time=0.004..0.109 rows=713 loops=1)

64. 0.736 1.488 ↑ 1.0 2,800 1

Sort (cost=278.27..285.40 rows=2,853 width=24) (actual time=1.327..1.488 rows=2,800 loops=1)

  • Sort Key: p_1.projectgroupuid
  • Sort Method: quicksort Memory: 315kB
65. 0.752 0.752 ↑ 1.0 2,800 1

Seq Scan on project p_1 (cost=0.00..114.53 rows=2,853 width=24) (actual time=0.014..0.752 rows=2,800 loops=1)

66. 0.369 1.166 ↑ 23.5 3,113 1

Sort (cost=7,381.10..7,564.16 rows=73,225 width=32) (actual time=1.017..1.166 rows=3,113 loops=1)

  • Sort Key: pgparents.projectgroupuid
  • Sort Method: quicksort Memory: 69kB
67. 0.797 0.797 ↑ 85.5 856 1

CTE Scan on pgparents (cost=0.00..1,464.50 rows=73,225 width=32) (actual time=0.010..0.797 rows=856 loops=1)

68. 0.533 4.110 ↑ 120.3 2,842 1

Hash (cost=19,640.83..19,640.83 rows=341,916 width=8) (actual time=4.110..4.110 rows=2,842 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 4,208kB
69. 0.232 3.577 ↑ 120.3 2,842 1

Subquery Scan on pppg (cost=11,078.67..19,640.83 rows=341,916 width=8) (actual time=2.347..3.577 rows=2,842 loops=1)

70. 0.681 3.345 ↑ 120.3 2,842 1

Merge Join (cost=11,078.67..16,221.67 rows=341,916 width=32) (actual time=2.346..3.345 rows=2,842 loops=1)

  • Merge Cond: (p_2.projectgroupuid = pgparents_1.projectgroupuid)
71.          

CTE pgparents

72. 0.117 0.580 ↑ 85.5 856 1

Recursive Union (cost=0.00..3,419.30 rows=73,225 width=40) (actual time=0.008..0.580 rows=856 loops=1)

73. 0.133 0.133 ↑ 1.0 713 1

Seq Scan on projectgroup pg_2 (cost=0.00..20.25 rows=725 width=40) (actual time=0.007..0.133 rows=713 loops=1)

74. 0.082 0.330 ↑ 151.0 48 3

Hash Join (cost=29.31..193.45 rows=7,250 width=40) (actual time=0.073..0.110 rows=48 loops=3)

  • Hash Cond: (pgparents_1_2.parentuid = pg2_1.projectgroupuid)
75. 0.045 0.045 ↑ 25.4 285 3

WorkTable Scan on pgparents pgparents_1_2 (cost=0.00..145.00 rows=7,250 width=32) (actual time=0.001..0.015 rows=285 loops=3)

76. 0.096 0.203 ↑ 1.0 713 1

Hash (cost=20.25..20.25 rows=725 width=40) (actual time=0.203..0.203 rows=713 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 49kB
77. 0.107 0.107 ↑ 1.0 713 1

Seq Scan on projectgroup pg2_1 (cost=0.00..20.25 rows=725 width=40) (actual time=0.003..0.107 rows=713 loops=1)

78. 0.758 1.525 ↑ 1.0 2,800 1

Sort (cost=278.27..285.40 rows=2,853 width=24) (actual time=1.353..1.525 rows=2,800 loops=1)

  • Sort Key: p_2.projectgroupuid
  • Sort Method: quicksort Memory: 315kB
79. 0.767 0.767 ↑ 1.0 2,800 1

Seq Scan on project p_2 (cost=0.00..114.53 rows=2,853 width=24) (actual time=0.020..0.767 rows=2,800 loops=1)

80. 0.360 1.139 ↑ 23.5 3,113 1

Sort (cost=7,381.10..7,564.16 rows=73,225 width=16) (actual time=0.988..1.139 rows=3,113 loops=1)

  • Sort Key: pgparents_1.projectgroupuid
  • Sort Method: quicksort Memory: 65kB
81. 0.779 0.779 ↑ 85.5 856 1

CTE Scan on pgparents pgparents_1 (cost=0.00..1,464.50 rows=73,225 width=16) (actual time=0.010..0.779 rows=856 loops=1)

82. 0.004 5.061 ↑ 3.8 8 1

Hash (cost=477.17..477.17 rows=30 width=75) (actual time=5.061..5.061 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
83. 0.007 5.057 ↑ 3.8 8 1

Nested Loop (cost=314.60..477.17 rows=30 width=75) (actual time=4.293..5.057 rows=8 loops=1)

84. 0.229 4.996 ↑ 8.6 9 1

Hash Join (cost=314.32..449.86 rows=77 width=8) (actual time=4.273..4.996 rows=9 loops=1)

  • Hash Cond: (ug.usergroupid = ugv2.id)
85. 2.338 4.727 ↑ 1.6 3,291 1

HashAggregate (cost=275.50..339.68 rows=5,134 width=64) (actual time=4.187..4.727 rows=3,291 loops=1)

  • Group Key: ug.usergroupname, ug.usergroupid, ug.usergroupdescription, CASE WHEN (ug.usergroupisactive < 1) THEN 'ACTIVE'::text ELSE 'INACTIVE'::text END, u_1.user_id
86. 1.316 2.389 ↑ 1.0 4,896 1

Hash Right Join (cost=60.64..211.33 rows=5,134 width=64) (actual time=0.593..2.389 rows=4,896 loops=1)

  • Hash Cond: (u_1.user_group_id = ug.usergroupid)
87. 0.491 0.491 ↑ 1.1 4,750 1

Seq Scan on users_roles u_1 (cost=0.00..124.34 rows=5,134 width=16) (actual time=0.004..0.491 rows=4,750 loops=1)

88. 0.272 0.582 ↑ 1.0 1,673 1

Hash (cost=39.73..39.73 rows=1,673 width=32) (actual time=0.582..0.582 rows=1,673 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 141kB
89. 0.310 0.310 ↑ 1.0 1,673 1

Seq Scan on usergroup ug (cost=0.00..39.73 rows=1,673 width=32) (actual time=0.007..0.310 rows=1,673 loops=1)

90. 0.001 0.040 ↑ 3.0 1 1

Hash (cost=38.78..38.78 rows=3 width=8) (actual time=0.040..0.040 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
91. 0.001 0.039 ↑ 3.0 1 1

Subquery Scan on ugv2 (cost=38.71..38.78 rows=3 width=8) (actual time=0.036..0.039 rows=1 loops=1)

92. 0.003 0.038 ↑ 3.0 1 1

Unique (cost=38.71..38.75 rows=3 width=64) (actual time=0.035..0.038 rows=1 loops=1)

93. 0.012 0.035 ↑ 1.0 3 1

Sort (cost=38.71..38.72 rows=3 width=64) (actual time=0.035..0.035 rows=3 loops=1)

  • Sort Key: ug_1.usergroupname, ug_1.usergroupid, ug_1.usergroupdescription, (CASE WHEN (ug_1.usergroupisactive < 1) THEN 'ACTIVE'::text ELSE 'INACTIVE'::text END)
  • Sort Method: quicksort Memory: 25kB
94. 0.006 0.023 ↑ 1.0 3 1

Nested Loop (cost=0.56..38.69 rows=3 width=64) (actual time=0.018..0.023 rows=3 loops=1)

95. 0.011 0.011 ↑ 1.0 3 1

Index Only Scan using users_roles_user_id_usergroup_role_id on users_roles u_2 (cost=0.28..13.79 rows=3 width=16) (actual time=0.009..0.011 rows=3 loops=1)

  • Index Cond: (user_id = 3)
  • Heap Fetches: 3
96. 0.006 0.006 ↑ 1.0 1 3

Index Scan using pk_usergroup_usergroupid on usergroup ug_1 (cost=0.28..8.29 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=3)

  • Index Cond: (usergroupid = u_2.user_group_id)
97. 0.054 0.054 ↑ 1.0 1 9

Index Scan using users_pkey on users u (cost=0.28..0.35 rows=1 width=67) (actual time=0.006..0.006 rows=1 loops=9)

  • Index Cond: (id = u_1.user_id)
  • Filter: (active AND (((first_name)::text ~~* '%a%'::text) OR ((last_name)::text ~~* '%a%'::text) OR ((email)::text ~~* '%a%'::text)))
  • Rows Removed by Filter: 0
Planning time : 8.198 ms
Execution time : 36,970.156 ms