[postgis-devel] [PostGIS] #536: Get rid of the STRICT on geography ST_Intersects

[PostGIS]

#536: Get rid of the STRICT on geography ST_Intersects
----------------------+-----------------------------------------------------
  Reporter:  robe     |       Owner:  pramsey      
      Type:  defect   |      Status:  new          
  Priority:  high     |   Milestone:  PostGIS 1.5.2
 Component:  postgis  |     Version:  1.5.X        
Resolution:           |    Keywords:               
----------------------+-----------------------------------------------------
Comment (by robe):

 Mark,

 Here is your test


 {{{
 CREATE TABLE geogtest(gid SERIAL primary key, geog
 geography(POLYGON,4326));
 CREATE INDEX idx_geogtest_geog
   ON geogtest
   USING gist
   (geog);

 INSERT INTO geogtest(geog)
 SELECT ST_Buffer(geog,random()*10) As geog
 FROM (SELECT ST_GeogFromText('POINT(' || i*0.5 || ' ' || j*0.5 || ')') As
 geog
         FROM generate_series(-350,350) As i
                 CROSS JOIN generate_series(-175,175) As j
                 ) As foo
 LIMIT 1000;

 vacuum analyze geogtest;

 SELECT f.gid as gid1, count(f2.gid) As tot
 FROM geogtest As f INNER JOIN geogtest As f2
         ON ST_Intersects(f.geog, f2.geog)
 GROUP BY f.gid;

 }}}

 With STRICT in there -- this query is unbearably slow. So slow I can't
 wait for it to finish.

 Take strict out and it completes in 78 ms.

 This is running on 8.4, PostGIS 1.5.1

 I think what the STRICT is doing, even though not documented I think that
 its killing the inlining behavior of the SQL function that we've been
 relying on to break out the index piece from rest of the function.  Its a
 non-issue for plpgsql functions since they are treated as blackboxes
 anyway, but a killer for SQL functions where we are counting on the
 planner to see thru it to see there is a piece that can benefit from
 index.

 I suppose we could say this could be a bug in PostgreSQL and maybe worth
 noting.

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