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

 This one seems to be a winner.  I'm going to commit today unless there are
 objections.  Everything is done in the view (no extra functions), no use
 of new syntax like LATERAL so works for any version of PostgreSQL I can
 think of and it beats or matches all contenders on all tests.  It uses 3
 joins instead of 1 to constraint table.


 {{{
 -- STATS on my 30,000 geometry column set --

 -- -- 2080-2090 ms (30,000 records - same speed as lateral, a little
 faster than committed array solution which was -- 2520ms - 2830 ms for
 same test )
 SELECT *  FROM  geometry_columns;

 -- 380 ms - 5,000 rows (constraint columns beats all solutions including
 lateral one,
 current committed array is -- 1260 ms -- 5,000 rows)
 SELECT * FROM  geometry_columns WHERE type = 'POINT';

 -- 1410ms 20,000 rows (compared to array which is 1840ms, lateral 1400 ms)
 SELECT * from geometry_columns_robe4
 where type = 'LINESTRING';


 -- Exact table match test 2 rows 60 ms, compared to 510 ms array approach
 and 220 ms lateral.
 -- The time goes down to 20 ms if I remove the varchar(256) casting
 SELECT * from geometry_columns
 WHERE  f_table_name = 'constraint_test_linestring_1382';



 -- 1490ms- 1500 ms   - 20,000 rows (mix of constraint and  columns)
 SELECT * FROM  geometry_columns WHERE type = 'LINESTRING';

 -- 220 - 240 ms
 SELECT * from geometry_columns
 WHERE  f_table_name = 'constraint_test_linestring_1382';

 }}}







 -- view is below --
 {{{
 CREATE OR REPLACE VIEW geometry_columns AS
  SELECT current_database()::character varying(256) AS f_table_catalog,
 -- TODO consider getting rid of CASTING it slows things down by 2 to 6
 fold for table/column exact matches.
 --   Geography doesn't have casting by the way
     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(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 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 s.connamespace,
             s.conrelid,
             s.conkey, replace(split_part(s.consrc, ''''::text, 2),
 ')'::text, ''::text) As type
            FROM pg_constraint AS 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 pg_constraint AS 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 pg_constraint AS 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"])) 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);

 }}}

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