[postgis-users] Query choces on searching too small area

[Sandro Santilli]

On Fri, Dec 16, 2016 at 02:09:01PM +0100, Arjen Haayman wrote:

> What I really don't understand why it would fail when the query gets too specific, I mean searching on a point should be easier than searching a large bounding box. It usually is the other way around??

It looks like a bug in the estimator, which somehow thinks that a smaller
bounding box is less selective than a larger one. You can check postgis
guess on selectivity using an internal function:

  -- Availability: 2.1.0
  -- Given a table, column and query geometry, returns the estimate of what proportion
  -- of the table would be returned by a query using the &&/&&& operators. The mode
  -- changes whether the estimate is in x/y only or in all available dimensions.
  CREATE OR REPLACE FUNCTION _postgis_selectivity(tbl regclass, att_name text, geom geometry, mode text default '2')

NOTE: EXPLAIN ANALYZE also runs the query, so just use EXPLAIN to know more
      about how the plan changes based on changed parameters.

--strk;