pgjdbc: Retrieving system column data types is slow

I’m submitting a …

• bug report
• feature request

Describe the issue Retrieving system column data types is slow, and noticeable with remote servers. The driver first runs a query to get the available types, in PgDatabaseMetaData.java#L2224-L2229

SELECT t.typname,t.oid FROM pg_catalog.pg_type t 
JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid)  
WHERE n.nspname  != 'pg_toast'
(t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid))

Then in in PgDatabaseMetaData.java#L1138-L1139 (which in turn calls TypeInfoCache.java#L204-L216) the driver runs the following query in a loop for each type:

SELECT typinput='array_in'::regproc, typtype   
FROM pg_catalog.pg_type   
LEFT   
JOIN (select ns.oid as nspoid, ns.nspname, r.r           
        from pg_namespace as ns           
        join ( select s.r, (current_schemas(false))[s.r] as nspname                   
        from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r          
        using ( nspname )        
      ) as sp     
   ON sp.nspoid = typnamespace  
WHERE typname = $1  
ORDER BY sp.r, pg_type.oid DESC LIMIT 1`

From @pyrocks in https://github.com/schemacrawler/SchemaCrawler/issues/229 - This looks inefficient. I am certain the info can be found in a single fetch and then parsed by code.

Driver Version? 42.2.5

Java Version? Any

OS Version? Any

PostgreSQL Version? Any

To Reproduce Steps to reproduce the behaviour: N/A

Expected behaviour See description for an idea on how to make getTypeInfo more efficient.

Logs None