[postgis-users] Query choces on searching too small area
strk at kbt.io
Fri Dec 16 06:25:11 PST 2016
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.
More information about the postgis-users