[postgis-tickets] [PostGIS] #2365: find_srid performance regression with large numbers of tables

[PostGIS]

#2365: find_srid performance regression with large numbers of tables
------------------------+---------------------------
  Reporter:  angrygoat  |      Owner:  pramsey
      Type:  defect     |     Status:  reopened
  Priority:  medium     |  Milestone:  PostGIS 2.2.0
 Component:  postgis    |    Version:  2.0.x
Resolution:             |   Keywords:
------------------------+---------------------------

Comment (by robe):

 See #3103 also sorry should have put comment here.

 I'd really like to simplify logic to :


 {{{
 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;
 }}}

 But it's a breaking change since it would mean find_srid would no longer
 support

 constructs like below and I'm getting rid of error handling raise notice
 warnings.


 {{{
 SELECT find_srid('','myschema.mytable', 'geom');
 }}}

 But that extra logic is massively killing performance on both the 9.3.6
 and 9.4.1 instance I have and not sure if any third party tool even relies
 on that logic.

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