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

[PostGIS]

#3739: ST_Within Not using index
--------------------------+---------------------------
  Reporter:  postgispaul  |      Owner:  pramsey
      Type:  defect       |     Status:  closed
  Priority:  medium       |  Milestone:  PostGIS 2.3.3
 Component:  postgis      |    Version:  2.3.x
Resolution:  wontfix      |   Keywords:
--------------------------+---------------------------

Comment (by komzpa):

 I've met the same thing and digged into estimators to understand what's
 happening.

 ANALYSE builds inexact histogram of data, with up to ~10000 cells. On the
 scale of the world, it means they are ~300x300 km cells, with all the
 cities falling into same cell.

 When you join it, it multiplies a cell from table A by cell from table B
 to get upper limit. Thus inside a city you get a squared number of
 objects. When you're using unfiltered planet on both sides (with different
 conditions) it leads to catastrophic overestimate of row count.

 PostGIS estimator is unsuitable for planet-sized datasets unfortunately.

 Workarounds I've used:
  - kill postgis statistics using CLUSTER. Vacuum will come anyway and re-
 collect it, so a short-term thing to use in scripts;
  - bump work_mem to tremendously large values (20GB on my machine), so it
 thinks the intermediate result will fit into it anyway;
  - repeat the operator more times. Selectivity is still < 100%, each
 repeat will multiply it by itself, getting it lower and lower.

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