[MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?

[Rob Dennett]

So, I ran explain analyze, and got different results when run against the old and new db.  I ran VACUUM on the table (which ran in less than ½ a second) and checked the query plans again.  They're now the same except for the time estimates and against the new db they're still much slower.

We ran across a StackExchange article about performance degradation after upgrading to postgres 13 and postgis 3.4 (https://dba.stackexchange.com/questions/300292/why-is-my-spatial-query-slower-in-postgres-13-than-in-postgres-11)  and they mention a change in the way postgis works, but since these queries are generated by mapserver, I am not sure what I can do on my end.  Is there a version of MapServer that's recommended for Postgres 13/PostGIS 3?
[https://cdn.sstatic.net/Sites/dba/Img/apple-touch-icon@2.png?v=246e2cb2439c]<https://dba.stackexchange.com/questions/300292/why-is-my-spatial-query-slower-in-postgres-13-than-in-postgres-11>
Why is my spatial query slower in Postgres 13 than in Postgres 11?<https://dba.stackexchange.com/questions/300292/why-is-my-spatial-query-slower-in-postgres-13-than-in-postgres-11>
Postgres versions PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit PostgreSQL 11.11 (Debian 11.11-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled ...
dba.stackexchange.com


________________________________
From: Travis Kirstine <traviskirstine at gmail.com>
Sent: Monday, February 5, 2024 10:50 AM
To: Rob Dennett <Rob.Dennett at twdb.texas.gov>
Cc: mapserver-users at lists.osgeo.org <mapserver-users at lists.osgeo.org>
Subject: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?


External: Beware of links/attachments.


Not sure I understand the issue with the polygons.   The polygon value in the select statement will change based on the client's view extent, for example if the users moves the map a new request with a different polygon / bbox would be issued to mapserver and through to postgres. The EXTENT value defined in the mapfile defines the extent of the map or layer coverage.

I would try running EXPLAIN ANALYZE on the same query on both versions of Postgres and see what the differences are.

explain analyze select "sourceid"::text,"featuretyp"::text,"cartodb_id"::text,"name"::text,"sourcetype"::text,"drawingord"::text,"isnew"::text,ST_AsBinary(("geom"),'NDR') as geom,"cartodb_id"::text from the_table_in_question where "geom" && ST_GeomFromText('POLYGON((-12520996.7293382 2507134.52775378,-12520996.7293382 3754586.82936786,-11273544.4277241 3754586.82936786,-11273544.4277241 2507134.52775378,-12520996.7293382 2507134.52775378))',find_srid('','iswp_sourcefeatures2022','geom')) and ("featuretyp"::text = 'polygon')







-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20240206/e6d45c1d/attachment.htm>