[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:  new          
 Priority:  low        |   Milestone:  PostGIS 2.1.0
Component:  postgis    |     Version:  2.0.x        
 Keywords:             |  
-----------------------+----------------------------------------------------
 The speed of find_srid has dropped significantly in PostGIS >= 2.0 when a
 large number of tables exist in the database. It looks like this is
 related to the new definition of geometry_columns as a view.

 On my system, for a database containing a hundred tables find_srid() takes
 ~ 10ms, with five thousand tables ~ 100ms, with fifteen thousand tables ~
 450ms.

 I'm not sure if this is a bug or a regression you'd consider worth fixing,
 however mapserver's postgis vector data source will put a find_srid() call
 in every query if the user doesn't specify "using srid=<srid>". With
 PostGIS>=2.0 on a database with lots of tables, not specifying the srid is
 suddenly a massive (rather than very minor) performance issue.

 I've attached a rather ugly shell script that reproduces the issue :-)
 Thanks!

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