[postgis-tickets] [PostGIS] #3103: geometry_columns, find_srid performance regress tests

[PostGIS]

#3103: geometry_columns, find_srid performance regress tests
----------------------+---------------------------
  Reporter:  robe     |      Owner:  robe
      Type:  task     |     Status:  new
  Priority:  medium   |  Milestone:  PostGIS 2.2.0
 Component:  postgis  |    Version:  2.1.x
Resolution:           |   Keywords:
----------------------+---------------------------

Comment (by robe):

 On closer inspection the find_srid that whole splitting exercise to
 account for if people throw the schema name as part of the table name is a
 real performance killer.

 If I change the function to this:


 {{{
 CREATE OR REPLACE FUNCTION find_srid(
    character varying, character varying, character varying)
   RETURNS integer AS
 $$
 BEGIN
         RETURN srid FROM geometry_columns
             WHERE (f_table_schema = $1 OR $1 = '')
             AND f_table_name = $2  AND f_geometry_column = $3 LIMIT 1;
 END;
 $$
   LANGUAGE plpgsql IMMUTABLE STRICT;
 }}}


 And have a table of 30,000 tests  I created with this:


 {{{

 SELECT f_table_schema::Text, f_table_name::text, f_geometry_column::text
 INTO tmp_geoms from geometry_columns ;

 -- finishes in 1861 ms
 SELECT find_srid(f_table_schema, f_table_name, f_geometry_column) FROM
 tmp_geoms;


 -- With the old find_srid that has all this error handling gook and split
 table name
 -- into schema table, I lost patience waiting

 }}}

 Why it's such a massive overhead on my 9.3 instance I'm not sure, but I
 also don't think all that splitting is worth the overhead of something
 people shouldn't be doing anyway.  It's pretty clear to me that table name
 should not include

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