PostGIS 3.5.2 ST_Contains() query on gist is slow

[Sebastiaan Couwenberg]

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?

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