sql: improve REGPROCEDURE cast performance #61082
Description
ActiveRecord executes a query on the pg_type table when initiating a connection (full query below). This query has a filter with a ::REGPROCEDURE cast which is extremely slow to execute. Ideally we can improve the performance of this cast. In cockroach demo this cast adds a few hundred milliseconds of latency to the query. In a multi-node cluster, it adds 2+ seconds of latency.
Example (in cockroach demo).
defaultdb> SELECT count(*) FROM pg_type;
count
---------
75
(1 row)
Time: 2ms total (execution 2ms / network 0ms)
defaultdb> SELECT count(*) FROM pg_type WHERE typinput = 'array_in(cstring,oid,integer)'::REGPROCEDURE;
count
---------
36
(1 row)
Time: 528ms total (execution 527ms / network 0ms)
Complete ActiveRecord query
SELECT
t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM
pg_type AS t LEFT JOIN pg_range AS r ON oid = rngtypid
WHERE
t.typname
IN (
'int2',
'int4',
'int8',
'oid',
'float4',
'float8',
'text',
'varchar',
'char',
'name',
'bpchar',
'bool',
'bit',
'varbit',
'timestamptz',
'date',
'money',
'bytea',
'point',
'hstore',
'json',
'jsonb',
'cidr',
'inet',
'uuid',
'xml',
'tsvector',
'macaddr',
'citext',
'ltree',
'line',
'lseg',
'box',
'path',
'polygon',
'circle',
'interval',
'time',
'timestamp',
'numeric'
)
OR t.typtype IN ('r', 'e', 'd')
OR t.typinput = 'array_in(cstring,oid,integer)'::REGPROCEDURE
OR t.typelem != 0