PostGIS 3.5.2 ST_Contains() query on gist is slow

[Sebastiaan Couwenberg]

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.

Kind Regards,

Bas

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