Using system tables directly takes many hours, using temp tables with no indexes takes a few seconds for geometry_columns view.

[Lars Aksel Opsahl]

Hi

We are running
postgres server 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="120" GEOS="3.9.0-CAPI-1.16.2" 
SFCGAL="1.3.7" PROJ="7.2.1" GDAL="GDAL 3.2.1, released 2020/12/29" 
LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" 
TOPOLOGY RASTER

The problem is that it takes more than 10 hours (duration: 36885527.039) to 
browse tables geometry from qgis https://explain.depesz.com/s/MxAN#bquery with 
high load on the server.
We have at least 45 jobs running and around 70% CPU load on the server.

Then I started to check views/tables involved and found that the view 
geometry_columns seems to be using a very long time
'explain analyze select * from geometry_columns' have been waiting for more 
than 2 hours now, will paste the result to https://explain.depesz.com when done.

While waiting I created temp table for the system tables involved in view 
geometry_columns like this

create temp table pg_attribute_temp as select 
attcollation,attrelid,attname,atttypid,attstattarget,attlen,attnum,attndims,attcacheoff,atttypmod,attbyval,attstorage,attalign,attnotnull,atthasdef,atthasmissing,attidentity,attgenerated,attisdropped,attislocal,attinhcount,attacl,attoptions,attfdwoptions
 from pg_attribute;
create temp table pg_namespace_temp as select * from pg_namespace;
create temp table pg_type_temp as select * from pg_type;
create temp table pg_constraint_temp as select * from pg_constraint;

SELECT 1702623
Time: 42552.899 ms (00:42.553)
SELECT 841
Time: 132.595 ms
SELECT 245239
Time: 3378.395 ms (00:03.378)
SELECT 9575
Time: 205.036 ms

That did not take very long time.

Then created geometry_columns_temp_no_rules using those new temp tables.

explain analyze select * from geometry_columns_temp_no_rules

And that takes less than 6 seconds with no indexes. Here is the explain from 
https://explain.depesz.com/s/yBSd

Why is temp tables with no indexes much faster system tables with indexes ?

(I do not think it's related to not having rules I tested to crated a view 
using system tables with but with no rules and that hanged for more that 15 
minuttes an dthen I gave up)

Here is the view def that I used.

CREATE VIEW geometry_columns_temp_no_rules AS
SELECT current_database()::character varying(256) AS f_table_catalog,
    n.nspname AS f_table_schema,
    c.relname AS f_table_name,
    a.attname AS f_geometry_column,
    COALESCE(postgis_typmod_dims(a.atttypmod), sn.ndims, 2) AS coord_dimension,
    COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod), 0), sr.srid, 0) AS srid,
    replace(replace(COALESCE(NULLIF(upper(postgis_typmod_type(a.atttypmod)), 
'GEOMETRY'::text), st.type, 'GEOMETRY'::text), 'ZM'::text, ''::text), 
'Z'::text, ''::text)::character varying(30) AS type
   FROM pg_class c
     JOIN pg_attribute_temp a ON a.attrelid = c.oid AND NOT a.attisdropped
     JOIN pg_namespace_temp n ON c.relnamespace = n.oid
     JOIN pg_type_temp t ON a.atttypid = t.oid
     LEFT JOIN ( SELECT s.connamespace,
            s.conrelid,
            s.conkey,
            replace(split_part(s.consrc, ''''::text, 2), ')'::text, ''::text) 
AS type
           FROM ( SELECT pg_constraint_temp.connamespace,
                    pg_constraint_temp.conrelid,
                    pg_constraint_temp.conkey,
                    pg_get_constraintdef(pg_constraint_temp.oid) AS consrc
                   FROM pg_constraint_temp) s
          WHERE s.consrc ~~* '%geometrytype(% = %'::text) st ON st.connamespace 
= n.oid AND st.conrelid = c.oid AND (a.attnum = ANY (st.conkey))
     LEFT JOIN ( SELECT s.connamespace,
            s.conrelid,
            s.conkey,
            replace(split_part(s.consrc, ' = '::text, 2), ')'::text, 
''::text)::integer AS ndims
           FROM ( SELECT pg_constraint_temp.connamespace,
                    pg_constraint_temp.conrelid,
                    pg_constraint_temp.conkey,
                    pg_get_constraintdef(pg_constraint_temp.oid) AS consrc
                   FROM pg_constraint_temp) s
          WHERE s.consrc ~~* '%ndims(% = %'::text) sn ON sn.connamespace = 
n.oid AND sn.conrelid = c.oid AND (a.attnum = ANY (sn.conkey))
     LEFT JOIN ( SELECT s.connamespace,
            s.conrelid,
            s.conkey,
            replace(replace(split_part(s.consrc, ' = '::text, 2), ')'::text, 
''::text), '('::text, ''::text)::integer AS srid
           FROM ( SELECT pg_constraint_temp.connamespace,
                    pg_constraint_temp.conrelid,
                    pg_constraint_temp.conkey,
                    pg_get_constraintdef(pg_constraint_temp.oid) AS consrc
                   FROM pg_constraint_temp) s
          WHERE s.consrc ~~* '%srid(% = %'::text) sr ON sr.connamespace = n.oid 
AND sr.conrelid = c.oid AND (a.attnum = ANY (sr.conkey))
  WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", 
'f'::"char", 'p'::"char"])) AND NOT c.relname = 'raster_columns'::name AND 
t.typname = 'geometry'::name AND NOT pg_is_other_temp_schema(c.relnamespace) 
AND has_table_privilege(c.oid, 'SELECT'::text);
;

Thanks.

Lars