[postgis-users] Points and Row Count Estimates

[Gareth Bell]

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