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

 Ok, just found some time on our server to dive into this.
 The thing is: I've simplified my example to the most simple one where the
 st_within isn't behaving as it should.

 The initial query where I'm working with is even worse:
 {{{
 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_Within(s.way,ap.way)
 where ap.tags?'ISO3166-1' AND ap.tags->'ISO3166-1' in ('KR') AND
 s.admin_level ~ '^\d+$'
 }}}

 gives me:
 {{{
 Nested Loop  (cost=0.28..193474644.34 rows=227104 width=271)
   Output: (ap.tags -> 'ISO3166-1'::text), s.admin_level, s.way, s.osm_id
   Join Filter: ((ap.way ~ s.way) AND _st_contains(ap.way, s.way))
   ->  Seq Scan on public.planet_osm_polygon s  (cost=0.00..12924619.20
 rows=513037 width=239)
         Output: s.osm_id, s.highway, s.surface, s.railway, s.waterway,
 s.tunnel, s.bridge, s.oneway, s.access, s.name, s.z_order, s.way_area,
 s.area, s.natural, s.water, s.admin_level, s.boundary, s.landuse,
 s.amenity, s.parking, s.aeroway, s.military, s (...)
         Filter: (s.admin_level ~ '^\d+$'::text)
   ->  Materialize  (cost=0.28..1479.66 rows=1328 width=258)
         Output: ap.tags, ap.way
         ->  Index Scan using polygon_iso3166_1_idx on
 public.planet_osm_polygon ap  (cost=0.28..1473.02 rows=1328 width=258)
               Output: ap.tags, ap.way
               Index Cond: ((ap.tags -> 'ISO3166-1'::text) = 'KR'::text)
 }}}

 while
 {{{
 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 ap.way ~ s.way and ST_Within(s.way,ap.way)
 where ap.tags?'ISO3166-1' AND ap.tags->'ISO3166-1' in ('KR') AND
 s.admin_level ~ '^\d+$'
 }}}
 gives me
 {{{
 Nested Loop  (cost=0.83..486830.14 rows=227 width=271)
   Output: (ap.tags -> 'ISO3166-1'::text), s.admin_level, s.way, s.osm_id
   ->  Index Scan using polygon_iso3166_1_idx on public.planet_osm_polygon
 ap  (cost=0.28..1473.02 rows=1328 width=258)
         Output: ap.osm_id, ap.highway, ap.surface, ap.railway,
 ap.waterway, ap.tunnel, ap.bridge, ap.oneway, ap.access, ap.name,
 ap.z_order, ap.way_area, ap.area, ap.natural, ap.water, ap.admin_level,
 ap.boundary, ap.landuse, ap.amenity, ap.parking, ap.a (...)
         Index Cond: ((ap.tags -> 'ISO3166-1'::text) = 'KR'::text)
   ->  Index Scan using planet_osm_polygon_index on
 public.planet_osm_polygon s  (cost=0.55..365.47 rows=1 width=239)
         Output: s.osm_id, s.highway, s.surface, s.railway, s.waterway,
 s.tunnel, s.bridge, s.oneway, s.access, s.name, s.z_order, s.way_area,
 s.area, s.natural, s.water, s.admin_level, s.boundary, s.landuse,
 s.amenity, s.parking, s.aeroway, s.military, s (...)
         Index Cond: ((ap.way ~ s.way) AND (ap.way ~ s.way))
         Filter: ((s.admin_level ~ '^\d+$'::text) AND _st_contains(ap.way,
 s.way))
 }}}

 So without the extra condition, it's using a sequential scan, and that one
 is definitely slower than an index scan.

 Do you still need those explain analyze results?

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