[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:  robe
      Type:  enhancement  |     Status:  new
  Priority:  high         |  Milestone:  PostGIS 2.1.8
 Component:  postgis      |    Version:  2.0.x
Resolution:               |   Keywords:
--------------------------+---------------------------

Comment (by robe):

 Backported to 2.1 branch at r13446.  I did a couple of quick tests thru
 9.2.  I'll let the bots check 9.1 and below and we probably should put
 some timings in.

 There was a slight performance hit with SELECT count(*) in all platforms I
 tested.  But that performance hit is considerably low compared to the
 large performance gains in other areas.  So here is what I have:


 {{{
 -- generate 30,000 geometry columns
 -- ALTER SYSTEM SET max_locks_per_transaction = '100000'; -- pre 9.4 need
 to edit postgres.conf, after change need to restart service.
 DROP SCHEMA IF EXISTS test CASCADE;
 CREATE SCHEMA test; --250620 ms need to increase max
 -- need to increase max_locks_per_transaction;

 DO language plpgsql
 $$
 DECLARE var_sql text;
 BEGIN
   SELECT string_agg('CREATE TABLE test.constraint_test_' || lower(f.geom)
 || '_' || i::text || '(gid serial, geom2 geometry(LINESTRING,2249) );
   SELECT addgeometrycolumn(''test'', '
   || quote_literal('constraint_test_' || lower(f.geom) || '_' || i::text)
 || ' , ''geom'',
         ' || f.srid::text || ',' || quote_literal(f.geom) || ', 2,
 false);', '') INTO var_sql
   FROM (VALUES ('POINT'::text, 4326), ('MULTIPOLYGON'::text, 4269),
 ('LINESTRING'::text, 4326)) f(geom,srid)
                 , generate_series(1,5000) As i;
   EXECUTE var_sql;
 END;
 $$

 }}}


 The test results on 30,000 geometry columns database, old compared to new
 3 join approach.  I also marked those helper functions used in the old
 view as deprecated in PostGIS 2.2 (for future removal). Leaving in just in
 case some 3rd party tools are using them.


 {{{
 -- 30000, 9.5 old 340 ms, new 740ms
 -- 9.3 old 270 ms, new 480ms
 -- 9.2 old 330 ms, new 450 ms
 SELECT count(*) from geometry_columns;

 -- 30000 records,  9.5alpha  old 222,260 ms, new 2030 ms
 -- 9.3 old 349,659ms, new 2080 ms
 -- 9.2.10 old > 730,000 ms (stopped the process), new 2320 ms
 SELECT * FROM geometry_columns;

 -- 5000 records, 9.5 old  147,280 ms, new 560 ms
 -- 9.3 old 127,900 ms, new 380 ms
 -- 9.2 old - didn't bother, new 570 ms
 SELECT * FROM geometry_columns WHERE type = 'POINT';

 -- 20000 records, old 9.5  148,270 ms, new 1370 ms
 -- 9.3 old 258,801 ms, new 1410 ms
 -- 9.2.10 old didn't bother, new 1630 ms
 SELECT * FROM geometry_columns WHERE type = 'LINESTRING';

 -- 2 records, 9.5 old   210ms, new 9.5 70-160 ms
 -- 9.3 old 220ms, new 170-180 ms
 -- 9.2.10 new 200-210ms
 SELECT * FROM geometry_columns
 WHERE f_table_name = 'constraint_test_linestring_1382';

 }}}

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