PostGIS 3.5.2 ST_Contains() query on gist is slow

[Paul Ramsey]

> On Sep 9, 2025, at 11:29 AM, Sebastiaan Couwenberg via postgis-users <postgis-users at lists.osgeo.org> wrote:
> 
> On 9/9/25 7:25 PM, Paul Ramsey wrote:
>> On Mon, Sep 8, 2025 at 8:34 PM Sebastiaan Couwenberg via postgis-users <
>> postgis-users at lists.osgeo.org> wrote:
>>> may be a regression in PostGIS 3.5.2, but I'm at a loss on how to confirm
>>> and fix that.
>>> 
>>> 
>> So, this seems to be a change in planning behaviour primarily, and you have
>> a lot of variability in your upgrade, any piece of which could be
>> implicated:
>> - Have you run ANALYZE on the relevant tables?
> 
> Yes, that has no effect on performance.
> 
>> - You have a PostgreSQL upgrade from 15 to 17, can you hold your
>> PostGIS version constant while testing PostgreSQL version changes
>> - You have a PostGIS upgrade from 3.3 to 3.5, can you hold your PostgreSQL
>> version constant while testing PostGIS version changes
> 
> There is only a single version of postgresql & postgis in the Debian stable repos, pgdg packages are not used.
> 
>> - Can you check the PostGIS selectivity estimate on the old and the new
>> installations, using
>> SELECT  _postgis_selectivity ('nodes', 'geom',
>> ST_GeomFromEWKT('SRID=4326;POLYGON((6.11982 52.6121955,6.11982
>> 53.2038323,7.0927397 53.2038323,7.0927397 52.6121955,6.11982
>> 52.6121955))'), '2');
> 
> I cannot test this easily on the old installation, I'd need to load the data in bookworm chroot on that system which is very time consuming.
> 
> For the new installation:
> 
> osm-nl=# SELECT _postgis_selectivity ('nodes', 'geom', ST_GeomFromEWKT('SRID=4326;POLYGON((6.11982 52.6121955,6.11982 53.2038323,7.0927397 53.2038323,7.0927397 52.6121955,6.11982 52.6121955))'), '2');
> _postgis_selectivity
> ----------------------
>                    0
> (1 row)
> 
> Despite Daryl mentioning he didn't suspect his comment would be all that helpful, the CTE OFFSET 0 trick works well to get back to the old performance.

Effectively you’re just forcing the plan, doing the two PK filters before applying the spatial filter.
It’s a shame you cannot provide the selectivity on the other machine, because this way we are just guessing. The fact that selectivity is coming back at zero does maybe indicate a change in selectivity calculations, and zero is smaller than a lot of numbers, so perhaps that’s driving it, but that’s a big guess.

P

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