Seq Scan because of stats or because of cast?

[Dominique Devienne <ddevienne@xxxxxxxxx>]

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)