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

 I reformatted your ticket since it was hard to follow.

 Unfortunately I don't have OSM data to test with.  I did a sample of a
 similar query on one of my datasets and it behaved correctly with the gist
 index kicking in first.

 Can you try the following:

 1. Do you have the same issue with ST_Intersects?

 That would at least isolate the issue to ST_Within since in theory && and
 ~ should be more or less optimized the same and use the same geo stats


 2. Sometimes I have seen indexes get corrupt. Particularly gist indexes
 (not just geometry but I've had issues with gist trigram for example that
 I also use a lot).
 Queries that use them just go down to a crawl.  I forget what the plan
 showed of these, but when I see the behavior, I just reindex the whole
 table or just the offending index and have a weekly routine to do this
 since it happens quite frequently (like once a month or so it happens to
 us on tables that are under massive updates).

 (Could very well be a windows specific issue too).


 Try:

 {{{

 reindex index public.planet_osm_polygon_index;
 }}}

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