QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=217.04..275.81 rows=1 width=133) (actual time=0.754..0.766 rows=1 loops=1) -> Nested Loop (cost=162.71..163.98 rows=1 width=102) (actual time=0.438..0.447 rows=1 loops=1) -> Nested Loop (cost=108.53..109.77 rows=1 width=102) (actual time=0.319..0.327 rows=1 loops=1) -> Nested Loop (cost=54.19..55.40 rows=1 width=70) (actual time=0.174..0.181 rows=1 loops=1) -> Seq Scan on pg_authid (cost=0.00..1.19 rows=1 width=70) (actual time=0.018..0.023 rows=1 loops=1) Filter: (rolname ~ 'u6_green'::text) Rows Removed by Filter: 21 -> Aggregate (cost=54.19..54.20 rows=1 width=32) (actual time=0.155..0.156 rows=1 loops=1) -> Nested Loop (cost=51.88..54.19 rows=1 width=0) (actual time=0.154..0.155 rows=0 loops=1) Join Filter: (pg_authid_1.oid = cte_role_relationship.group_node) Rows Removed by Join Filter: 20 -> CTE Scan on cte_role_relationship (cost=51.88..53.00 rows=1 width=181) (actual time=0.061..0.141 rows=5 loops=1) Filter: ((pg_authid.oid <> group_node) AND (leaf_node = pg_authid.oid)) Rows Removed by Filter: 51 CTE cte_role_relationship -> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.003..0.110 rows=56 loops=1) -> Seq Scan on pg_authid pg_authid_9 (cost=0.00..1.15 rows=15 width=181) (actual time=0.002..0.004 rows=22 loops=1) -> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.006..0.017 rows=7 loops=5) Hash Cond: (a_1.group_node = m_1.member) Join Filter: (((NOT m_1.set_option) AND (NOT m_1.inherit_option) AND (NOT m_1.admin_option) AND ((a_1.level + 1) <= 1)) OR (COALESCE(a_1.got_set, true) AND m_1.set_option) OR (COALESCE(a_1.got_inherit, true) AND m_1.inherit_option) OR (COALESCE(a_1.got_inherit_via_set, false) AND m_1.inherit_option) OR (COALESCE(a_1.got_set, false) AND (NOT COALESCE(a_1.got_inherit, false)) AND (NOT m_1.set_option) AND m_1.inherit_option) OR (COALESCE(m_1.admin_option, false) AND (((a_1.level + 1) = 1) OR (a_1.got_set AND m_1.set_option) OR (a_1.got_inherit AND m_1.inherit_option) OR (COALESCE(a_1.got_inherit_via_set, false) AND m_1.inherit_option) OR (COALESCE(a_1.got_set, false) AND (NOT COALESCE(a_1.got_inherit, false)) AND (NOT m_1.set_option) AND m_1.inherit_option)))) Rows Removed by Join Filter: 2 -> WorkTable Scan on cte_role_relationship a_1 (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.001 rows=11 loops=5) -> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.007..0.007 rows=17 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on pg_auth_members m_1 (cost=0.00..1.03 rows=3 width=15) (actual time=0.002..0.004 rows=17 loops=1) -> Seq Scan on pg_authid pg_authid_1 (cost=0.00..1.15 rows=2 width=4) (actual time=0.001..0.002 rows=4 loops=5) Filter: rolcanlogin Rows Removed by Filter: 18 -> Aggregate (cost=54.34..54.35 rows=1 width=32) (actual time=0.145..0.146 rows=1 loops=1) -> Sort (cost=54.33..54.34 rows=1 width=4) (actual time=0.141..0.143 rows=5 loops=1) Sort Key: cte_role_relationship_1.group_node Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=51.88..54.32 rows=1 width=4) (actual time=0.051..0.139 rows=5 loops=1) Join Filter: (pg_authid_2.oid = cte_role_relationship_1.group_node) Rows Removed by Join Filter: 77 -> CTE Scan on cte_role_relationship cte_role_relationship_1 (cost=51.88..53.00 rows=1 width=181) (actual time=0.047..0.123 rows=5 loops=1) Filter: ((pg_authid.oid <> group_node) AND (leaf_node = pg_authid.oid)) Rows Removed by Filter: 51 CTE cte_role_relationship -> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.002..0.093 rows=56 loops=1) -> Seq Scan on pg_authid pg_authid_10 (cost=0.00..1.15 rows=15 width=181) (actual time=0.002..0.004 rows=22 loops=1) -> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.005..0.014 rows=7 loops=5) Hash Cond: (a_2.group_node = m_2.member) Join Filter: (((NOT m_2.set_option) AND (NOT m_2.inherit_option) AND (NOT m_2.admin_option) AND ((a_2.level + 1) <= 1)) OR (COALESCE(a_2.got_set, true) AND m_2.set_option) OR (COALESCE(a_2.got_inherit, true) AND m_2.inherit_option) OR (COALESCE(a_2.got_inherit_via_set, false) AND m_2.inherit_option) OR (COALESCE(a_2.got_set, false) AND (NOT COALESCE(a_2.got_inherit, false)) AND (NOT m_2.set_option) AND m_2.inherit_option) OR (COALESCE(m_2.admin_option, false) AND (((a_2.level + 1) = 1) OR (a_2.got_set AND m_2.set_option) OR (a_2.got_inherit AND m_2.inherit_option) OR (COALESCE(a_2.got_inherit_via_set, false) AND m_2.inherit_option) OR (COALESCE(a_2.got_set, false) AND (NOT COALESCE(a_2.got_inherit, false)) AND (NOT m_2.set_option) AND m_2.inherit_option)))) Rows Removed by Join Filter: 2 -> WorkTable Scan on cte_role_relationship a_2 (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.000 rows=11 loops=5) -> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.005..0.006 rows=17 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on pg_auth_members m_2 (cost=0.00..1.03 rows=3 width=15) (actual time=0.001..0.003 rows=17 loops=1) -> Seq Scan on pg_authid pg_authid_2 (cost=0.00..1.15 rows=13 width=4) (actual time=0.001..0.002 rows=16 loops=5) Filter: (NOT rolcanlogin) Rows Removed by Filter: 4 -> Aggregate (cost=54.19..54.20 rows=1 width=32) (actual time=0.118..0.119 rows=1 loops=1) -> Nested Loop (cost=51.88..54.19 rows=1 width=0) (actual time=0.118..0.119 rows=0 loops=1) Join Filter: (pg_authid_3.oid = cte_role_relationship_2.leaf_node) -> CTE Scan on cte_role_relationship cte_role_relationship_2 (cost=51.88..53.00 rows=1 width=181) (actual time=0.118..0.118 rows=0 loops=1) Filter: ((pg_authid.oid <> leaf_node) AND (group_node = pg_authid.oid)) Rows Removed by Filter: 56 CTE cte_role_relationship -> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.002..0.090 rows=56 loops=1) -> Seq Scan on pg_authid pg_authid_11 (cost=0.00..1.15 rows=15 width=181) (actual time=0.002..0.004 rows=22 loops=1) -> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.004..0.014 rows=7 loops=5) Hash Cond: (a_3.group_node = m_3.member) Join Filter: (((NOT m_3.set_option) AND (NOT m_3.inherit_option) AND (NOT m_3.admin_option) AND ((a_3.level + 1) <= 1)) OR (COALESCE(a_3.got_set, true) AND m_3.set_option) OR (COALESCE(a_3.got_inherit, true) AND m_3.inherit_option) OR (COALESCE(a_3.got_inherit_via_set, false) AND m_3.inherit_option) OR (COALESCE(a_3.got_set, false) AND (NOT COALESCE(a_3.got_inherit, false)) AND (NOT m_3.set_option) AND m_3.inherit_option) OR (COALESCE(m_3.admin_option, false) AND (((a_3.level + 1) = 1) OR (a_3.got_set AND m_3.set_option) OR (a_3.got_inherit AND m_3.inherit_option) OR (COALESCE(a_3.got_inherit_via_set, false) AND m_3.inherit_option) OR (COALESCE(a_3.got_set, false) AND (NOT COALESCE(a_3.got_inherit, false)) AND (NOT m_3.set_option) AND m_3.inherit_option)))) Rows Removed by Join Filter: 2 -> WorkTable Scan on cte_role_relationship a_3 (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.000 rows=11 loops=5) -> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.005..0.005 rows=17 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on pg_auth_members m_3 (cost=0.00..1.03 rows=3 width=15) (actual time=0.001..0.003 rows=17 loops=1) -> Seq Scan on pg_authid pg_authid_3 (cost=0.00..1.15 rows=2 width=4) (never executed) Filter: rolcanlogin -> Aggregate (cost=54.32..54.33 rows=1 width=32) (actual time=0.116..0.117 rows=1 loops=1) -> Nested Loop (cost=51.88..54.32 rows=1 width=0) (actual time=0.116..0.117 rows=0 loops=1) Join Filter: (pg_authid_4.oid = cte_role_relationship_3.leaf_node) -> CTE Scan on cte_role_relationship cte_role_relationship_3 (cost=51.88..53.00 rows=1 width=181) (actual time=0.116..0.116 rows=0 loops=1) Filter: ((pg_authid.oid <> leaf_node) AND (group_node = pg_authid.oid)) Rows Removed by Filter: 56 CTE cte_role_relationship -> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.002..0.088 rows=56 loops=1) -> Seq Scan on pg_authid pg_authid_12 (cost=0.00..1.15 rows=15 width=181) (actual time=0.002..0.004 rows=22 loops=1) -> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.004..0.013 rows=7 loops=5) Hash Cond: (a_4.group_node = m_4.member) Join Filter: (((NOT m_4.set_option) AND (NOT m_4.inherit_option) AND (NOT m_4.admin_option) AND ((a_4.level + 1) <= 1)) OR (COALESCE(a_4.got_set, true) AND m_4.set_option) OR (COALESCE(a_4.got_inherit, true) AND m_4.inherit_option) OR (COALESCE(a_4.got_inherit_via_set, false) AND m_4.inherit_option) OR (COALESCE(a_4.got_set, false) AND (NOT COALESCE(a_4.got_inherit, false)) AND (NOT m_4.set_option) AND m_4.inherit_option) OR (COALESCE(m_4.admin_option, false) AND (((a_4.level + 1) = 1) OR (a_4.got_set AND m_4.set_option) OR (a_4.got_inherit AND m_4.inherit_option) OR (COALESCE(a_4.got_inherit_via_set, false) AND m_4.inherit_option) OR (COALESCE(a_4.got_set, false) AND (NOT COALESCE(a_4.got_inherit, false)) AND (NOT m_4.set_option) AND m_4.inherit_option)))) Rows Removed by Join Filter: 2 -> WorkTable Scan on cte_role_relationship a_4 (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.000 rows=11 loops=5) -> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.005..0.005 rows=17 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on pg_auth_members m_4 (cost=0.00..1.03 rows=3 width=15) (actual time=0.001..0.003 rows=17 loops=1) -> Seq Scan on pg_authid pg_authid_4 (cost=0.00..1.15 rows=13 width=4) (never executed) Filter: (NOT rolcanlogin) SubPlan 2 -> GroupAggregate (cost=57.34..57.47 rows=1 width=128) (actual time=0.186..0.197 rows=4 loops=1) Group Key: pg_authid_6.rolname, cte_role_relationship_4.via -> Sort (cost=57.34..57.34 rows=1 width=265) (actual time=0.176..0.177 rows=5 loops=1) Sort Key: pg_authid_6.rolname, cte_role_relationship_4.via, cte_role_relationship_4.level, cte_role_relationship_4.grantor, cte_role_relationship_4.grantor_path Sort Method: quicksort Memory: 26kB -> Nested Loop Left Join (cost=51.88..57.33 rows=1 width=265) (actual time=0.062..0.171 rows=5 loops=1) Join Filter: (pg_authid_8.oid = cte_role_relationship_4.via[(cardinality(cte_role_relationship_4.via) - 1)]) Rows Removed by Join Filter: 110 -> Nested Loop (cost=51.88..55.91 rows=1 width=201) (actual time=0.056..0.152 rows=5 loops=1) Join Filter: (pg_authid_7.oid = cte_role_relationship_4.grantor) Rows Removed by Join Filter: 15 -> Nested Loop (cost=51.88..54.58 rows=1 width=137) (actual time=0.054..0.145 rows=5 loops=1) Join Filter: (pg_authid_6.oid = other.other) Rows Removed by Join Filter: 97 -> Nested Loop (cost=51.88..53.24 rows=1 width=81) (actual time=0.050..0.131 rows=5 loops=1) Join Filter: (other.other = cte_role_relationship_4.group_node) Rows Removed by Join Filter: 15 -> CTE Scan on cte_role_relationship cte_role_relationship_4 (cost=51.88..53.00 rows=1 width=181) (actual time=0.047..0.124 rows=5 loops=1) Filter: (((got_admin AND (leaf_node <> grantor)) OR grant_is_empty) AND (leaf_node = pg_authid.oid)) Rows Removed by Filter: 51 CTE cte_role_relationship -> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.002..0.092 rows=56 loops=1) -> Seq Scan on pg_authid pg_authid_5 (cost=0.00..1.15 rows=15 width=181) (actual time=0.002..0.004 rows=22 loops=1) -> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.004..0.014 rows=7 loops=5) Hash Cond: (a.group_node = m.member) Join Filter: (((NOT m.set_option) AND (NOT m.inherit_option) AND (NOT m.admin_option) AND ((a.level + 1) <= 1)) OR (COALESCE(a.got_set, true) AND m.set_option) OR (COALESCE(a.got_inherit, true) AND m.inherit_option) OR (COALESCE(a.got_inherit_via_set, false) AND m.inherit_option) OR (COALESCE(a.got_set, false) AND (NOT COALESCE(a.got_inherit, false)) AND (NOT m.set_option) AND m.inherit_option) OR (COALESCE(m.admin_option, false) AND (((a.level + 1) = 1) OR (a.got_set AND m.set_option) OR (a.got_inherit AND m.inherit_option) OR (COALESCE(a.got_inherit_via_set, false) AND m.inherit_option) OR (COALESCE(a.got_set, false) AND (NOT COALESCE(a.got_inherit, false)) AND (NOT m.set_option) AND m.inherit_option)))) Rows Removed by Join Filter: 2 -> WorkTable Scan on cte_role_relationship a (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.000 rows=11 loops=5) -> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.005..0.005 rows=17 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on pg_auth_members m (cost=0.00..1.03 rows=3 width=15) (actual time=0.001..0.003 rows=17 loops=1) -> Function Scan on unnest other (cost=0.00..0.10 rows=10 width=4) (actual time=0.001..0.001 rows=4 loops=5) -> Seq Scan on pg_authid pg_authid_6 (cost=0.00..1.15 rows=15 width=68) (actual time=0.001..0.001 rows=20 loops=5) -> Seq Scan on pg_authid pg_authid_7 (cost=0.00..1.15 rows=15 width=68) (actual time=0.000..0.001 rows=4 loops=5) -> Seq Scan on pg_authid pg_authid_8 (cost=0.00..1.15 rows=15 width=68) (actual time=0.001..0.002 rows=22 loops=5) Planning Time: 3.037 ms Execution Time: 0.924 ms (128 rows)