Seq Scan because of stats or because of cast? - Mailing list pgsql-general
| From | Dominique Devienne |
|---|---|
| Subject | Seq Scan because of stats or because of cast? |
| Date | |
| Msg-id | CAFCRh-_Ptmhdj3Ly-bdZLqxzG6UijtE=9zitcHhNMGB0A5V93Q@mail.gmail.com Whole thread Raw |
| Responses |
Re: Seq Scan because of stats or because of cast?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-general |
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).
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)
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-> 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-> 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)
pgsql-general by date: