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

 Here are the results:

 Fast one:
 {{{
 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 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+$'
 }}}

 {{{
 Nested Loop  (cost=0.83..486830.14 rows=227 width=271) (actual
 time=1319.929..92007.078 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.067..0.076 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=443.719..30636.884
 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.340 ms
 Execution time: 92018.858 ms
 }}}

 Slow one:
 {{{
 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+$'
 }}}

 {{{
 Nested Loop  (cost=0.28..193474644.34 rows=227104 width=271) (actual
 time=368744.872..4795335.403 rows=1377 loops=1)
   Join Filter: ((ap.way ~ s.way) AND _st_contains(ap.way, s.way))
   Rows Removed by Join Filter: 1541343
   ->  Seq Scan on planet_osm_polygon s  (cost=0.00..12924619.20
 rows=513037 width=239) (actual time=26.551..4588533.670 rows=514240
 loops=1)
         Filter: (admin_level ~ '^\d+$'::text)
         Rows Removed by Filter: 265120823
   ->  Materialize  (cost=0.28..1479.66 rows=1328 width=258) (actual
 time=0.001..0.005 rows=3 loops=514240)
         ->  Index Scan using polygon_iso3166_1_idx on planet_osm_polygon
 ap  (cost=0.28..1473.02 rows=1328 width=258) (actual time=32.366..37.253
 rows=3 loops=1)
               Index Cond: ((tags -> 'ISO3166-1'::text) = 'KR'::text)
 Planning time: 107.125 ms
 Execution time: 4795338.151 ms
 }}}
 indices on planet_osm_polygon:
 {{{
 polygon_iso3166_1_idx : CREATE INDEX polygon_iso3166_1_idx ON
 planet_osm_polygon USING btree (((tags -> 'ISO3166-1'::text))) WHERE (tags
 ? 'ISO3166-1'::text)
 planet_osm_polygon_pkey : CREATE INDEX planet_osm_polygon_pkey ON
 planet_osm_polygon USING btree (osm_id)
 planet_osm_polygon_index : CREATE INDEX planet_osm_polygon_index ON
 planet_osm_polygon USING gist (way)
 }}}

 So I used a partial index, because the ISO3166-1 tag is the only one we
 use in our queries, and a full gist/gin index would be wasting too much
 diskspace.
 And as for sample data, that's going to take a while, if at all possible.

 But if you're able to get the bitmap/normal index difference at your end,
 wouldn't that be sufficient to get to the cause of the issue?

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