I'm interested in the members of specific roles, providing the roles of
interest to the query via an array of integers (binary bind in code, not
textual array literal like I had to use to have the EXPLAIN work, see
below).
I figured that query would use the "pg_auth_members_role_member_index"
index,
but instead it's using a sequential scan.
And I'm wondering is this is because the cardinality of that catalog is
small (172),
which is just an artifact of my dev-testing, or whether that's because I
cast roleid
to an int4, preventing the use of the index?
In production, the cardinality will be much greator, which is why I worry a
bit.
Also, I don't really need the grantor and admin_option columns for now, thus
it could even be an index-only scan, IF the index was used by the plan.
I tried changing the cast around, or allowing an index-only scan,
but it's still a Seq Scan on the table (see below).
Is there a way to know why the index is not used, in any of my attempts?
I currently does not support (binary) binding Oids in my case, thus the
::int4 casts.
Would supporting binding actual Oid arrays instead of Int4 arrays help in
this case?
I'd appreciate some insights here. Thanks, --DD
PS: libpq uses unsigned int for Oid. So do OIDs go above 2GB in practice?
I'm asking, since I'm casting to ::int4, thus if they do, then that
case might overflow.
PPS: Are OIDs recycled / reused? Or are they monotonically increasing?
What happens when the Cluster runs out of OIDs?
Are they Cluster-wide unique or it depends on the OID type?
dd_pns2=> explain SELECT roleid::int4, member::int4, grantor::int4,
admin_option
dd_pns2-> FROM pg_auth_members
dd_pns2-> WHERE roleid::int4 = ANY($1);
ERROR: there is no parameter $1
LINE 3: WHERE roleid::int4 = ANY($1);
^
dd_pns2=> explain SELECT roleid::int4, member::int4, grantor::int4,
admin_option
dd_pns2-> FROM pg_auth_members
dd_pns2-> WHERE roleid::int4 = ANY(array[1,2,3]);
QUERY PLAN
----------------------------------------------------------------
Seq Scan on pg_auth_members (cost=0.00..5.33 rows=3 width=13)
Filter: ((roleid)::integer = ANY ('{1,2,3}'::integer[]))
(2 rows)
dd_pns2=> explain SELECT roleid::int4, member::int4, grantor::int4,
admin_option
dd_pns2-> FROM pg_auth_members
dd_pns2-> WHERE roleid = ANY(array[1::oid,2::oid,3::oid]);
QUERY PLAN
----------------------------------------------------------------
Seq Scan on pg_auth_members (cost=0.00..5.33 rows=3 width=13)
Filter: (roleid = ANY ('{1,2,3}'::oid[]))
(2 rows)
dd_pns2=> explain SELECT roleid::int4, member::int4
dd_pns2-> FROM pg_auth_members
dd_pns2-> WHERE roleid = ANY(array[1::oid,2::oid,3::oid]);
QUERY PLAN
---------------------------------------------------------------
Seq Scan on pg_auth_members (cost=0.00..5.33 rows=3 width=8)
Filter: (roleid = ANY ('{1,2,3}'::oid[]))
(2 rows)
dd_pns2=> \d pg_auth_members
Table "pg_catalog.pg_auth_members"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
roleid | oid | | not null |
member | oid | | not null |
grantor | oid | | not null |
admin_option | boolean | | not null |
Indexes:
"pg_auth_members_member_role_index" UNIQUE, btree (member, roleid),
tablespace "pg_global"
"pg_auth_members_role_member_index" UNIQUE, btree (roleid, member),
tablespace "pg_global"
Tablespace: "pg_global"
dd_pns2=> select count(*) from pg_auth_members;
count
-------
172
(1 row)
