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

 Yes it would help to have the explain analyze results to see what the
 actual number of rows it is comparing vs what it's actually getting and
 estimated vs. actual costs.

 How long do each of these queries take? The slower vs. faster.

 I'd also like to see the indexes you have in place, clearly the ones you
 have listed above is not all of them. For example it seems odd to me that
 your index cond is (ap.tags ->'....' ..) unless you have a functional
 index on that. I would have also expected a gist or gin index on tags so
 you could write the query like



 {{{
 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=>KR'::hstore  AND s.admin_level ~ '^\d+$'
 }}}

 If you can provide some sample data that exhibits the issue that would be
 really great.  I know it might be hard that it might require your full
 dataset so do your best.

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