no-collapse.txt
text/plain
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=217.06..334.09 rows=1 width=133) (actual time=0.972..0.990 rows=1 loops=1)
-> Nested Loop (cost=162.73..163.99 rows=1 width=134) (actual time=0.472..0.484 rows=1 loops=1)
-> Nested Loop (cost=108.55..109.77 rows=1 width=134) (actual time=0.345..0.355 rows=1 loops=1)
-> Nested Loop (cost=54.20..55.41 rows=1 width=102) (actual time=0.190..0.199 rows=1 loops=1)
-> Seq Scan on pg_authid (cost=0.00..1.19 rows=1 width=70) (actual time=0.020..0.027 rows=1 loops=1)
Filter: (rolname ~ 'u6_green'::text)
Rows Removed by Filter: 21
-> Aggregate (cost=54.20..54.21 rows=1 width=32) (actual time=0.168..0.170 rows=1 loops=1)
-> Sort (cost=54.20..54.20 rows=1 width=4) (actual time=0.167..0.169 rows=0 loops=1)
Sort Key: cte_role_relationship.group_node
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=51.88..54.19 rows=1 width=4) (actual time=0.165..0.166 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.067..0.152 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.004..0.119 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_13 (cost=0.00..1.15 rows=15 width=181) (actual time=0.003..0.005 rows=22 loops=1)
-> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.007..0.018 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.001 rows=11 loops=5)
-> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.007..0.008 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.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.154..0.155 rows=1 loops=1)
-> Sort (cost=54.33..54.34 rows=1 width=4) (actual time=0.150..0.152 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.056..0.149 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.051..0.132 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.101 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_14 (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.015 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.001 rows=11 loops=5)
-> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.006..0.006 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.002..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.126..0.128 rows=1 loops=1)
-> Nested Loop (cost=51.88..54.19 rows=1 width=0) (actual time=0.126..0.127 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.126..0.126 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.096 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_15 (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.015 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.001 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_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.126..0.127 rows=1 loops=1)
-> Nested Loop (cost=51.88..54.32 rows=1 width=0) (actual time=0.126..0.126 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.125..0.126 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.004..0.096 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_16 (cost=0.00..1.15 rows=15 width=181) (actual time=0.004..0.006 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_5.group_node = m_5.member)
Join Filter: (((NOT m_5.set_option) AND (NOT m_5.inherit_option) AND (NOT m_5.admin_option) AND ((a_5.level + 1) <= 1)) OR (COALESCE(a_5.got_set, true) AND m_5.set_option) OR (COALESCE(a_5.got_inherit, true) AND m_5.inherit_option) OR (COALESCE(a_5.got_inherit_via_set, false) AND m_5.inherit_option) OR (COALESCE(a_5.got_set, false) AND (NOT COALESCE(a_5.got_inherit, false)) AND (NOT m_5.set_option) AND m_5.inherit_option) OR (COALESCE(m_5.admin_option, false) AND (((a_5.level + 1) = 1) OR (a_5.got_set AND m_5.set_option) OR (a_5.got_inherit AND m_5.inherit_option) OR (COALESCE(a_5.got_inherit_via_set, false) AND m_5.inherit_option) OR (COALESCE(a_5.got_set, false) AND (NOT COALESCE(a_5.got_inherit, false)) AND (NOT m_5.set_option) AND m_5.inherit_option))))
Rows Removed by Join Filter: 2
-> WorkTable Scan on cte_role_relationship a_5 (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.005..0.005 rows=17 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pg_auth_members m_5 (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 3
-> Append (cost=56.01..115.74 rows=2 width=32) (actual time=0.358..0.373 rows=5 loops=1)
-> Subquery Scan on "*SELECT* 1_1" (cost=56.01..57.87 rows=1 width=32) (actual time=0.152..0.154 rows=0 loops=1)
-> GroupAggregate (cost=56.01..57.86 rows=1 width=128) (actual time=0.151..0.153 rows=0 loops=1)
Group Key: grant_instance.via, pg_authid_7.rolname
-> Incremental Sort (cost=56.01..57.62 rows=2 width=265) (actual time=0.151..0.153 rows=0 loops=1)
Sort Key: grant_instance.grantor, pg_authid_7.rolname, grant_instance.via, grant_instance.level, grant_instance.grantor_path
Presorted Key: grant_instance.grantor
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 25kB Peak Memory: 25kB
-> Nested Loop Left Join (cost=54.47..57.53 rows=1 width=265) (actual time=0.147..0.150 rows=0 loops=1)
Join Filter: (pg_authid_9.oid = grant_instance.via[(cardinality(grant_instance.via) - 1)])
-> Nested Loop (cost=54.47..56.12 rows=1 width=201) (actual time=0.147..0.149 rows=0 loops=1)
Join Filter: (pg_authid_7.oid = other.other)
-> Nested Loop (cost=54.47..54.78 rows=1 width=145) (actual time=0.147..0.149 rows=0 loops=1)
Join Filter: (other.other = grant_instance.group_node)
-> Merge Join (cost=54.46..54.56 rows=1 width=141) (actual time=0.142..0.146 rows=5 loops=1)
Merge Cond: (grant_instance.grantor = pg_authid_8.oid)
-> Sort (cost=53.02..53.03 rows=1 width=77) (actual time=0.133..0.135 rows=5 loops=1)
Sort Key: grant_instance.grantor
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on grant_instance (cost=51.88..53.01 rows=1 width=77) (actual time=0.052..0.132 rows=5 loops=1)
-> CTE Scan on cte_role_relationship cte_role_relationship_4 (cost=51.88..53.00 rows=1 width=181) (actual time=0.052..0.131 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.097 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.005..0.015 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.001 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)
-> Sort (cost=1.44..1.48 rows=15 width=68) (actual time=0.007..0.007 rows=16 loops=1)
Sort Key: pg_authid_8.oid
Sort Method: quicksort Memory: 27kB
-> Seq Scan on pg_authid pg_authid_8 (cost=0.00..1.15 rows=15 width=68) (actual time=0.002..0.003 rows=22 loops=1)
-> Function Scan on unnest other (cost=0.00..0.10 rows=10 width=4) (actual time=0.000..0.000 rows=0 loops=5)
-> Seq Scan on pg_authid pg_authid_7 (cost=0.00..1.15 rows=15 width=68) (never executed)
-> Seq Scan on pg_authid pg_authid_9 (cost=0.00..1.15 rows=15 width=68) (never executed)
-> Subquery Scan on "*SELECT* 2" (cost=56.01..57.87 rows=1 width=32) (actual time=0.205..0.217 rows=5 loops=1)
-> GroupAggregate (cost=56.01..57.86 rows=1 width=128) (actual time=0.205..0.216 rows=5 loops=1)
Group Key: grant_instance_1.via, pg_authid_10.rolname
-> Incremental Sort (cost=56.01..57.62 rows=2 width=265) (actual time=0.193..0.195 rows=5 loops=1)
Sort Key: grant_instance_1.grantor, pg_authid_10.rolname, grant_instance_1.via, grant_instance_1.level, grant_instance_1.grantor_path
Presorted Key: grant_instance_1.grantor
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB
-> Nested Loop Left Join (cost=54.47..57.53 rows=1 width=265) (actual time=0.154..0.189 rows=5 loops=1)
Join Filter: (pg_authid_12.oid = grant_instance_1.via[(cardinality(grant_instance_1.via) - 1)])
Rows Removed by Join Filter: 110
-> Nested Loop (cost=54.47..56.12 rows=1 width=201) (actual time=0.148..0.165 rows=5 loops=1)
Join Filter: (pg_authid_10.oid = other_1.other)
Rows Removed by Join Filter: 97
-> Nested Loop (cost=54.47..54.78 rows=1 width=145) (actual time=0.144..0.151 rows=5 loops=1)
Join Filter: (other_1.other = grant_instance_1.group_node)
Rows Removed by Join Filter: 15
-> Merge Join (cost=54.46..54.56 rows=1 width=141) (actual time=0.140..0.144 rows=5 loops=1)
Merge Cond: (grant_instance_1.grantor = pg_authid_11.oid)
-> Sort (cost=53.02..53.03 rows=1 width=77) (actual time=0.133..0.134 rows=5 loops=1)
Sort Key: grant_instance_1.grantor
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on grant_instance_1 (cost=51.88..53.01 rows=1 width=77) (actual time=0.053..0.132 rows=5 loops=1)
-> CTE Scan on cte_role_relationship cte_role_relationship_5 (cost=51.88..53.00 rows=1 width=181) (actual time=0.052..0.131 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.003..0.097 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_6 (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.015 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.005..0.006 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.001..0.003 rows=17 loops=1)
-> Sort (cost=1.44..1.48 rows=15 width=68) (actual time=0.006..0.007 rows=16 loops=1)
Sort Key: pg_authid_11.oid
Sort Method: quicksort Memory: 27kB
-> Seq Scan on pg_authid pg_authid_11 (cost=0.00..1.15 rows=15 width=68) (actual time=0.001..0.003 rows=22 loops=1)
-> Function Scan on unnest other_1 (cost=0.00..0.10 rows=10 width=4) (actual time=0.000..0.001 rows=4 loops=5)
-> Seq Scan on pg_authid pg_authid_10 (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_12 (cost=0.00..1.15 rows=15 width=68) (actual time=0.000..0.001 rows=22 loops=5)
Planning Time: 4.502 ms
Execution Time: 1.245 ms
(180 rows)