[postgis-tickets] [PostGIS] #3092: Slow performance of geometry_columns in PostGIS >= 2

[PostGIS]

#3092: Slow performance of geometry_columns in PostGIS >= 2
--------------------------+---------------------------
  Reporter:  rouault      |      Owner:  pramsey
      Type:  enhancement  |     Status:  new
  Priority:  high         |  Milestone:  PostGIS 2.1.8
 Component:  postgis      |    Version:  2.0.x
Resolution:               |   Keywords:
--------------------------+---------------------------

Comment (by robe):

 Hmm and yet another version -- lateral beats all but would require putting
 in conditional for > 9.2


 {{{
 CREATE OR REPLACE VIEW geometry_columns_lateral AS
 SELECT  current_database()::varchar(256) AS f_table_catalog,
   n.nspname::varchar(256) AS f_table_schema,
   c.relname::varchar(256) AS f_table_name,
   a.attname::varchar(256) AS f_geometry_column,
   COALESCE(postgis_typmod_dims(atttypmod),
            s.dims,
            2) AS coord_dimension,
  COALESCE(NULLIF(postgis_typmod_srid(atttypmod),0),
            s.srid,
            0) AS srid,
   -- force to be uppercase with no ZM so is backwards compatible
   -- with old geometry_columns
   replace(
     replace(
       COALESCE(
         NULLIF(upper(postgis_typmod_type(atttypmod)::text), 'GEOMETRY'),
        s.type,
         'GEOMETRY'
       ), 'ZM', ''
     ), 'Z', ''
   )::varchar(30) AS type
 FROM pg_class c
 JOIN pg_attribute a ON a.attrelid = c.oid AND NOT a.attisdropped
 JOIN pg_namespace n ON c.relnamespace = n.oid
 JOIN pg_type t ON a.atttypid = t.oid
 LEFT JOIN LATERAL
 (SELECT connamespace, conrelid, conkey
     , MAX( CASE WHEN consrc ILIKE '%geometrytype(% = %'  THEN
 replace(split_part(consrc, '''', 2), ')', '') ELSE NULL END ) As type
 , MAX( CASE WHEN consrc ILIKE '%srid(% = %' THEN
 replace(replace(split_part(consrc, ' = ', 2), ')', ''), '(', '')::integer
 ELSE NULL END) As srid
 , MAX( CASE WHEN consrc ILIKE '%ndims(% = %' THEN
 replace(split_part(consrc, ' = ', 2), ')', '')::integer ELSE NULL END) As
 dims
   FROM pg_constraint As s
 WHERE (consrc LIKE '%geometrytype(% = %' OR consrc LIKE '%ndims(% = %' OR
 consrc LIKE '%srid(% = %' )
 AND s.connamespace = n.oid
   AND s.conrelid = c.oid
   AND a.attnum = ANY (s.conkey)
  GROUP BY connamespace, conrelid, conkey
  ) AS s ON true
 WHERE c.relkind IN ('r'::"char", 'v'::"char", 'm'::"char", 'f'::"char")
 AND NOT ( c.relname = 'raster_columns' )
 AND t.typname = 'geometry'::name
 AND NOT pg_is_other_temp_schema(c.relnamespace)
 AND has_table_privilege( c.oid, 'SELECT'::text );
 }}}


 {{{
 -- 120 ms
 SELECT * FROM geometry_columns_lateral;

 -- 40 ms
 SELECT * FROM geometry_columns_lateral
 WHERE f_table_name LIKE 'constraint%point%';
 }}}

--
Ticket URL: <http://trac.osgeo.org/postgis/ticket/3092#comment:11>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.