Hi postgis-users,
Please may I have some guidance. I have been looking in to bad row count
estimates for a simple query, similar to the following:
SELECT area_id FROM area WHERE ST_INTERSECTS(area.polygon, ST_MakePoint(x,
y))
I have found that the row count estimate is always '1', even if the point
intersects multiple geometries and the actual row count is in the thousands. Is
this expected behaviour?
To troubleshoot, I have tried:
1. Manually running `VACUUM ANALYZE`
2. Ensuring that stats are returned by `__postgis_stats`
3. Trying different but similar functions such as `ST_Contains`
I have also found that the following selectivity function returns `0` for any
point geometry:
`__postgis_selectivity('area', 'polygon', ST_MakePoint(x, y))`
After reading through
https://www.crunchydata.com/blog/indexes-selectivity-and-statistics, I think a
selectivity of `0` for a point might be the intended behaviour, but I'm not
sure -- based on the blog post (and reading through the PostGIS source),
spatial selectivity is the % of geometries covered by the search geometry. When
the search geometry is a point (which has no dimensions), none of the geometry
will be covered, giving a selectivity of `0`.
This is where I've got to on the investigation so far. Could I have some
guidance where to look next please? In particular:
1. Is always returning a specificity of `0` for a point geometry expected
behaviour?
2. Is there a way to get more accurate row estimates for point-in-poly
lookups such as `ST_INTERSECTS(area.polygon, ST_MakePoint(x, y)`?
Many thanks in advance,
Gareth
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
