On 9/9/25 7:25 PM, Paul Ramsey wrote:
On Mon, Sep 8, 2025 at 8:34 PM Sebastiaan Couwenberg via postgis-users <
[email protected]> 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
