explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ReJV

Settings
# exclusive inclusive rows x rows loops node
1. 0.139 3,175.603 ↓ 387.0 387 1

Subquery Scan on r (cost=974.81..1,282.84 rows=1 width=589) (actual time=22.126..3,175.603 rows=387 loops=1)

  • Filter: ($1 OR ((hashed SubPlan 8) AND (NOT $13)) OR (r.id = CASE WHEN (current_setting('role'::text) ~ '^[0-9a-f]{8}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{12}$'::text) THEN (current_setting('role'::text))::uuid ELSE '00000000-0000-0000-0000-000000000000'::uuid END))
2.          

Initplan (for Subquery Scan)

3. 0.776 0.776 ↑ 1.0 1 1

Result (cost=0.00..0.27 rows=1 width=16) (actual time=0.776..0.776 rows=1 loops=1)

4. 0.209 0.209 ↑ 1.0 1 1

Result (cost=0.00..0.27 rows=1 width=1) (actual time=0.208..0.209 rows=1 loops=1)

5. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.27 rows=1 width=1) (never executed)

6. 587.919 3,174.479 ↓ 387.0 387 1

Nested Loop Left Join (cost=787.80..1,095.80 rows=1 width=605) (actual time=21.904..3,174.479 rows=387 loops=1)

  • Join Filter: (sr.account_id = a.id)
7.          

CTE my_ops

8. 0.006 2.175 ↑ 2.0 1 1

Nested Loop (cost=81.44..89.59 rows=2 width=4) (actual time=2.173..2.175 rows=1 loops=1)

9. 0.051 0.051 ↑ 1.0 1 1

Index Scan using pk_role_raw on role_raw r_2 (cost=0.30..8.32 rows=1 width=20) (actual time=0.050..0.051 rows=1 loops=1)

  • Index Cond: (id = CASE WHEN (current_setting('role'::text) ~ '^[0-9a-f]{8}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{12}$'::text) THEN (current_setting('role'::text))::uuid ELSE '00000000-0000-0000-0000-000000000000'::uuid END)
10. 0.008 2.118 ↑ 2.0 1 1

GroupAggregate (cost=81.14..81.23 rows=2 width=100) (actual time=2.118..2.118 rows=1 loops=1)

  • Group Key: rg_1.role_id, g_5.account_id
11. 0.009 2.110 ↑ 2.0 1 1

Unique (cost=81.14..81.17 rows=2 width=116) (actual time=2.102..2.110 rows=1 loops=1)

12. 0.024 2.101 ↑ 1.0 2 1

Sort (cost=81.14..81.14 rows=2 width=116) (actual time=2.101..2.101 rows=2 loops=1)

  • Sort Key: rg_1.role_id, g_5.account_id, g_5.group_id, g_5.name, g_5.metadata, g_5.operations
  • Sort Method: quicksort Memory: 25kB
13. 0.002 2.077 ↑ 1.0 2 1

Append (cost=39.15..81.13 rows=2 width=116) (actual time=1.221..2.077 rows=2 loops=1)

14. 0.007 1.232 ↑ 1.0 1 1

Nested Loop (cost=39.15..67.24 rows=1 width=136) (actual time=1.220..1.232 rows=1 loops=1)

  • Join Filter: (rg_1.group_id = g_5.group_id)
15. 0.643 1.212 ↑ 1.0 1 1

Nested Loop (cost=38.88..59.94 rows=1 width=48) (actual time=1.200..1.212 rows=1 loops=1)

  • Join Filter: (rg_1.group_id = "*SELECT* 1_1".group_id)
  • Rows Removed by Join Filter: 3
16. 0.042 0.042 ↑ 1.0 1 1

Index Only Scan using role_group_raw_role_group_unq on role_group_raw rg_1 (cost=0.29..4.31 rows=1 width=32) (actual time=0.041..0.042 rows=1 loops=1)

  • Index Cond: (role_id = CASE WHEN (current_setting('role'::text) ~ '^[0-9a-f]{8}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{12}$'::text) THEN (current_setting('role'::text))::uuid ELSE '00000000-0000-0000-0000-000000000000'::uuid END)
  • Heap Fetches: 0
17. 0.003 0.527 ↑ 2.0 4 1

Append (cost=38.58..55.52 rows=8 width=16) (actual time=0.517..0.527 rows=4 loops=1)

18. 0.000 0.318 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_1 (cost=38.58..38.71 rows=5 width=16) (actual time=0.318..0.318 rows=0 loops=1)

19. 0.001 0.318 ↓ 0.0 0 1

GroupAggregate (cost=38.58..38.66 rows=5 width=17) (actual time=0.318..0.318 rows=0 loops=1)

  • Group Key: "*SELECT* 1_2".group_id
20. 0.004 0.317 ↓ 0.0 0 1

Sort (cost=38.58..38.59 rows=5 width=16) (actual time=0.317..0.317 rows=0 loops=1)

  • Sort Key: "*SELECT* 1_2".group_id
  • Sort Method: quicksort Memory: 25kB
21. 0.001 0.313 ↓ 0.0 0 1

Append (cost=1.94..38.52 rows=5 width=16) (actual time=0.313..0.313 rows=0 loops=1)

22. 0.000 0.171 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_2 (cost=1.94..10.77 rows=1 width=16) (actual time=0.171..0.171 rows=0 loops=1)

23. 0.004 0.171 ↓ 0.0 0 1

Result (cost=1.94..10.76 rows=1 width=17) (actual time=0.170..0.171 rows=0 loops=1)

  • One-Time Filter: (NOT $15)
24.          

Initplan (for Result)

25. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.27 rows=1 width=16) (never executed)

26. 0.167 0.167 ↑ 1.0 1 1

Result (cost=0.00..0.27 rows=1 width=1) (actual time=0.167..0.167 rows=1 loops=1)

27. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.40..10.21 rows=1 width=16) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.12..9.73 rows=1 width=16) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.85..9.40 rows=1 width=16) (never executed)

  • Join Filter: (rl_1.group_id = g_7.group_id)
30. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..8.65 rows=2 width=48) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Index Only Scan using role_group_raw_role_group_unq on role_group_raw rl_1 (cost=0.29..4.31 rows=1 width=16) (never executed)

  • Index Cond: (role_id = CASE WHEN (current_setting('role'::text) ~ '^[0-9a-f]{8}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{12}$'::text) THEN (current_setting('role'::text))::uuid ELSE '00000000-0000-0000-0000-000000000000'::uuid END)
  • Heap Fetches: 0
32. 0.000 0.000 ↓ 0.0 0

Index Only Scan using group_link_group_target_unq on group_link_raw gl_2 (cost=0.28..4.31 rows=2 width=32) (never executed)

  • Index Cond: (group_id = rl_1.group_id)
  • Heap Fetches: 0
33. 0.000 0.000 ↓ 0.0 0

Index Scan using group_raw_pkey on group_raw g_7 (cost=0.28..0.37 rows=1 width=16) (never executed)

  • Index Cond: (group_id = gl_2.group_id)
  • Filter: ((status <> 'R'::navigator_data.t_status) AND (account_id = $14))
34. 0.000 0.000 ↓ 0.0 0

Index Scan using group_path_ancestor_id_idx on group_path gp_2 (cost=0.28..0.32 rows=1 width=32) (never executed)

  • Index Cond: (ancestor_id = gl_2.target_group_id)
35. 0.000 0.000 ↓ 0.0 0

Index Scan using group_raw_pkey on group_raw g_6 (cost=0.28..0.47 rows=1 width=16) (never executed)

  • Index Cond: (group_id = gp_2.id)
  • Filter: (status <> 'R'::navigator_data.t_status)
36. 0.001 0.141 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_2 (cost=4.96..27.73 rows=4 width=16) (actual time=0.141..0.141 rows=0 loops=1)

37. 0.003 0.140 ↓ 0.0 0 1

Result (cost=4.96..27.69 rows=4 width=17) (actual time=0.140..0.140 rows=0 loops=1)

  • One-Time Filter: (NOT $16)
38.          

Initplan (for Result)

39. 0.137 0.137 ↑ 1.0 1 1

Result (cost=0.00..0.27 rows=1 width=1) (actual time=0.137..0.137 rows=1 loops=1)

40. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.69..27.42 rows=4 width=16) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=4.41..25.49 rows=4 width=16) (never executed)

  • Hash Cond: (gp_3.ancestor_id = gl_3.target_group_id)
42. 0.000 0.000 ↓ 0.0 0

Seq Scan on group_path gp_3 (cost=0.00..18.56 rows=956 width=32) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Hash (cost=4.36..4.36 rows=4 width=16) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Index Only Scan using group_link_role_target_group_unq on group_link_raw gl_3 (cost=0.29..4.36 rows=4 width=16) (never executed)

  • Index Cond: (role_id = CASE WHEN (current_setting('role'::text) ~ '^[0-9a-f]{8}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{12}$'::text) THEN (current_setting('role'::text))::uuid ELSE '00000000-0000-0000-0000-000000000000'::uuid END)
  • Heap Fetches: 0
45. 0.000 0.000 ↓ 0.0 0

Index Scan using group_raw_pkey on group_raw g_8 (cost=0.28..0.47 rows=1 width=16) (never executed)

  • Index Cond: (group_id = gp_3.id)
  • Filter: (status <> 'R'::navigator_data.t_status)
46. 0.002 0.206 ↓ 1.3 4 1

Subquery Scan on *SELECT* 2_1 (cost=4.85..16.78 rows=3 width=16) (actual time=0.197..0.206 rows=4 loops=1)

47. 0.000 0.204 ↓ 1.3 4 1

Result (cost=4.85..16.75 rows=3 width=17) (actual time=0.196..0.204 rows=4 loops=1)

  • One-Time Filter: $18
48.          

Initplan (for Result)

49. 0.627 0.627 ↑ 1.0 1 1

Result (cost=0.00..0.27 rows=1 width=16) (actual time=0.626..0.627 rows=1 loops=1)

50. 0.163 0.163 ↑ 1.0 1 1

Result (cost=0.00..0.27 rows=1 width=1) (actual time=0.163..0.163 rows=1 loops=1)

51. 0.021 0.035 ↓ 1.3 4 1

Bitmap Heap Scan on group_raw g_9 (cost=4.31..16.20 rows=3 width=16) (actual time=0.029..0.035 rows=4 loops=1)

  • Recheck Cond: ((account_id = $17) AND (status <> 'R'::navigator_data.t_status))
  • Filter: ((type = 'U'::bpchar) OR ((type = 'C'::bpchar) AND (owner_id IS NULL)) OR (owner_id = CASE WHEN (current_setting('role'::text) ~ '^[0-9a-f]{8}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{12}$'::text) THEN (current_setting('role'::text))::uuid ELSE '00000000-0000-0000-0000-000000000000'::uuid END))
  • Heap Blocks: exact=2
52. 0.014 0.014 ↑ 1.0 4 1

Bitmap Index Scan on group_raw_account_name_unq (cost=0.00..4.31 rows=4 width=0) (actual time=0.014..0.014 rows=4 loops=1)

  • Index Cond: (account_id = $17)
53. 0.013 0.013 ↑ 1.0 1 1

Index Scan using group_raw_pkey on group_raw g_5 (cost=0.28..7.30 rows=1 width=120) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (group_id = "*SELECT* 1_1".group_id)
  • Filter: (status = 'A'::navigator_data.t_status)
54. 0.000 0.843 ↑ 1.0 1 1

Nested Loop (cost=1.41..13.86 rows=1 width=136) (actual time=0.841..0.843 rows=1 loops=1)

  • Join Filter: (rg_2.group_id = g_11.group_id)
55.          

Initplan (for Nested Loop)

56. 0.650 0.650 ↑ 1.0 1 1

Result (cost=0.00..0.27 rows=1 width=16) (actual time=0.650..0.650 rows=1 loops=1)

57. 0.037 0.174 ↑ 1.0 1 1

Nested Loop (cost=0.86..13.09 rows=1 width=168) (actual time=0.172..0.174 rows=1 loops=1)

  • Join Filter: (rg_2.group_id = rl_2.group_id)
58. 0.008 0.131 ↑ 1.0 1 1

Nested Loop (cost=0.57..12.62 rows=1 width=152) (actual time=0.129..0.131 rows=1 loops=1)

59. 0.097 0.097 ↑ 1.0 1 1

Index Only Scan using role_group_raw_role_group_unq on role_group_raw rg_2 (cost=0.29..4.31 rows=1 width=32) (actual time=0.096..0.097 rows=1 loops=1)

  • Index Cond: (role_id = CASE WHEN (current_setting('role'::text) ~ '^[0-9a-f]{8}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{12}$'::text) THEN (current_setting('role'::text))::uuid ELSE '00000000-0000-0000-0000-000000000000'::uuid END)
  • Heap Fetches: 0
60. 0.026 0.026 ↑ 1.0 1 1

Index Scan using group_raw_pkey on group_raw g_10 (cost=0.28..8.29 rows=1 width=120) (actual time=0.026..0.026 rows=1 loops=1)

  • Index Cond: (group_id = rg_2.group_id)
  • Filter: (status = 'A'::navigator_data.t_status)
61. 0.006 0.006 ↑ 1.0 1 1

Index Only Scan using role_group_raw_role_group_unq on role_group_raw rl_2 (cost=0.29..0.46 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: ((role_id = CASE WHEN (current_setting('role'::text) ~ '^[0-9a-f]{8}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{12}$'::text) THEN (current_setting('role'::text))::uuid ELSE '00000000-0000-0000-0000-000000000000'::uuid END) AND (group_id = g_10.group_id))
  • Heap Fetches: 0
62. 0.663 0.663 ↑ 1.0 1 1

Index Scan using group_raw_pkey on group_raw g_11 (cost=0.28..0.48 rows=1 width=16) (actual time=0.662..0.663 rows=1 loops=1)

  • Index Cond: (group_id = g_10.group_id)
  • Filter: ((status <> 'R'::navigator_data.t_status) AND (account_id = $19))
63.          

Initplan (for Nested Loop Left Join)

64. 2.178 2.178 ↑ 2.0 1 1

CTE Scan on my_ops (cost=0.00..0.04 rows=2 width=4) (actual time=2.175..2.178 rows=1 loops=1)

65. 6.294 2,346.764 ↓ 387.0 387 1

Nested Loop Left Join (cost=692.60..865.36 rows=1 width=539) (actual time=14.194..2,346.764 rows=387 loops=1)

  • Join Filter: (ra.role_id = r_1.id)
  • Rows Removed by Join Filter: 33,966
66. 2.262 2,313.380 ↓ 387.0 387 1

Nested Loop Left Join (cost=685.78..855.28 rows=1 width=475) (actual time=13.913..2,313.380 rows=387 loops=1)

67. 2.672 2,306.474 ↓ 387.0 387 1

Nested Loop Left Join (cost=685.50..848.53 rows=1 width=448) (actual time=13.892..2,306.474 rows=387 loops=1)

  • Join Filter: (k.account_id = a.id)
68. 23.342 2,298.384 ↓ 387.0 387 1

Nested Loop Left Join (cost=685.23..848.23 rows=1 width=432) (actual time=13.861..2,298.384 rows=387 loops=1)

  • Join Filter: ((r_1.account_id = asr.account_id) AND (rsl.role_id = r_1.id))
  • Rows Removed by Join Filter: 74,691
69. 23.169 1,545.934 ↓ 387.0 387 1

Nested Loop Left Join (cost=622.53..785.31 rows=1 width=368) (actual time=10.531..1,545.934 rows=387 loops=1)

  • Join Filter: ((r_1.account_id = g.account_id) AND (rgl.role_id = r_1.id))
  • Rows Removed by Join Filter: 81,377
70. 1.391 30.880 ↓ 387.0 387 1

Nested Loop Left Join (cost=499.35..661.09 rows=1 width=300) (actual time=1.952..30.880 rows=387 loops=1)

71. 1.813 24.071 ↓ 387.0 387 1

Nested Loop (cost=499.06..652.77 rows=1 width=284) (actual time=1.927..24.071 rows=387 loops=1)

72. 1.561 18.388 ↓ 387.0 387 1

Nested Loop (cost=498.64..652.10 rows=1 width=262) (actual time=1.907..18.388 rows=387 loops=1)

  • Join Filter: (r_1.id = ur.role_id)
73. 2.325 12.570 ↓ 387.0 387 1

Nested Loop (cost=498.35..650.60 rows=1 width=262) (actual time=1.888..12.570 rows=387 loops=1)

  • Join Filter: (CASE WHEN (a.state = 'D'::bpchar) THEN 'R'::bpchar ELSE r_1.status END <> 'R'::bpchar)
  • Rows Removed by Join Filter: 13
74. 2.478 6.645 ↓ 400.0 400 1

Hash Join (cost=498.08..642.29 rows=1 width=172) (actual time=1.872..6.645 rows=400 loops=1)

  • Hash Cond: (acl.role_id = r_1.id)
75. 2.405 2.405 ↓ 1.0 2,729 1

Seq Scan on acl_raw acl (cost=0.00..137.11 rows=2,707 width=36) (actual time=0.022..2.405 rows=2,729 loops=1)

  • Filter: (type = 'H'::bpchar)
  • Rows Removed by Filter: 2,202
76. 0.336 1.762 ↓ 23.2 418 1

Hash (cost=497.85..497.85 rows=18 width=136) (actual time=1.762..1.762 rows=418 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 71kB
77. 0.596 1.426 ↓ 23.2 418 1

Bitmap Heap Scan on role_raw r_1 (cost=5.64..497.85 rows=18 width=136) (actual time=0.863..1.426 rows=418 loops=1)

  • Recheck Cond: (account_id = $0)
  • Filter: (NOT is_contact)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=93
78. 0.830 0.830 ↓ 2.3 419 1

Bitmap Index Scan on role_raw_account_id_idx (cost=0.00..5.63 rows=179 width=0) (actual time=0.830..0.830 rows=419 loops=1)

  • Index Cond: (account_id = $0)
79. 3.600 3.600 ↑ 1.0 1 400

Index Scan using account_raw_pkey on account_raw a (cost=0.27..8.29 rows=1 width=90) (actual time=0.009..0.009 rows=1 loops=400)

  • Index Cond: (id = $0)
80. 4.257 4.257 ↑ 1.0 1 387

Index Scan using user_role_raw_role_id_idx on user_role_raw ur (cost=0.29..1.49 rows=1 width=32) (actual time=0.009..0.011 rows=1 loops=387)

  • Index Cond: (role_id = acl.role_id)
81. 3.870 3.870 ↑ 1.0 1 387

Index Scan using pk_user_raw_id on user_raw u (cost=0.42..0.67 rows=1 width=38) (actual time=0.010..0.010 rows=1 loops=387)

  • Index Cond: (id = ur.user_id)
82. 5.418 5.418 ↑ 1.0 1 387

Index Scan using role_xref_child_id_idx on role_xref rx (cost=0.29..8.31 rows=1 width=32) (actual time=0.011..0.014 rows=1 loops=387)

  • Index Cond: (child_id = r_1.id)
83. 1,467.117 1,491.885 ↓ 11.7 211 387

GroupAggregate (cost=123.18..123.77 rows=18 width=100) (actual time=0.045..3.855 rows=211 loops=387)

  • Group Key: rgl.role_id, g.account_id
84. 22.552 24.768 ↓ 11.8 212 387

Sort (cost=123.18..123.23 rows=18 width=152) (actual time=0.008..0.064 rows=212 loops=387)

  • Sort Key: rgl.role_id
  • Sort Method: quicksort Memory: 136kB
85. 1.381 2.216 ↓ 23.3 419 1

Hash Join (cost=16.17..122.81 rows=18 width=152) (actual time=0.553..2.216 rows=419 loops=1)

  • Hash Cond: (rgl.group_id = g.group_id)
86. 0.791 0.791 ↓ 1.0 4,525 1

Seq Scan on role_group_raw rgl (cost=0.00..95.23 rows=4,323 width=32) (actual time=0.012..0.791 rows=4,525 loops=1)

87. 0.005 0.044 ↑ 1.0 4 1

Hash (cost=16.12..16.12 rows=4 width=120) (actual time=0.044..0.044 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
88. 0.025 0.039 ↑ 1.0 4 1

Bitmap Heap Scan on group_raw g (cost=4.31..16.12 rows=4 width=120) (actual time=0.031..0.039 rows=4 loops=1)

  • Recheck Cond: ((account_id = $0) AND (status <> 'R'::navigator_data.t_status))
  • Filter: (status = 'A'::navigator_data.t_status)
  • Heap Blocks: exact=2
89. 0.014 0.014 ↑ 1.0 4 1

Bitmap Index Scan on group_raw_account_name_unq (cost=0.00..4.31 rows=4 width=0) (actual time=0.014..0.014 rows=4 loops=1)

  • Index Cond: (account_id = $0)
90. 705.501 729.108 ↓ 48.5 194 387

GroupAggregate (cost=62.70..62.82 rows=4 width=96) (actual time=0.018..1.884 rows=194 loops=387)

  • Group Key: rsl.role_id, asr.account_id
91. 23.069 23.607 ↓ 48.8 195 387

Sort (cost=62.70..62.71 rows=4 width=52) (actual time=0.003..0.061 rows=195 loops=387)

  • Sort Key: rsl.role_id
  • Sort Method: quicksort Memory: 79kB
92. 0.141 0.538 ↓ 96.5 386 1

Nested Loop (cost=4.78..62.66 rows=4 width=52) (actual time=0.122..0.538 rows=386 loops=1)

93. 0.043 0.043 ↑ 1.0 1 1

Index Scan using account_service_raw_unq on account_service_raw asr (cost=0.27..8.29 rows=1 width=36) (actual time=0.041..0.043 rows=1 loops=1)

  • Index Cond: ((account_id = $0) AND (service_key = 'SFTP'::text))
94. 0.303 0.354 ↓ 25.7 386 1

Bitmap Heap Scan on role_service_raw rsl (cost=4.50..54.22 rows=15 width=32) (actual time=0.067..0.354 rows=386 loops=1)

  • Recheck Cond: (service_id = asr.id)
  • Filter: (status = 'A'::navigator_data.t_status)
  • Rows Removed by Filter: 31
  • Heap Blocks: exact=45
95. 0.051 0.051 ↓ 14.4 417 1

Bitmap Index Scan on role_service_raw_service_id (cost=0.00..4.50 rows=29 width=0) (actual time=0.051..0.051 rows=417 loops=1)

  • Index Cond: (service_id = asr.id)
96. 5.418 5.418 ↓ 0.0 0 387

Index Scan using user_crypt_key_user_account_idx on user_crypt_key_raw k (cost=0.27..0.29 rows=1 width=48) (actual time=0.014..0.014 rows=0 loops=387)

  • Index Cond: ((user_id = u.id) AND (account_id = $0))
97. 4.644 4.644 ↑ 1.0 1 387

Index Scan using role_props_raw_pkey on role_props_raw rp (cost=0.28..6.74 rows=1 width=43) (actual time=0.012..0.012 rows=1 loops=387)

  • Index Cond: (r_1.id = role_id)
98. 26.950 27.090 ↓ 1.0 88 387

HashAggregate (cost=6.82..8.12 rows=87 width=80) (actual time=0.004..0.070 rows=88 loops=387)

  • Group Key: ra.role_id
99. 0.060 0.140 ↑ 1.0 91 1

Hash Join (cost=1.08..6.14 rows=91 width=20) (actual time=0.054..0.140 rows=91 loops=1)

  • Hash Cond: (ra.auth_type_id = a_1.id)
100. 0.060 0.060 ↑ 1.0 91 1

Seq Scan on role_auth_raw ra (cost=0.00..4.49 rows=91 width=33) (actual time=0.020..0.060 rows=91 loops=1)

  • Filter: (status = 'A'::bpchar)
  • Rows Removed by Filter: 29
101. 0.004 0.020 ↑ 1.0 3 1

Hash (cost=1.04..1.04 rows=3 width=19) (actual time=0.020..0.020 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
102. 0.016 0.016 ↑ 1.0 3 1

Seq Scan on auth_type_raw a_1 (cost=0.00..1.04 rows=3 width=19) (actual time=0.014..0.016 rows=3 loops=1)

  • Filter: (status = 'A'::bpchar)
103. 0.387 8.901 ↓ 0.0 0 387

GroupAggregate (cost=5.56..12.81 rows=1 width=48) (actual time=0.023..0.023 rows=0 loops=387)

  • Group Key: sr.account_id
104. 0.000 8.514 ↓ 0.0 0 387

Nested Loop (cost=5.56..12.80 rows=1 width=19) (actual time=0.022..0.022 rows=0 loops=387)

  • Join Filter: (sa.auth_id = a_2.id)
105. 1.143 8.514 ↓ 0.0 0 387

Hash Join (cost=5.56..11.72 rows=1 width=32) (actual time=0.022..0.022 rows=0 loops=387)

  • Hash Cond: (sr.id = sa.service_id)
106. 3.096 7.353 ↑ 1.0 2 387

Bitmap Heap Scan on account_service_raw sr (cost=4.29..10.42 rows=2 width=32) (actual time=0.017..0.019 rows=2 loops=387)

  • Recheck Cond: ((account_id = $0) AND (status <> 'D'::bpchar))
  • Heap Blocks: exact=774
107. 4.257 4.257 ↑ 1.0 2 387

Bitmap Index Scan on account_service_raw_unq (cost=0.00..4.29 rows=2 width=0) (actual time=0.011..0.011 rows=2 loops=387)

  • Index Cond: (account_id = $0)
108. 0.005 0.018 ↓ 1.3 8 1

Hash (cost=1.20..1.20 rows=6 width=32) (actual time=0.018..0.018 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
109. 0.013 0.013 ↓ 1.3 8 1

Seq Scan on service_auth_xref sa (cost=0.00..1.20 rows=6 width=32) (actual time=0.010..0.013 rows=8 loops=1)

  • Filter: (status = 'A'::navigator_data.t_status)
  • Rows Removed by Filter: 9
110. 0.000 0.000 ↓ 0.0 0

Seq Scan on auth_type_raw a_2 (cost=0.00..1.04 rows=3 width=19) (never executed)

  • Filter: (status = 'A'::bpchar)
111.          

SubPlan (for Nested Loop Left Join)

112. 3.125 5.805 ↑ 1.0 1 387

Result (cost=3.07..3.08 rows=1 width=38) (actual time=0.015..0.015 rows=1 loops=387)

113.          

Initplan (for Result)

114. 2.506 2.506 ↑ 1.0 1 358

Seq Scan on language_raw l (cost=0.00..1.02 rows=1 width=6) (actual time=0.006..0.007 rows=1 loops=358)

  • Filter: (id = r_1.language)
  • Rows Removed by Filter: 1
115. 0.174 0.174 ↑ 1.0 1 29

Seq Scan on language_raw l_1 (cost=0.00..1.02 rows=1 width=6) (actual time=0.005..0.006 rows=1 loops=29)

  • Filter: (id = a.language)
  • Rows Removed by Filter: 1
116. 0.000 0.000 ↓ 0.0 0

Seq Scan on language_raw l_2 (cost=0.00..1.02 rows=1 width=6) (never executed)

  • Filter: (("default")::text = 'Y'::text)
117. 1.161 8.514 ↑ 1.0 1 387

Result (cost=0.50..0.51 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=387)

118.          

Initplan (for Result)

119. 1.548 7.353 ↑ 1.0 1 387

Limit (cost=0.43..0.50 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=387)

120. 5.805 5.805 ↑ 1,164.0 1 387

Index Only Scan Backward using session_log_user_id_session_start_idx on session_log (cost=0.43..83.71 rows=1,164 width=8) (actual time=0.015..0.015 rows=1 loops=387)

  • Index Cond: ((user_id = u.id) AND (session_start IS NOT NULL))
  • Heap Fetches: 107
121. 4.644 4.644 ↓ 0.0 0 387

Index Scan using agr_account_usage_raw_role_id_idx on agr_account_usage_raw au (cost=0.28..8.30 rows=1 width=8) (actual time=0.008..0.012 rows=0 loops=387)

  • Index Cond: (role_id = r_1.id)
  • Filter: (account_id = r_1.account_id)
122. 3.483 209.754 ↑ 1.0 1 387

Aggregate (cost=114.51..114.52 rows=1 width=32) (actual time=0.542..0.542 rows=1 loops=387)

123. 206.271 206.271 ↓ 0.0 0 387

Seq Scan on delta_account_usage_raw du (cost=0.00..113.86 rows=258 width=8) (actual time=0.533..0.533 rows=0 loops=387)

  • Filter: ((account_id = r_1.account_id) AND (role_id = r_1.id))
  • Rows Removed by Filter: 2,328
124.          

SubPlan (for Subquery Scan)

125. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=73.97..186.04 rows=63 width=16) (never executed)

  • Hash Cond: (rg.group_id = "*SELECT* 1".group_id)
126. 0.000 0.000 ↓ 0.0 0

Seq Scan on role_group_raw rg (cost=0.00..95.23 rows=4,323 width=32) (never executed)

127. 0.000 0.000 ↓ 0.0 0

Hash (cost=73.90..73.90 rows=5 width=16) (never executed)

128. 0.000 0.000 ↓ 0.0 0

Append (cost=56.99..73.90 rows=5 width=16) (never executed)

129. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 1 (cost=56.99..57.10 rows=2 width=16) (never executed)

130. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=56.99..57.08 rows=2 width=17) (never executed)

  • Group Key: g_1.group_id
  • Filter: bool_or(gl.manage)
131. 0.000 0.000 ↓ 0.0 0

Sort (cost=56.99..57.00 rows=5 width=17) (never executed)

  • Sort Key: g_1.group_id
132. 0.000 0.000 ↓ 0.0 0

Append (cost=5.96..56.93 rows=5 width=17) (never executed)

133. 0.000 0.000 ↓ 0.0 0

Result (cost=5.96..17.29 rows=1 width=17) (never executed)

  • One-Time Filter: (NOT $3)
134.          

Initplan (for Result)

135. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.27 rows=1 width=16) (never executed)

136. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.27 rows=1 width=1) (never executed)

137. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.42..16.74 rows=1 width=17) (never executed)

138. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.14..16.26 rows=1 width=17) (never executed)

139. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.87..15.93 rows=1 width=17) (never executed)

  • Join Filter: (rl.group_id = g_2.group_id)
140. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.59..15.18 rows=2 width=49) (never executed)

141. 0.000 0.000 ↓ 0.0 0

Index Only Scan using role_group_raw_role_group_unq on role_group_raw rl (cost=0.29..4.31 rows=1 width=16) (never executed)

  • Index Cond: (role_id = CASE WHEN (current_setting('role'::text) ~ '^[0-9a-f]{8}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{12}$'::text) THEN (current_setting('role'::text))::uuid ELSE '00000000-0000-0000-0000-000000000000'::uuid END)
  • Heap Fetches: 0
142. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on group_link_raw gl (cost=4.30..10.84 rows=2 width=33) (never executed)

  • Recheck Cond: (group_id = rl.group_id)
143. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on group_link_group_target_unq (cost=0.00..4.29 rows=2 width=0) (never executed)

  • Index Cond: (group_id = rl.group_id)
144. 0.000 0.000 ↓ 0.0 0

Index Scan using group_raw_pkey on group_raw g_2 (cost=0.28..0.37 rows=1 width=16) (never executed)

  • Index Cond: (group_id = gl.group_id)
  • Filter: ((status <> 'R'::navigator_data.t_status) AND (account_id = $2))
145. 0.000 0.000 ↓ 0.0 0

Index Scan using group_path_ancestor_id_idx on group_path gp (cost=0.28..0.32 rows=1 width=32) (never executed)

  • Index Cond: (ancestor_id = gl.target_group_id)
146. 0.000 0.000 ↓ 0.0 0

Index Scan using group_raw_pkey on group_raw g_1 (cost=0.28..0.47 rows=1 width=16) (never executed)

  • Index Cond: (group_id = gp.id)
  • Filter: (status <> 'R'::navigator_data.t_status)
147. 0.000 0.000 ↓ 0.0 0

Result (cost=16.84..39.57 rows=4 width=17) (never executed)

  • One-Time Filter: (NOT $4)
148.          

Initplan (for Result)

149. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.27 rows=1 width=1) (never executed)

150. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=16.57..39.30 rows=4 width=17) (never executed)

151. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=16.30..37.38 rows=4 width=17) (never executed)

  • Hash Cond: (gp_1.ancestor_id = gl_1.target_group_id)
152. 0.000 0.000 ↓ 0.0 0

Seq Scan on group_path gp_1 (cost=0.00..18.56 rows=956 width=32) (never executed)

153. 0.000 0.000 ↓ 0.0 0

Hash (cost=16.25..16.25 rows=4 width=17) (never executed)

154. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on group_link_raw gl_1 (cost=4.32..16.25 rows=4 width=17) (never executed)

  • Recheck Cond: (role_id = CASE WHEN (current_setting('role'::text) ~ '^[0-9a-f]{8}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{12}$'::text) THEN (current_setting('role'::text))::uuid ELSE '00000000-0000-0000-0000-000000000000'::uuid END)
155. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on group_link_role_target_group_unq (cost=0.00..4.32 rows=4 width=0) (never executed)

  • Index Cond: (role_id = CASE WHEN (current_setting('role'::text) ~ '^[0-9a-f]{8}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{12}$'::text) THEN (current_setting('role'::text))::uuid ELSE '00000000-0000-0000-0000-000000000000'::uuid END)
156. 0.000 0.000 ↓ 0.0 0

Index Scan using group_raw_pkey on group_raw g_3 (cost=0.28..0.47 rows=1 width=16) (never executed)

  • Index Cond: (group_id = gp_1.id)
  • Filter: (status <> 'R'::navigator_data.t_status)
157. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2 (cost=4.85..16.78 rows=3 width=16) (never executed)

158. 0.000 0.000 ↓ 0.0 0

Result (cost=4.85..16.75 rows=3 width=17) (never executed)

  • One-Time Filter: $6
159.          

Initplan (for Result)

160. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.27 rows=1 width=16) (never executed)

161. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.27 rows=1 width=1) (never executed)

162. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on group_raw g_4 (cost=4.31..16.20 rows=3 width=16) (never executed)

  • Recheck Cond: ((account_id = $5) AND (status <> 'R'::navigator_data.t_status))
  • Filter: ((type = 'U'::bpchar) OR ((type = 'C'::bpchar) AND (owner_id IS NULL)) OR (owner_id = CASE WHEN (current_setting('role'::text) ~ '^[0-9a-f]{8}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{4}(-)?[0-9a-f]{12}$'::text) THEN (current_setting('role'::text))::uuid ELSE '00000000-0000-0000-0000-000000000000'::uuid END))
163. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on group_raw_account_name_unq (cost=0.00..4.31 rows=4 width=0) (never executed)

  • Index Cond: (account_id = $5)
Planning time : 38.924 ms
Execution time : 3,179.147 ms