sql: investigate composite type introspection slowness

[annrpom]

Repro:

demo@127.0.0.1:26257/demoapp/movr> create type t as (foo int, bar int);                                                    
CREATE TYPE

Time: 5ms total (execution 5ms / network 0ms)

demo@127.0.0.1:26257/demoapp/movr> select attrelid::regclass, attname, atttypid::regtype, attnum from pg_attribute where   
                                -> attrelid = 't'::regclass;                                                               
  attrelid | attname | atttypid | attnum
-----------+---------+----------+---------
  t        | foo     | int8     |      1
  t        | bar     | int8     |      2
(2 rows)

Time: 15.917s total (execution 15.917s / network 0.000s)

Observe the 15.917s total latency on the introspection query

However, if we lookup t's oid via the pg_type table (cast to regtype instead of regclass), we get what we expect:

demo@127.0.0.1:26257/demoapp/movr> select attrelid::regclass, attname, atttypid::regtype, attnum from pg_attribute where   
                                -> attrelid = 't'::regtype;                                                                
  attrelid | attname | atttypid | attnum
-----------+---------+----------+---------
  t        | foo     | int8     |      1
  t        | bar     | int8     |      2
(2 rows)

Time: 7ms total (execution 6ms / network 0ms)

So it looks like pg_type is good - something in pg_class is likely the problem.

While we are here, we can see/keep track of how many round trips the query does by adding this to rttanalysis tests.

Jira issue: CRDB-37145

[fqazi]

@annrpom Did this come from a support escalation? If it did can we add the tags on this issue for that

[annrpom]

It did not @fqazi 🙇