[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,
 {{{
 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_Within(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=6230.89..475346571.95 rows=227104 width=271) (actual
 time=7009.454..15032.957 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=13.654..13.685
 rows=3 loops=1)
         Index Cond: ((tags -> 'ISO3166-1'::text) = 'KR'::text)
   ->  Bitmap Heap Scan on planet_osm_polygon s  (cost=6230.62..357938.45
 rows=171 width=239) (actual time=2291.334..4919.787 rows=459 loops=3)
         Recheck Cond: (ap.way ~ way)
         Filter: ((admin_level ~ '^\d+$'::text) AND _st_contains(ap.way,
 way))
         Rows Removed by Filter: 102799
         Heap Blocks: exact=14628
         ->  Bitmap Index Scan on planet_osm_polygon_index
 (cost=0.00..6230.57 rows=265635 width=0) (actual time=2186.556..2186.556
 rows=103258 loops=3)
               Index Cond: (ap.way ~ way)
 Planning time: 0.292 ms
 Execution time: 15054.474 ms
 }}}
 subsequent executions are in the 1800ms range due to caching.
 So in this case the speed is comparable to the query with the extra ~
 condition in it

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