PostGIS 3.5.2 ST_Contains() query on gist is slow

[Paul Ramsey]

> On Sep 10, 2025, at 9:26 AM, Sebastiaan Couwenberg via postgis-users <postgis-users at lists.osgeo.org> wrote:
> 
> On 9/10/25 6:05 PM, Paul Ramsey wrote:
>> Because the core of your query is “find this small handful of records that meet the unique key constraints, and then filter them with ST_Contains”, and that query cannot make use of the spatial index, so for that query the index is superfluous. You might have other queries that *do* need the index, but the one you’re getting the bad plan on does not.
> 
> Dropping the index is not what you want, because you still want it for other queries. You do want a way to not use the index for this query where it's detrimental.
> 
> Isn't the OFFSET 0 trick a way to manipulate the query planner to avoid using the detrimental index on its first try?

Forcing the planner via a CTE w/ OFFSET 0 gets you (personally) through the day, but really we need to look at your data and query and figure out what the “correct” selectivity answer is and then figure why the estimate is off. Because while 7% is a small selectivity, it’s not nearly as small as 0% and that mistake is bunging your plan.

P.

> 
> Is there a better way? I read your post [0] about the clever postgres query planning, which suggests that ANALYZE should suffice.
> 
> [0] https://www.crunchydata.com/blog/indexes-selectivity-and-statistics
> 
> Kind Regards,
> 
> Bas
> 
> -- 
> GPG Key ID: 4096R/6750F10AE88D4AF1
> Fingerprint: 8182 DE41 7056 408D 6146  50D1 6750 F10A E88D 4AF1