explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pTSw

Settings
# exclusive inclusive rows x rows loops node
1. 0.306 14,591.454 ↓ 387.0 387 1

Subquery Scan on r (cost=1,473.48..3,290.75 rows=1 width=589) (actual time=56.073..14,591.454 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.670 0.670 ↑ 1.0 1 1

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

4. 0.164 0.164 ↑ 1.0 1 1

Result (cost=0.00..0.27 rows=1 width=1) (actual time=0.164..0.164 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. 732.052 14,590.314 ↓ 387.0 387 1

Nested Loop Left Join (cost=1,286.47..3,103.71 rows=1 width=605) (actual time=55.897..14,590.314 rows=387 loops=1)

  • Join Filter: ((r_2.account_id = a.id) AND (r_2.id = r_1.id))
  • Rows Removed by Join Filter: 81,103
7.          

CTE my_ops

8. 0.006 2.470 ↑ 2.0 1 1

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

9. 0.064 0.064 ↑ 1.0 1 1

Index Scan using pk_role_raw on role_raw r_3 (cost=0.30..8.32 rows=1 width=20) (actual time=0.063..0.064 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.400 ↑ 2.0 1 1

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

  • Group Key: rg_1.role_id, g_5.account_id
11. 0.008 2.392 ↑ 2.0 1 1

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

12. 0.025 2.384 ↑ 1.0 2 1

Sort (cost=81.14..81.14 rows=2 width=116) (actual time=2.384..2.384 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.011 2.359 ↑ 1.0 2 1

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

14. 0.006 1.657 ↑ 1.0 1 1

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

  • Join Filter: (rg_1.group_id = g_5.group_id)
15. 1.097 1.640 ↑ 1.0 1 1

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

  • Join Filter: (rg_1.group_id = "*SELECT* 1_1".group_id)
  • Rows Removed by Join Filter: 3
16. 0.039 0.039 ↑ 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.037..0.039 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.002 0.504 ↑ 2.0 4 1

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

18. 0.001 0.355 ↓ 0.0 0 1

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

19. 0.001 0.354 ↓ 0.0 0 1

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

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

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

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

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

22. 0.000 0.216 ↓ 0.0 0 1

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

23. 0.004 0.216 ↓ 0.0 0 1

Result (cost=1.94..10.76 rows=1 width=17) (actual time=0.216..0.216 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.212 0.212 ↑ 1.0 1 1

Result (cost=0.00..0.27 rows=1 width=1) (actual time=0.212..0.212 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.000 0.131 ↓ 0.0 0 1

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

37. 0.003 0.131 ↓ 0.0 0 1

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

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

Initplan (for Result)

39. 0.128 0.128 ↑ 1.0 1 1

Result (cost=0.00..0.27 rows=1 width=1) (actual time=0.128..0.128 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.003 0.147 ↓ 1.3 4 1

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

47. 0.000 0.144 ↓ 1.3 4 1

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

  • One-Time Filter: $18
48.          

Initplan (for Result)

49. 1.079 1.079 ↑ 1.0 1 1

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

50. 0.101 0.101 ↑ 1.0 1 1

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

51. 0.018 0.038 ↓ 1.3 4 1

Bitmap Heap Scan on group_raw g_9 (cost=4.31..16.20 rows=3 width=16) (actual time=0.032..0.038 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.020 0.020 ↑ 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.020..0.020 rows=4 loops=1)

  • Index Cond: (account_id = $17)
53. 0.011 0.011 ↑ 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.011..0.011 rows=1 loops=1)

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

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

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

Initplan (for Nested Loop)

56. 0.595 0.595 ↑ 1.0 1 1

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

57. 0.018 0.085 ↑ 1.0 1 1

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

  • Join Filter: (rg_2.group_id = rl_2.group_id)
58. 0.005 0.060 ↑ 1.0 1 1

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

59. 0.050 0.050 ↑ 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.049..0.050 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.005 0.005 ↑ 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.005..0.005 rows=1 loops=1)

  • Index Cond: (group_id = rg_2.group_id)
  • Filter: (status = 'A'::navigator_data.t_status)
61. 0.007 0.007 ↑ 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.007..0.007 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.601 0.601 ↑ 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.601..0.601 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.473 2.473 ↑ 2.0 1 1

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

65. 7.168 2,672.263 ↓ 387.0 387 1

Nested Loop Left Join (cost=698.16..878.20 rows=1 width=571) (actual time=13.829..2,672.263 rows=387 loops=1)

  • Join Filter: (sr.account_id = a.id)
66. 6.701 2,654.646 ↓ 387.0 387 1

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

  • Join Filter: (ra.role_id = r_1.id)
  • Rows Removed by Join Filter: 33,966
67. 12.055 2,619.694 ↓ 387.0 387 1

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

68. 3.006 2,602.608 ↓ 387.0 387 1

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

  • Join Filter: (k.account_id = a.id)
69. 31.751 2,589.540 ↓ 387.0 387 1

Nested Loop Left Join (cost=685.23..848.23 rows=1 width=432) (actual time=13.364..2,589.540 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
70. 28.223 1,724.578 ↓ 387.0 387 1

Nested Loop Left Join (cost=622.53..785.31 rows=1 width=368) (actual time=10.032..1,724.578 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
71. 1.321 31.868 ↓ 387.0 387 1

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

72. 1.850 25.516 ↓ 387.0 387 1

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

73. 1.640 19.409 ↓ 387.0 387 1

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

  • Join Filter: (r_1.id = ur.role_id)
74. 2.721 13.125 ↓ 387.0 387 1

Nested Loop (cost=498.35..650.60 rows=1 width=262) (actual time=1.981..13.125 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
75. 3.076 7.204 ↓ 400.0 400 1

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

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

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

  • Filter: (type = 'H'::bpchar)
  • Rows Removed by Filter: 2,202
77. 0.294 1.499 ↓ 23.2 418 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 71kB
78. 0.494 1.205 ↓ 23.2 418 1

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

  • Recheck Cond: (account_id = $0)
  • Filter: (NOT is_contact)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=93
79. 0.711 0.711 ↓ 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.711..0.711 rows=419 loops=1)

  • Index Cond: (account_id = $0)
80. 3.200 3.200 ↑ 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.008..0.008 rows=1 loops=400)

  • Index Cond: (id = $0)
81. 4.644 4.644 ↑ 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.010..0.012 rows=1 loops=387)

  • Index Cond: (role_id = acl.role_id)
82. 4.257 4.257 ↑ 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.011..0.011 rows=1 loops=387)

  • Index Cond: (id = ur.user_id)
83. 5.031 5.031 ↑ 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.010..0.013 rows=1 loops=387)

  • Index Cond: (child_id = r_1.id)
84. 1,636.623 1,664.487 ↓ 11.7 211 387

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

  • Group Key: rgl.role_id, g.account_id
85. 25.505 27.864 ↓ 11.8 212 387

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

  • Sort Key: rgl.role_id
  • Sort Method: quicksort Memory: 136kB
86. 1.543 2.359 ↓ 23.3 419 1

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

  • Hash Cond: (rgl.group_id = g.group_id)
87. 0.756 0.756 ↓ 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.011..0.756 rows=4,525 loops=1)

88. 0.005 0.060 ↑ 1.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
89. 0.040 0.055 ↑ 1.0 4 1

Bitmap Heap Scan on group_raw g (cost=4.31..16.12 rows=4 width=120) (actual time=0.049..0.055 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
90. 0.015 0.015 ↑ 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.015..0.015 rows=4 loops=1)

  • Index Cond: (account_id = $0)
91. 810.378 833.211 ↓ 48.5 194 387

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

  • Group Key: rsl.role_id, asr.account_id
92. 22.382 22.833 ↓ 48.8 195 387

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

  • Sort Key: rsl.role_id
  • Sort Method: quicksort Memory: 79kB
93. 0.113 0.451 ↓ 96.5 386 1

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

94. 0.041 0.041 ↑ 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.038..0.041 rows=1 loops=1)

  • Index Cond: ((account_id = $0) AND (service_key = 'SFTP'::text))
95. 0.248 0.297 ↓ 25.7 386 1

Bitmap Heap Scan on role_service_raw rsl (cost=4.50..54.22 rows=15 width=32) (actual time=0.065..0.297 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
96. 0.049 0.049 ↓ 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.049..0.049 rows=417 loops=1)

  • Index Cond: (service_id = asr.id)
97. 10.062 10.062 ↓ 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.026..0.026 rows=0 loops=387)

  • Index Cond: ((user_id = u.id) AND (account_id = $0))
98. 5.031 5.031 ↑ 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.013..0.013 rows=1 loops=387)

  • Index Cond: (r_1.id = role_id)
99. 28.087 28.251 ↓ 1.0 88 387

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

  • Group Key: ra.role_id
100. 0.086 0.164 ↑ 1.0 91 1

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

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
103. 0.012 0.012 ↑ 1.0 3 1

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

  • Filter: (status = 'A'::bpchar)
104. 0.000 10.449 ↓ 0.0 0 387

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

  • Group Key: sr.account_id
105. 0.387 10.449 ↓ 0.0 0 387

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

  • Join Filter: (sa.auth_id = a_2.id)
106. 1.918 10.062 ↓ 0.0 0 387

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

  • Hash Cond: (sr.id = sa.service_id)
107. 3.483 8.127 ↑ 1.0 2 387

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

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

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

  • Index Cond: (account_id = $0)
109. 0.004 0.017 ↓ 1.3 8 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
110. 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.009..0.013 rows=8 loops=1)

  • Filter: (status = 'A'::navigator_data.t_status)
  • Rows Removed by Filter: 9
111. 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)
112. 376.536 11,168.046 ↓ 11.7 211 387

Group (cost=498.67..2,130.28 rows=18 width=72) (actual time=0.299..28.858 rows=211 loops=387)

  • Group Key: r_2.id
113. 34.209 34.830 ↓ 11.7 211 387

Sort (cost=498.67..498.72 rows=18 width=32) (actual time=0.003..0.090 rows=211 loops=387)

  • Sort Key: r_2.id
  • Sort Method: quicksort Memory: 57kB
114. 0.577 0.621 ↓ 23.2 418 1

Bitmap Heap Scan on role_raw r_2 (cost=5.64..498.30 rows=18 width=32) (actual time=0.064..0.621 rows=418 loops=1)

  • Recheck Cond: (account_id = $0)
  • Filter: (type = 'U'::bpchar)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=93
115. 0.044 0.044 ↓ 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.044..0.044 rows=419 loops=1)

  • Index Cond: (account_id = $0)
116.          

SubPlan (for Group)

117. 81.490 407.450 ↑ 1.0 1 81,490

Aggregate (cost=8.30..8.31 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=81,490)

118. 325.960 325.960 ↓ 0.0 0 81,490

Index Scan using agr_account_usage_raw_role_id_idx on agr_account_usage_raw ur_1 (cost=0.28..8.30 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=81,490)

  • Index Cond: (role_id = r_2.id)
  • Filter: (account_id = r_2.account_id)
119. 407.450 10,349.230 ↑ 1.0 1 81,490

Aggregate (cost=82.31..82.33 rows=1 width=32) (actual time=0.127..0.127 rows=1 loops=81,490)

120. 9,941.780 9,941.780 ↓ 0.0 0 81,490

Seq Scan on delta_account_usage_raw d (cost=0.00..82.18 rows=53 width=8) (actual time=0.122..0.122 rows=0 loops=81,490)

  • Filter: ((account_id = r_2.account_id) AND (role_id = r_2.id))
  • Rows Removed by Filter: 282
121.          

SubPlan (for Nested Loop Left Join)

122. 3.009 5.418 ↑ 1.0 1 387

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

123.          

Initplan (for Result)

124. 2.148 2.148 ↑ 1.0 1 358

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

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

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

  • Filter: (id = a.language)
  • Rows Removed by Filter: 1
126. 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)
127. 1.161 10.062 ↑ 1.0 1 387

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

128.          

Initplan (for Result)

129. 2.709 8.901 ↑ 1.0 1 387

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

130. 6.192 6.192 ↑ 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.016..0.016 rows=1 loops=387)

  • Index Cond: ((user_id = u.id) AND (session_start IS NOT NULL))
  • Heap Fetches: 107
131.          

SubPlan (for Subquery Scan)

132. 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)
133. 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)

134. 0.000 0.000 ↓ 0.0 0

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

135. 0.000 0.000 ↓ 0.0 0

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

136. 0.000 0.000 ↓ 0.0 0

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

137. 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)
138. 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
139. 0.000 0.000 ↓ 0.0 0

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

140. 0.000 0.000 ↓ 0.0 0

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

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

Initplan (for Result)

142. 0.000 0.000 ↓ 0.0 0

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

143. 0.000 0.000 ↓ 0.0 0

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

144. 0.000 0.000 ↓ 0.0 0

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

145. 0.000 0.000 ↓ 0.0 0

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

146. 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)
147. 0.000 0.000 ↓ 0.0 0

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

148. 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
149. 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)
150. 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)
151. 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))
152. 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)
153. 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)
154. 0.000 0.000 ↓ 0.0 0

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

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

Initplan (for Result)

156. 0.000 0.000 ↓ 0.0 0

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

157. 0.000 0.000 ↓ 0.0 0

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

158. 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)
159. 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)

160. 0.000 0.000 ↓ 0.0 0

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

161. 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)
162. 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)
163. 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)
164. 0.000 0.000 ↓ 0.0 0

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

165. 0.000 0.000 ↓ 0.0 0

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

  • One-Time Filter: $6
166.          

Initplan (for Result)

167. 0.000 0.000 ↓ 0.0 0

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

168. 0.000 0.000 ↓ 0.0 0

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

169. 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))
170. 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 : 34.580 ms
Execution time : 14,595.427 ms