explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8J6W

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 3,719.358 ↓ 566.0 566 1

Subquery Scan on r (cost=938.14..1,027.48 rows=1 width=562) (actual time=22.721..3,719.358 rows=566 loops=1)

  • Filter: ($1 OR ((hashed SubPlan 8) AND (NOT $15)) 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 (forSubquery Scan)

3. 1.231 1.231 ↑ 1.0 1 1

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

4. 0.161 0.161 ↑ 1.0 1 1

Result (cost=0.00..0.27 rows=1 width=1) (actual time=0.161..0.161 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. 888.470 3,718.109 ↓ 566.0 566 1

Nested Loop Left Join (cost=790.50..879.80 rows=1 width=578) (actual time=22.552..3,718.109 rows=566 loops=1)

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

CTE my_ops

8. 0.004 1.466 ↑ 2.0 1 1

Nested Loop (cost=96.09..104.25 rows=2 width=4) (actual time=1.465..1.466 rows=1 loops=1)

9. 0.037 0.037 ↑ 1.0 1 1

Index Scan using pk_role_raw on role_raw r_3 (cost=0.30..8.31 rows=1 width=20) (actual time=0.036..0.037 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.005 1.425 ↑ 2.0 1 1

GroupAggregate (cost=95.80..95.89 rows=2 width=100) (actual time=1.424..1.425 rows=1 loops=1)

  • Group Key: rg_3.role_id, g_11.account_id
11. 0.005 1.420 ↑ 2.0 1 1

Unique (cost=95.80..95.83 rows=2 width=116) (actual time=1.415..1.420 rows=1 loops=1)

12. 0.016 1.415 ↑ 1.0 2 1

Sort (cost=95.80..95.80 rows=2 width=116) (actual time=1.414..1.415 rows=2 loops=1)

  • Sort Key: rg_3.role_id, g_11.account_id, g_11.group_id, g_11.name, g_11.metadata, g_11.operations
  • Sort Method: quicksort Memory: 25kB
13. 0.002 1.399 ↑ 1.0 2 1

Append (cost=54.28..95.79 rows=2 width=116) (actual time=0.856..1.399 rows=2 loops=1)

14. 0.450 0.860 ↑ 1.0 1 1

Nested Loop (cost=54.28..82.06 rows=1 width=141) (actual time=0.855..0.860 rows=1 loops=1)

  • Join Filter: (rg_3.group_id = "*SELECT* 1_3".group_id)
  • Rows Removed by Join Filter: 3
15. 0.004 0.033 ↑ 1.0 1 1

Nested Loop (cost=0.58..12.62 rows=1 width=157) (actual time=0.031..0.033 rows=1 loops=1)

16. 0.020 0.020 ↑ 1.0 1 1

Index Only Scan using role_group_raw_role_group_unq on role_group_raw rg_3 (cost=0.29..4.31 rows=1 width=32) (actual time=0.019..0.020 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.009 0.009 ↑ 1.0 1 1

Index Scan using group_raw_pkey on group_raw g_11 (cost=0.28..8.30 rows=1 width=125) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (group_id = rg_3.group_id)
  • Filter: (status = 'A'::navigator_data.t_status)
18. 0.001 0.377 ↑ 1.8 4 1

Append (cost=53.70..69.36 rows=7 width=16) (actual time=0.371..0.377 rows=4 loops=1)

19. 0.001 0.245 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_3 (cost=53.70..53.80 rows=4 width=16) (actual time=0.245..0.245 rows=0 loops=1)

20. 0.001 0.244 ↓ 0.0 0 1

GroupAggregate (cost=53.70..53.76 rows=4 width=17) (actual time=0.244..0.244 rows=0 loops=1)

  • Group Key: "*SELECT* 1_4".group_id
21. 0.003 0.243 ↓ 0.0 0 1

Sort (cost=53.70..53.71 rows=4 width=16) (actual time=0.243..0.243 rows=0 loops=1)

  • Sort Key: "*SELECT* 1_4".group_id
  • Sort Method: quicksort Memory: 25kB
22. 0.002 0.240 ↓ 0.0 0 1

Append (cost=1.97..53.66 rows=4 width=16) (actual time=0.240..0.240 rows=0 loops=1)

23. 0.001 0.136 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_4 (cost=1.97..14.77 rows=1 width=16) (actual time=0.136..0.136 rows=0 loops=1)

24. 0.003 0.135 ↓ 0.0 0 1

Result (cost=1.97..14.76 rows=1 width=17) (actual time=0.135..0.135 rows=0 loops=1)

  • One-Time Filter: (NOT $17)
25.          

Initplan (forResult)

26. 0.000 0.000 ↓ 0.0 0

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

27. 0.132 0.132 ↑ 1.0 1 1

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

28. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.42..14.21 rows=1 width=16) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.14..13.78 rows=1 width=16) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..13.43 rows=1 width=16) (never executed)

  • Join Filter: (rl_3.group_id = g_13.group_id)
31. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.58..12.65 rows=2 width=48) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Index Only Scan using role_group_raw_role_group_unq on role_group_raw rl_3 (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
33. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (group_id = rl_3.group_id)
  • Heap Fetches: 0
34. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Index Cond: (ancestor_id = gl_4.target_group_id)
36. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (group_id = gp_4.id)
  • Filter: (status <> 'R'::navigator_data.t_status)
37. 0.000 0.102 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_4 (cost=1.13..38.88 rows=3 width=16) (actual time=0.102..0.102 rows=0 loops=1)

38. 0.003 0.102 ↓ 0.0 0 1

Result (cost=1.13..38.85 rows=3 width=17) (actual time=0.102..0.102 rows=0 loops=1)

  • One-Time Filter: (NOT $18)
39.          

Initplan (forResult)

40. 0.099 0.099 ↑ 1.0 1 1

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

41. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..38.57 rows=3 width=16) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.58..37.27 rows=3 width=16) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Index Only Scan using group_link_role_target_group_unq on group_link_raw gl_5 (cost=0.29..12.34 rows=3 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
44. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (ancestor_id = gl_5.target_group_id)
45. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (group_id = gp_5.id)
  • Filter: (status <> 'R'::navigator_data.t_status)
46. 0.001 0.131 ↓ 1.3 4 1

Subquery Scan on *SELECT* 2_3 (cost=4.85..15.52 rows=3 width=16) (actual time=0.125..0.131 rows=4 loops=1)

47. 0.000 0.130 ↓ 1.3 4 1

Result (cost=4.85..15.49 rows=3 width=17) (actual time=0.125..0.130 rows=4 loops=1)

  • One-Time Filter: $20
48.          

Initplan (forResult)

49. 0.435 0.435 ↑ 1.0 1 1

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

50. 0.096 0.096 ↑ 1.0 1 1

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

51. 0.016 0.031 ↓ 1.3 4 1

Bitmap Heap Scan on group_raw g_15 (cost=4.31..14.94 rows=3 width=16) (actual time=0.026..0.031 rows=4 loops=1)

  • Recheck Cond: ((account_id = $19) 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=3
52. 0.015 0.015 ↓ 1.3 4 1

Bitmap Index Scan on group_raw_account_name_unq (cost=0.00..4.30 rows=3 width=0) (actual time=0.015..0.015 rows=4 loops=1)

  • Index Cond: (account_id = $19)
53. 0.000 0.537 ↑ 1.0 1 1

Nested Loop (cost=1.43..13.69 rows=1 width=141) (actual time=0.535..0.537 rows=1 loops=1)

  • Join Filter: (rg_4.group_id = g_17.group_id)
54.          

Initplan (forNested Loop)

55. 0.444 0.444 ↑ 1.0 1 1

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

56. 0.021 0.074 ↑ 1.0 1 1

Nested Loop (cost=0.87..12.98 rows=1 width=173) (actual time=0.073..0.074 rows=1 loops=1)

  • Join Filter: (rg_4.group_id = rl_4.group_id)
57. 0.005 0.045 ↑ 1.0 1 1

Nested Loop (cost=0.58..12.62 rows=1 width=157) (actual time=0.044..0.045 rows=1 loops=1)

58. 0.027 0.027 ↑ 1.0 1 1

Index Only Scan using role_group_raw_role_group_unq on role_group_raw rg_4 (cost=0.29..4.31 rows=1 width=32) (actual time=0.027..0.027 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
59. 0.013 0.013 ↑ 1.0 1 1

Index Scan using group_raw_pkey on group_raw g_16 (cost=0.28..8.30 rows=1 width=125) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (group_id = rg_4.group_id)
  • Filter: (status = 'A'::navigator_data.t_status)
60. 0.008 0.008 ↑ 1.0 1 1

Index Only Scan using role_group_raw_role_group_unq on role_group_raw rl_4 (cost=0.29..0.35 rows=1 width=16) (actual time=0.008..0.008 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_16.group_id))
  • Heap Fetches: 0
61. 0.458 0.458 ↑ 1.0 1 1

Index Scan using group_raw_pkey on group_raw g_17 (cost=0.28..0.43 rows=1 width=16) (actual time=0.458..0.458 rows=1 loops=1)

  • Index Cond: (group_id = g_16.group_id)
  • Filter: ((status <> 'R'::navigator_data.t_status) AND (account_id = $21))
62.          

Initplan (forNested Loop Left Join)

63. 1.469 1.469 ↑ 2.0 1 1

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

64. 6.037 2,776.664 ↓ 566.0 566 1

Nested Loop Left Join (cost=685.94..751.14 rows=1 width=526) (actual time=18.228..2,776.664 rows=566 loops=1)

  • Join Filter: (r_1.id = ra.role_id)
  • Rows Removed by Join Filter: 38488
65. 3.971 2,720.819 ↓ 566.0 566 1

Nested Loop Left Join (cost=202.66..264.49 rows=1 width=462) (actual time=14.396..2,720.819 rows=566 loops=1)

  • Join Filter: ((r_1.account_id = asr.account_id) AND (r_1.id = rs.role_id))
  • Rows Removed by Join Filter: 565
66. 44.226 2,711.754 ↓ 566.0 566 1

Nested Loop Left Join (cost=174.21..235.98 rows=1 width=389) (actual time=14.219..2,711.754 rows=566 loops=1)

  • Join Filter: ((g.account_id = r_1.account_id) AND (r_1.id = rg.role_id))
  • Rows Removed by Join Filter: 312629
67. 1.770 76.946 ↓ 566.0 566 1

Nested Loop Left Join (cost=49.68..111.29 rows=1 width=321) (actual time=3.540..76.946 rows=566 loops=1)

  • Join Filter: (k.account_id = a.id)
68. 1.433 72.912 ↓ 566.0 566 1

Nested Loop Left Join (cost=49.54..111.11 rows=1 width=305) (actual time=3.519..72.912 rows=566 loops=1)

69. 5.436 65.253 ↓ 566.0 566 1

Nested Loop (cost=49.26..102.80 rows=1 width=289) (actual time=3.493..65.253 rows=566 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: 246
70. 3.371 55.757 ↓ 812.0 812 1

Nested Loop (cost=48.98..94.49 rows=1 width=231) (actual time=3.469..55.757 rows=812 loops=1)

71. 2.298 45.078 ↓ 812.0 812 1

Nested Loop (cost=48.69..93.60 rows=1 width=205) (actual time=3.444..45.078 rows=812 loops=1)

72. 2.395 20.960 ↓ 363.7 1,091 1

Nested Loop (cost=48.41..92.25 rows=3 width=201) (actual time=3.405..20.960 rows=1,091 loops=1)

73. 4.028 8.746 ↓ 363.7 1,091 1

Hash Right Join (cost=48.12..67.31 rows=3 width=169) (actual time=3.364..8.746 rows=1,091 loops=1)

  • Hash Cond: (COALESCE(u_1.role_id, du.role_id) = r_1.id)
74. 1.011 1.446 ↑ 1.0 533 1

Hash Full Join (cost=25.62..43.42 rows=533 width=64) (actual time=0.049..1.446 rows=533 loops=1)

  • Hash Cond: ((u_1.account_id = du.account_id) AND (u_1.role_id = du.role_id))
75. 0.426 0.426 ↑ 1.0 533 1

Seq Scan on agr_account_usage_raw u_1 (cost=0.00..12.33 rows=533 width=40) (actual time=0.010..0.426 rows=533 loops=1)

76. 0.001 0.009 ↓ 0.0 0 1

Hash (cost=25.53..25.53 rows=6 width=64) (actual time=0.009..0.009 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
77. 0.001 0.008 ↓ 0.0 0 1

HashAggregate (cost=25.40..25.47 rows=6 width=64) (actual time=0.008..0.008 rows=0 loops=1)

  • Group Key: du.account_id, du.role_id
78. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on delta_account_usage_raw du (cost=0.00..18.80 rows=880 width=40) (actual time=0.007..0.007 rows=0 loops=1)

79. 0.773 3.272 ↓ 363.7 1,091 1

Hash (cost=22.46..22.46 rows=3 width=137) (actual time=3.272..3.272 rows=1,091 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 183kB
80. 1.146 2.499 ↓ 363.7 1,091 1

Bitmap Heap Scan on role_raw r_1 (cost=4.32..22.46 rows=3 width=137) (actual time=1.387..2.499 rows=1,091 loops=1)

  • Recheck Cond: (account_id = $0)
  • Filter: (NOT is_contact)
  • Rows Removed by Filter: 162
  • Heap Blocks: exact=156
81. 1.353 1.353 ↓ 250.6 1,253 1

Bitmap Index Scan on role_raw_account_id_idx (cost=0.00..4.32 rows=5 width=0) (actual time=1.353..1.353 rows=1,253 loops=1)

  • Index Cond: (account_id = $0)
82. 9.819 9.819 ↑ 1.0 1 1,091

Index Scan using user_role_raw_role_id_idx on user_role_raw ur (cost=0.29..8.30 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=1,091)

  • Index Cond: (role_id = r_1.id)
83. 21.820 21.820 ↑ 1.0 1 1,091

Index Scan using acl_raw_role_id_idx on acl_raw acl (cost=0.28..0.44 rows=1 width=36) (actual time=0.020..0.020 rows=1 loops=1,091)

  • Index Cond: (role_id = ur.role_id)
  • Filter: (type = 'H'::bpchar)
  • Rows Removed by Filter: 5
84. 7.308 7.308 ↑ 1.0 1 812

Index Scan using pk_user_raw_id on user_raw u (cost=0.29..0.88 rows=1 width=42) (actual time=0.009..0.009 rows=1 loops=812)

  • Index Cond: (id = ur.user_id)
85. 4.060 4.060 ↑ 1.0 1 812

Index Scan using account_raw_pkey on account_raw a (cost=0.28..8.29 rows=1 width=58) (actual time=0.005..0.005 rows=1 loops=812)

  • Index Cond: (id = $0)
86. 6.226 6.226 ↑ 1.0 1 566

Index Scan using role_xref_child_id_idx on role_xref rx (cost=0.28..8.30 rows=1 width=32) (actual time=0.009..0.011 rows=1 loops=566)

  • Index Cond: (child_id = r_1.id)
87. 2.264 2.264 ↓ 0.0 0 566

Index Scan using user_crypt_key_raw_user_id_account_id on user_crypt_key_raw k (cost=0.14..0.17 rows=1 width=48) (actual time=0.004..0.004 rows=0 loops=566)

  • Index Cond: ((user_id = u.id) AND (account_id = $0))
  • Filter: (status = 'A'::bpchar)
88. 2,157.026 2,590.582 ↓ 276.5 553 566

GroupAggregate (cost=124.53..124.64 rows=2 width=100) (actual time=0.028..4.577 rows=553 loops=566)

  • Group Key: rg.role_id, g.account_id
89. 358.278 433.556 ↓ 277.0 554 566

Unique (cost=124.53..124.56 rows=2 width=116) (actual time=0.009..0.766 rows=554 loops=566)

90. 71.956 75.278 ↓ 277.5 555 566

Sort (cost=124.53..124.53 rows=2 width=116) (actual time=0.008..0.133 rows=555 loops=566)

  • Sort Key: rg.role_id, g.account_id, g.group_id, g.name, g.metadata, g.operations
  • Sort Method: quicksort Memory: 342kB
91. 0.124 3.322 ↓ 553.0 1,106 1

Append (cost=62.31..124.52 rows=2 width=116) (actual time=1.580..3.322 rows=1,106 loops=1)

92. 0.297 2.412 ↓ 1,105.0 1,105 1

Nested Loop (cost=62.31..98.94 rows=1 width=141) (actual time=1.579..2.412 rows=1,105 loops=1)

93. 0.010 1.559 ↓ 4.0 4 1

Nested Loop (cost=58.01..84.56 rows=1 width=141) (actual time=1.518..1.559 rows=4 loops=1)

  • Join Filter: (g.group_id = "*SELECT* 1".group_id)
  • Rows Removed by Join Filter: 6
94. 0.002 1.493 ↑ 1.8 4 1

Append (cost=53.70..69.36 rows=7 width=16) (actual time=1.473..1.493 rows=4 loops=1)

95. 0.001 0.626 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=53.70..53.80 rows=4 width=16) (actual time=0.626..0.626 rows=0 loops=1)

96. 0.001 0.625 ↓ 0.0 0 1

GroupAggregate (cost=53.70..53.76 rows=4 width=17) (actual time=0.625..0.625 rows=0 loops=1)

  • Group Key: "*SELECT* 1_1".group_id
97. 0.011 0.624 ↓ 0.0 0 1

Sort (cost=53.70..53.71 rows=4 width=16) (actual time=0.624..0.624 rows=0 loops=1)

  • Sort Key: "*SELECT* 1_1".group_id
  • Sort Method: quicksort Memory: 25kB
98. 0.001 0.613 ↓ 0.0 0 1

Append (cost=1.97..53.66 rows=4 width=16) (actual time=0.613..0.613 rows=0 loops=1)

99. 0.000 0.418 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_1 (cost=1.97..14.77 rows=1 width=16) (actual time=0.418..0.418 rows=0 loops=1)

100. 0.004 0.418 ↓ 0.0 0 1

Result (cost=1.97..14.76 rows=1 width=17) (actual time=0.417..0.418 rows=0 loops=1)

  • One-Time Filter: (NOT $43)
101.          

Initplan (forResult)

102. 0.000 0.000 ↓ 0.0 0

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

103. 0.414 0.414 ↑ 1.0 1 1

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

104. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.42..14.21 rows=1 width=16) (never executed)

105. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.14..13.78 rows=1 width=16) (never executed)

106. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..13.43 rows=1 width=16) (never executed)

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

Nested Loop (cost=0.58..12.65 rows=2 width=48) (never executed)

108. 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
109. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (group_id = rl.group_id)
  • Heap Fetches: 0
110. 0.000 0.000 ↓ 0.0 0

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

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

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

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

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

  • Index Cond: (group_id = gp.id)
  • Filter: (status <> 'R'::navigator_data.t_status)
113. 0.001 0.194 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=1.13..38.88 rows=3 width=16) (actual time=0.194..0.194 rows=0 loops=1)

114. 0.004 0.193 ↓ 0.0 0 1

Result (cost=1.13..38.85 rows=3 width=17) (actual time=0.193..0.193 rows=0 loops=1)

  • One-Time Filter: (NOT $44)
115.          

Initplan (forResult)

116. 0.189 0.189 ↑ 1.0 1 1

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

117. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..38.57 rows=3 width=16) (never executed)

118. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.58..37.27 rows=3 width=16) (never executed)

119. 0.000 0.000 ↓ 0.0 0

Index Only Scan using group_link_role_target_group_unq on group_link_raw gl_1 (cost=0.29..12.34 rows=3 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
120. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (ancestor_id = gl_1.target_group_id)
121. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (group_id = gp_1.id)
  • Filter: (status <> 'R'::navigator_data.t_status)
122. 0.004 0.865 ↓ 1.3 4 1

Subquery Scan on *SELECT* 2 (cost=4.85..15.52 rows=3 width=16) (actual time=0.846..0.865 rows=4 loops=1)

123. 0.000 0.861 ↓ 1.3 4 1

Result (cost=4.85..15.49 rows=3 width=17) (actual time=0.845..0.861 rows=4 loops=1)

  • One-Time Filter: $46
124.          

Initplan (forResult)

125. 0.565 0.565 ↑ 1.0 1 1

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

126. 0.228 0.228 ↑ 1.0 1 1

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

127. 0.025 0.628 ↓ 1.3 4 1

Bitmap Heap Scan on group_raw g_4 (cost=4.31..14.94 rows=3 width=16) (actual time=0.613..0.628 rows=4 loops=1)

  • Recheck Cond: ((account_id = $45) 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=3
128. 0.603 0.603 ↓ 1.3 4 1

Bitmap Index Scan on group_raw_account_name_unq (cost=0.00..4.30 rows=3 width=0) (actual time=0.603..0.603 rows=4 loops=1)

  • Index Cond: (account_id = $45)
129. 0.021 0.056 ↑ 1.5 2 4

Materialize (cost=4.31..14.90 rows=3 width=125) (actual time=0.010..0.014 rows=2 loops=4)

130. 0.015 0.035 ↓ 1.3 4 1

Bitmap Heap Scan on group_raw g (cost=4.31..14.88 rows=3 width=125) (actual time=0.030..0.035 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=3
131. 0.020 0.020 ↓ 1.3 4 1

Bitmap Index Scan on group_raw_account_name_unq (cost=0.00..4.30 rows=3 width=0) (actual time=0.020..0.020 rows=4 loops=1)

  • Index Cond: (account_id = $0)
132. 0.428 0.556 ↓ 92.0 276 4

Bitmap Heap Scan on role_group_raw rg (cost=4.31..14.35 rows=3 width=32) (actual time=0.040..0.139 rows=276 loops=4)

  • Recheck Cond: (group_id = g.group_id)
  • Heap Blocks: exact=71
133. 0.128 0.128 ↓ 92.0 276 4

Bitmap Index Scan on role_group_raw_group_id (cost=0.00..4.30 rows=3 width=0) (actual time=0.032..0.032 rows=276 loops=4)

  • Index Cond: (group_id = g.group_id)
134. 0.000 0.786 ↑ 1.0 1 1

Nested Loop (cost=1.43..25.55 rows=1 width=141) (actual time=0.783..0.786 rows=1 loops=1)

  • Join Filter: (rg_1.group_id = g_6.group_id)
135.          

Initplan (forNested Loop)

136. 0.691 0.691 ↑ 1.0 1 1

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

137. 0.006 0.081 ↑ 1.0 1 1

Nested Loop (cost=0.87..16.95 rows=1 width=173) (actual time=0.079..0.081 rows=1 loops=1)

  • Join Filter: (rg_1.group_id = g_5.group_id)
138. 0.018 0.062 ↑ 1.0 1 1

Nested Loop (cost=0.59..8.64 rows=1 width=48) (actual time=0.061..0.062 rows=1 loops=1)

  • Join Filter: (rg_1.group_id = rl_1.group_id)
139. 0.040 0.040 ↑ 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.038..0.040 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
140. 0.004 0.004 ↑ 1.0 1 1

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) (actual time=0.004..0.004 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
141. 0.013 0.013 ↑ 1.0 1 1

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

  • Index Cond: (group_id = rl_1.group_id)
  • Filter: ((status = 'A'::navigator_data.t_status) AND (account_id = $0))
142. 0.699 0.699 ↑ 1.0 1 1

Index Scan using group_raw_pkey on group_raw g_6 (cost=0.28..8.31 rows=1 width=16) (actual time=0.699..0.699 rows=1 loops=1)

  • Index Cond: (group_id = g_5.group_id)
  • Filter: ((status <> 'R'::navigator_data.t_status) AND (account_id = $47))
143. 4.528 5.094 ↑ 1.0 1 566

GroupAggregate (cost=28.45..28.48 rows=1 width=137) (actual time=0.009..0.009 rows=1 loops=566)

  • Group Key: rs.id, asr.account_id, rp.unique_login, rp.sftp_home_id
144. 0.432 0.566 ↑ 1.0 1 566

Sort (cost=28.45..28.45 rows=1 width=93) (actual time=0.001..0.001 rows=1 loops=566)

  • Sort Key: rs.id, rp.unique_login, rp.sftp_home_id
  • Sort Method: quicksort Memory: 25kB
145. 0.005 0.134 ↑ 1.0 1 1

Nested Loop Left Join (cost=4.87..28.44 rows=1 width=93) (actual time=0.129..0.134 rows=1 loops=1)

146. 0.041 0.114 ↑ 1.0 1 1

Nested Loop (cost=4.59..27.97 rows=1 width=68) (actual time=0.109..0.114 rows=1 loops=1)

147. 0.031 0.031 ↑ 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.030..0.031 rows=1 loops=1)

  • Index Cond: ((account_id = $0) AND (service_key = 'SFTP'::text))
  • Filter: (NOT base_service)
148. 0.016 0.042 ↑ 3.0 1 1

Bitmap Heap Scan on role_service_raw rs (cost=4.32..19.65 rows=3 width=48) (actual time=0.038..0.042 rows=1 loops=1)

  • Recheck Cond: (service_id = asr.id)
  • Filter: (status = 'A'::navigator_data.t_status)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=2
149. 0.026 0.026 ↑ 2.5 2 1

Bitmap Index Scan on role_service_raw_service_id (cost=0.00..4.32 rows=5 width=0) (actual time=0.026..0.026 rows=2 loops=1)

  • Index Cond: (service_id = asr.id)
150. 0.015 0.015 ↑ 1.0 1 1

Index Scan using role_props_raw_pkey on role_props_raw rp (cost=0.28..0.46 rows=1 width=41) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (rs.role_id = role_id)
151. 41.318 49.808 ↑ 1.0 68 566

GroupAggregate (cost=483.28..485.06 rows=71 width=80) (actual time=0.011..0.088 rows=68 loops=566)

  • Group Key: ra.role_id
152. 4.807 8.490 ↑ 1.0 68 566

Sort (cost=483.28..483.46 rows=71 width=20) (actual time=0.007..0.015 rows=68 loops=566)

  • Sort Key: ra.role_id
  • Sort Method: quicksort Memory: 30kB
153. 0.066 3.683 ↑ 1.0 68 1

Nested Loop (cost=10.66..481.10 rows=71 width=20) (actual time=0.413..3.683 rows=68 loops=1)

  • Join Filter: (ra.auth_type_id = a_1.id)
  • Rows Removed by Join Filter: 52
154. 0.777 3.617 ↑ 1.0 68 1

Hash Join (cost=10.66..477.40 rows=71 width=33) (actual time=0.384..3.617 rows=68 loops=1)

  • Hash Cond: (r_2.id = ra.role_id)
155. 2.626 2.626 ↓ 1.0 5,802 1

Seq Scan on role_raw r_2 (cost=0.00..415.34 rows=5,793 width=16) (actual time=0.022..2.626 rows=5,802 loops=1)

  • Filter: (status = 'A'::bpchar)
  • Rows Removed by Filter: 3205
156. 0.106 0.214 ↑ 1.0 109 1

Hash (cost=9.29..9.29 rows=110 width=33) (actual time=0.214..0.214 rows=109 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
157. 0.108 0.108 ↑ 1.0 109 1

Seq Scan on role_auth_raw ra (cost=0.00..9.29 rows=110 width=33) (actual time=0.015..0.108 rows=109 loops=1)

  • Filter: (status = 'A'::bpchar)
  • Rows Removed by Filter: 77
158. 0.000 0.000 ↑ 1.5 2 68

Materialize (cost=0.00..1.05 rows=3 width=19) (actual time=0.000..0.000 rows=2 loops=68)

159. 0.017 0.017 ↑ 1.5 2 1

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

  • Filter: (status = 'A'::bpchar)
160. 0.000 23.772 ↓ 0.0 0 566

GroupAggregate (cost=0.27..10.64 rows=1 width=48) (actual time=0.042..0.042 rows=0 loops=566)

  • Group Key: sr.account_id
161. 0.000 23.772 ↓ 0.0 0 566

Nested Loop (cost=0.27..10.63 rows=1 width=19) (actual time=0.042..0.042 rows=0 loops=566)

  • Join Filter: (sa.auth_id = a_2.id)
162. 6.226 23.772 ↓ 0.0 0 566

Nested Loop (cost=0.27..9.55 rows=1 width=32) (actual time=0.042..0.042 rows=0 loops=566)

  • Join Filter: (sa.service_id = sr.id)
  • Rows Removed by Join Filter: 14
163. 9.622 9.622 ↓ 2.0 2 566

Index Scan using account_service_raw_unq on account_service_raw sr (cost=0.27..8.29 rows=1 width=32) (actual time=0.014..0.017 rows=2 loops=566)

  • Index Cond: (account_id = $0)
164. 7.924 7.924 ↑ 1.0 7 1,132

Seq Scan on service_auth_xref sa (cost=0.00..1.18 rows=7 width=32) (actual time=0.004..0.007 rows=7 loops=1,132)

  • Filter: (status = 'A'::navigator_data.t_status)
  • Rows Removed by Filter: 7
165. 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)
166.          

SubPlan (forNested Loop Left Join)

167. 3.835 6.792 ↑ 1.0 1 566

Result (cost=3.07..3.08 rows=1 width=38) (actual time=0.012..0.012 rows=1 loops=566)

168.          

Initplan (forResult)

169. 2.195 2.195 ↑ 1.0 1 439

Seq Scan on language_raw l (cost=0.00..1.02 rows=1 width=6) (actual time=0.004..0.005 rows=1 loops=439)

  • Filter: (id = r_1.language)
  • Rows Removed by Filter: 1
170. 0.762 0.762 ↑ 1.0 1 127

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=127)

  • Filter: (id = a.language)
  • Rows Removed by Filter: 1
171. 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)
172. 1.132 14.716 ↑ 1.0 1 566

Result (cost=0.76..0.77 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=566)

173.          

Initplan (forResult)

174. 1.698 13.584 ↑ 1.0 1 566

Limit (cost=0.56..0.76 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=566)

175. 11.886 11.886 ↑ 18,680.0 1 566

Index Only Scan Backward using session_log_user_id_session_start_idx on session_log (cost=0.56..3,769.71 rows=18,680 width=8) (actual time=0.021..0.021 rows=1 loops=566)

  • Index Cond: ((user_id = u.id) AND (session_start IS NOT NULL))
  • Heap Fetches: 104
176. 6.226 6.226 ↑ 1.0 1 566

Index Scan using role_props_raw_pkey on role_props_raw pr (cost=0.28..8.30 rows=1 width=1) (actual time=0.011..0.011 rows=1 loops=566)

  • Index Cond: (role_id = r_1.id)
177.          

SubPlan (forSubquery Scan)

178. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=63.57..146.79 rows=15 width=16) (never executed)

179. 0.000 0.000 ↓ 0.0 0

Append (cost=59.27..74.90 rows=5 width=16) (never executed)

180. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 1_2 (cost=59.27..59.36 rows=2 width=16) (never executed)

181. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=59.27..59.34 rows=2 width=17) (never executed)

  • Group Key: g_7.group_id
  • Filter: bool_or(gl_2.manage)
182. 0.000 0.000 ↓ 0.0 0

Sort (cost=59.27..59.28 rows=4 width=17) (never executed)

  • Sort Key: g_7.group_id
183. 0.000 0.000 ↓ 0.0 0

Append (cost=5.99..59.23 rows=4 width=17) (never executed)

184. 0.000 0.000 ↓ 0.0 0

Result (cost=5.99..17.88 rows=1 width=17) (never executed)

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

Initplan (forResult)

186. 0.000 0.000 ↓ 0.0 0

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

187. 0.000 0.000 ↓ 0.0 0

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

188. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.44..17.34 rows=1 width=17) (never executed)

189. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.16..16.90 rows=1 width=17) (never executed)

190. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.88..16.56 rows=1 width=17) (never executed)

  • Join Filter: (rl_2.group_id = g_8.group_id)
191. 0.000 0.000 ↓ 0.0 0

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

192. 0.000 0.000 ↓ 0.0 0

Index Only Scan using role_group_raw_role_group_unq on role_group_raw rl_2 (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
193. 0.000 0.000 ↓ 0.0 0

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

  • Recheck Cond: (group_id = rl_2.group_id)
194. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (group_id = rl_2.group_id)
195. 0.000 0.000 ↓ 0.0 0

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

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

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

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

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

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

Result (cost=5.16..41.29 rows=3 width=17) (never executed)

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

Initplan (forResult)

200. 0.000 0.000 ↓ 0.0 0

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

201. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.88..41.01 rows=3 width=17) (never executed)

202. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.60..39.71 rows=3 width=17) (never executed)

203. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on group_link_raw gl_3 (cost=4.32..14.78 rows=3 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)
204. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on group_link_role_target_group_unq (cost=0.00..4.32 rows=3 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)
205. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (ancestor_id = gl_3.target_group_id)
206. 0.000 0.000 ↓ 0.0 0

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

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

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

208. 0.000 0.000 ↓ 0.0 0

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

  • One-Time Filter: $6
209.          

Initplan (forResult)

210. 0.000 0.000 ↓ 0.0 0

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

211. 0.000 0.000 ↓ 0.0 0

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

212. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on group_raw g_10 (cost=4.31..14.94 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))
213. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (account_id = $5)
214. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on role_group_raw rg_2 (cost=4.31..14.35 rows=3 width=32) (never executed)

  • Recheck Cond: (group_id = "*SELECT* 1_2".group_id)
215. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on role_group_raw_group_id (cost=0.00..4.30 rows=3 width=0) (never executed)

  • Index Cond: (group_id = "*SELECT* 1_2".group_id)
Planning time : 34.009 ms
Execution time : 3,724.520 ms