[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):

 Okay here is my proposed.  No dependencies on any functions and for my
 simple tests the speed seems much better than existing and slower than
 yours for full table set but generally faster for filtered set of tables.

 {{{

 CREATE OR REPLACE VIEW geometry_columns 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
 (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
 WHERE consrc ILIKE '%geometrytype(% = %' OR consrc ILIKE '%ndims(% = %' OR
 consrc ILIKE '%srid(% = %'
  GROUP BY connamespace, conrelid, conkey
  ) AS s ON s.connamespace = n.oid
   AND s.conrelid = c.oid
   AND a.attnum = ANY (s.conkey)
 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 );
 }}}


 For full table scan your version beats by a wide margin.  For a 1500 table
 set yours comes out

 {{{
 -- Paul's 140 ms, Regina's 440ms, original 1860ms
 SELECT * from geometry_columns;
 }}}

 -- For filtered the story is a little different



 {{{
 -- Paul's 80ms, Regina's 40ms, original  640ms
 -- 500 rows returned
 SELECT * FROM geometry_columns
 WHERE f_table_name LIKE 'constraint%point%';
 }}}



 I still need to test it with multi geom column tables and typmod to make
 sure no consequences I missed and also a bigger set of tables.  I only
 tested with 1500 tables all of constraint type and no typmod so not a
 sufficiently complete test.

--
Ticket URL: <http://trac.osgeo.org/postgis/ticket/3092#comment:10>
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.