[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:  closed
  Priority:  medium     |  Milestone:  PostGIS 2.2.0
 Component:  postgis    |    Version:  2.0.x
Resolution:  wontfix    |   Keywords:
------------------------+---------------------------
Changes (by robe):

 * milestone:  PostGIS 2.0.4 => PostGIS 2.2.0


Comment:

 I'm reopening this one.  Looking at the find_srid function, I think this
 may be somewhat improved by the commit discussed in #3092, but to improve
 even more, we really got to get rid of the casting I noted in the TODO of
 the view.

 Unfortunately taking out the cast, would require dropping and recreating a
 view and since the output types would become of type: name instead of
 varchar, it would be considered an API change, so would have to go in 2.2.

 I'm guessing though that doing that would increase performance about 3
 fold at least since find_srid just does a table/column lookup like this:

 Aha see pramsey - column lookup
 {{{
 select SRID into sr from geometry_columns where f_table_schema like schem
 and f_table_name = tabl and f_geometry_column = $3;
 }}}


 Anyway find_srid probably needs a bit of cleanup too.

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