[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:  defect   |      Status:  new          
 Priority:  medium   |   Milestone:  PostGIS 2.1.7
Component:  postgis  |     Version:  2.0.x        
 Keywords:           |  
---------------------+------------------------------------------------------
 Original discussion in this thread http://lists.osgeo.org/pipermail/gdal-
 dev/2015-March/041299.html

 Querying geometry_columns exhibits poor performance (perhaps quadratic in
 number of spatial tables) due to geometry_columns potentially calling
 postgis_constraint_dims(), postgis_constraint_srid()
 and postgis_constraint_type(), because each causes a SELECT to be executed
 for each row if some typmod constraints are missing.

 The following request gives similar information but much faster:
 {{{
 select c.relname,
        a.attname,
        t.typname,
        postgis_typmod_dims(a.atttypmod) dim,
        postgis_typmod_srid(a.atttypmod) srid,
        postgis_typmod_type(a.atttypmod)::text typ,
        array_agg(s.consrc)::text
 FROM pg_class c join pg_attribute a on a.attrelid=c.oid
                 join pg_namespace n on c.relnamespace = n.oid
                 AND (c.relkind = 'r'::"char" OR c.relkind = 'v'::"char" OR
 c.relkind = 'm'::"char" OR c.relkind = 'f'::"char")
                 AND NOT ( n.nspname = 'public' AND c.relname =
 'raster_columns' )
                 join pg_type t on a.atttypid = t.oid and (t.typname =
 'geometry'::name OR t.typname = 'geography'::name)
                 left join pg_constraint s on s.connamespace = n.oid AND
 s.conrelid = c.oid
                           AND a.attnum = ANY (s.conkey)
                           AND (s.consrc LIKE '%geometrytype(% = %' OR
 s.consrc LIKE '%ndims(% = %' OR s.consrc LIKE '%srid(% = %')
 GROUP BY c.relname, a.attname, t.typname, dim, srid, typ, c.oid
 ORDER BY c.oid
 }}}

 Some analysis of the aggregated constraints similar to what is done in
 postgis_constraint_XXXX() should be done to merge that info with the one
 of postgis_typmod_XXXX()

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