[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):

 A tincy better but still pretty bad.  this time I did on only 20 records
 and had to wrap in a subselect otherwise seems to try to apply to whole
 table

 Here are benchmarks I have -- the tmpgeoms is just a raw table bulk insert
 from my 30,000 geometry_columns table


 {{{

 -- this is existing function
 -- 5530 ms
 SELECT find_srid('', f_table_name, f_geometry_column) FROM (SELECT * FROM
 tmp_geoms  ORDER BY f_table_name, f_geometry_column LIMIT 20) As f;

 -- 130 ms
 SELECT find_srid(f_table_schema, f_table_name, f_geometry_column) FROM
 (SELECT * FROM tmp_geoms  ORDER BY f_table_name, f_geometry_column LIMIT
 20) As f;

 ----

 -- this is the one using strpos for check instead of LIKE -- 5530 ms
 SELECT find_srid_new2('', f_table_name, f_geometry_column) FROM (SELECT *
 FROM tmp_geoms  ORDER BY f_table_name, f_geometry_column LIMIT 20) As f;

 -- 130 ms
 SELECT find_srid_new2(f_table_schema, f_table_name, f_geometry_column)
 FROM (SELECT * FROM tmp_geoms  ORDER BY f_table_name, f_geometry_column
 LIMIT 20) As f;


 ----

 -- Do not bother doing split check at all and go straight to query
 -- 30ms
 SELECT find_srid_new('', f_table_name, f_geometry_column) FROM (SELECT *
 FROM tmp_geoms  ORDER BY f_table_name, f_geometry_column LIMIT 20) As f;

 -- 20 ms
 SELECT find_srid_new(f_table_schema, f_table_name, f_geometry_column) FROM
 (SELECT * FROM tmp_geoms  ORDER BY f_table_name, f_geometry_column LIMIT
 20) As f;

 }}}

 Yah seems pretty shucking.  Makes me think something might be wrong with
 my install or a bug in postgres.  I ran on PostgreSQL 9.4.1, compiled by
 Visual C++ build 1800, 64-bit.  But had similar experience with 9.3.  This
 is using my new view (with casts removed.  As I recall though the old view
 was just as bad.

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