Re: PostGIS 3.5.2 ST_Contains() query on gist is slow

[Paul Ramsey via postgis-users]

> On Sep 10, 2025, at 9:26 AM, Sebastiaan Couwenberg via postgis-users 
> <postgis-users@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