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

Wed Apr 26 16:11:45 PDT 2017

#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 robe):

 Well bitmap/normal index is not an issue.  Usually in my case the bitmap
 and index scan speeds are pretty close and the bitmap often beats out the
 plain index scan.

 So the only concern I have here is your seq scan one.  Sadly I can't
 replicate with a smaller test sample even on my windows 64-bit 9.6.2
 running PostGIS 2.4 dev (which should be more or less the same as 2.3.2)
 and same makeup as yours.

 Here is my vein attempt just using full NJ state data I had lying around
 and using the same indexes you have except the iso had to replace with iso
 2 since I didn't have iso 1.

 {{{
 EXPLAIN  ANALYZE SELECT  ap.tags->'ISO3166-2' 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-2' AND ap.tags->'ISO3166-2' in ('US-NJ')  AND
 s.admin_level ~ '^\d+$';

 QUERY PLAN
 -----------------------------------------------------------------------------------------------
  Nested Loop  (cost=4.30..28.74 rows=1 width=7236) (actual
 time=2.629..389.108 rows=488 loops=1)
    ->  Index Scan using polygon_iso3166_2_idx on planet_osm_polygon ap
 (cost=0.13..8.14 rows=1 width=7231) (actual time=0.015..0.016 rows=1
 loops=1)
          Index Cond: ((tags -> 'ISO3166-2'::text) = 'US-NJ'::text)
    ->  Bitmap Heap Scan on planet_osm_polygon s  (cost=4.18..20.58 rows=1
 width=7204) (actual time=2.283..386.256 rows=488 loops=1)
          Recheck Cond: (ap.way ~ way)
          Filter: (((admin_level)::text ~ '^\d+$'::text) AND
 _st_contains(ap.way, way))
          Rows Removed by Filter: 3124
          Heap Blocks: exact=1348
          ->  Bitmap Index Scan on planet_osm_polygon_index
 (cost=0.00..4.18 rows=4 width=0) (actual time=0.594..0.594 rows=3612
 loops=1)
                Index Cond: (ap.way ~ way)
  Planning time: 4.018 ms
  Execution time: 392.913 ms
 (12 rows)
 }}}

 {{{
 EXPLAIN  ANALYZE SELECT  ap.tags->'ISO3166-2' 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-2' AND ap.tags->'ISO3166-2' in ('US-NJ')  AND
 s.admin_level ~ '^\d+$';

 QUERY PLAN
 ------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=0.27..16.58 rows=1 width=7236) (actual
 time=1.707..398.057 rows=488 loops=1)
    ->  Index Scan using polygon_iso3166_2_idx on planet_osm_polygon ap
 (cost=0.13..8.14 rows=1 width=7231) (actual time=0.016..0.017 rows=1
 loops=1)
          Index Cond: ((tags -> 'ISO3166-2'::text) = 'US-NJ'::text)
    ->  Index Scan using planet_osm_polygon_index on planet_osm_polygon s
 (cost=0.15..8.42 rows=1 width=7204) (actual time=1.480..395.016 rows=488
 loops=1)
          Index Cond: ((ap.way ~ way) AND (ap.way ~ way))
          Filter: (((admin_level)::text ~ '^\d+$'::text) AND
 _st_contains(ap.way, way))
          Rows Removed by Filter: 3124
  Planning time: 0.156 ms
  Execution time: 398.725 ms
 (9 rows)

 }}}

 {{{

 SET enable_bitmapscan = false;
 EXPLAIN  ANALYZE SELECT  ap.tags->'ISO3166-2' 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-2' AND ap.tags->'ISO3166-2' in ('US-NJ')  AND
 s.admin_level ~ '^\d+$';

 QUERY PLAN
 -------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=0.27..29.38 rows=1 width=7236) (actual
 time=1.607..402.173 rows=488 loops=1)
    ->  Index Scan using polygon_iso3166_2_idx on planet_osm_polygon ap
 (cost=0.13..8.14 rows=1 width=7231) (actual time=0.007..0.008 rows=1
 loops=1)
          Index Cond: ((tags -> 'ISO3166-2'::text) = 'US-NJ'::text)
    ->  Index Scan using planet_osm_polygon_index on planet_osm_polygon s
 (cost=0.15..21.23 rows=1 width=7204) (actual time=1.545..397.700 rows=488
 loops=1)
          Index Cond: (ap.way ~ way)
          Filter: (((admin_level)::text ~ '^\d+$'::text) AND
 _st_contains(ap.way, way))
          Rows Removed by Filter: 3124
  Planning time: 0.159 ms
  Execution time: 402.805 ms
 (9 rows)
 }}}

 See in above the execution time the bitmap is fastest and even when you
 add in planning it's still faster.
 You can fiddle with maybe enable_seqscan=false to see what it does if it
 makes it go the right way.

 It would be great if you can maybe try the above example too on your
 machine - to rule out machine/PostgreSQL version/ postgrsql settings
 specific issues.

 The data I used was - http://postgis.us/downloads/nj.osm.pbf

 If your bounding boxes are big enough (which is a especially big problem
 when using long lat projection and comparing countries), it could dismiss
 using an index entirely because it thinks its useless, never even bothers
 costing.  The extra ~ would just add some extra
 incentive to make it consider the plan.  pramsey can describe the innards
 better but I do think the histogram is used in some shape or form for the
 planner to decide if a plan is even worth considering.

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