[postgis-tickets] [PostGIS] #3739: ST_Within Not using index

[PostGIS]

#3739: ST_Within Not using index
--------------------------+---------------------------
  Reporter:  postgispaul  |      Owner:  pramsey
      Type:  defect       |     Status:  new
  Priority:  medium       |  Milestone:  PostGIS 2.3.3
 Component:  postgis      |    Version:  2.3.x
Resolution:               |   Keywords:
--------------------------+---------------------------

Comment (by postgispaul):

 You're right. Adding the ~ to the st_within (so it's present twice) is
 enough to persuade postgres to use the index correctly.

 {{{
 CREATE OR REPLACE FUNCTION public.st_withintest(
     geom1 geometry,
     geom2 geometry)
   RETURNS boolean AS
 'SELECT $2 OPERATOR(public.~) $1 AND $2 OPERATOR(public.~) $1 AND
 public._ST_Contains($2,$1)'
   LANGUAGE sql IMMUTABLE
   COST 100;

 explain analyze
 select ap.tags->'ISO3166-1' iso,s.admin_level admlev,s.way, s.osm_id from
 planet_osm_polygon s
 join planet_osm_polygon ap on ST_Withintest(s.way,ap.way)
 where ap.tags?'ISO3166-1' AND ap.tags->'ISO3166-1' in ('KR') AND
 s.admin_level ~ '^\d+$' }}}
 results in
 {{{
 Nested Loop  (cost=0.83..486830.14 rows=227 width=271) (actual
 time=15.872..1163.043 rows=1377 loops=1)
   ->  Index Scan using polygon_iso3166_1_idx on planet_osm_polygon ap
 (cost=0.28..1473.02 rows=1328 width=258) (actual time=0.146..0.179 rows=3
 loops=1)
         Index Cond: ((tags -> 'ISO3166-1'::text) = 'KR'::text)
   ->  Index Scan using planet_osm_polygon_index on planet_osm_polygon s
 (cost=0.55..365.47 rows=1 width=239) (actual time=3.473..363.983 rows=459
 loops=3)
         Index Cond: ((ap.way ~ way) AND (ap.way ~ way))
         Filter: ((admin_level ~ '^\d+$'::text) AND _st_contains(ap.way,
 way))
         Rows Removed by Filter: 102799
 Planning time: 0.352 ms
 Execution time: 1172.677 ms
 }}}

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3739#comment:27>
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.