PostGIS 3.5.2 ST_Contains() query on gist is slow

[Sebastiaan Couwenberg]

On 9/10/25 11:36 PM, Regina Obe wrote:
> Never mind the test above I proposed, I tried it and it still decided to use the spatial index instead of the primary key on n.id
> Which I found really surprising as I thought primary keys trump everything.
> 
> Anyway  Paul traced the commit that caused the issue and it was introduced in 3.4.

Which commit is that?

> I just tested on your data and seemed to do the trick reverting containment to old behavior
> 
> ALTER OPERATOR @ (geometry , geometry)
>      SET ( RESTRICT = contsel,
>      JOIN = contjoinsel )
> ;
> 
> But other operators were changed as well so not a complete revert and we'll see if we can come up with a better solution.
> 
> I have the issue ticketed here:
> 
> https://trac.osgeo.org/postgis/ticket/5984

Thanks you both for your help and testing so far.

You may be able to reproduce the issue with the andorra OSM extract which is much smaller than those for Netherlands or its provinces.

  https://download.geofabrik.de/europe/andorra-latest.osm.pbf

Although that might also be too small to be representative for this issue in a much larger dataset.

Kind Regards,

Bas

-- 
  GPG Key ID: 4096R/6750F10AE88D4AF1
Fingerprint: 8182 DE41 7056 408D 6146  50D1 6750 F10A E88D 4AF1