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

Tue Feb 6 11:42:02 PST 2024

Hi,

I made a test with a PostGIS on my own laptop. My versions are:
"POSTGIS=""3.4.1 3.4.1"" [EXTENSION] PGSQL=""160"" GEOS=""3.12.1-CAPI-1.18.1"" SFCGAL=""SFCGAL 1.5.0, CGAL 5.6, BOOST 1.78.0"" PROJ=""8.2.1

The polygon layer has 215000 lake polygons. My BBOX select 73 polygons. It takes 66 milliseconds.
My query and the execution plan are as follows:

select * from jarvinemo
where "geom" &&
ST_GeomFromText(
'POLYGON (( 314728.6874003611 6936494.124854623, 314728.6874003611 6946067.332484153, 327290.0638853506 6946067.332484153, 327290.0638853506 6936494.124854623, 314728.6874003611 6936494.124854623 ))');

"Bitmap Heap Scan on jarvinemo  (cost=4.58..155.40 rows=39 width=588) (actual time=0.048..0.076 rows=73 loops=1)"
"  Recheck Cond: (geom && '0103000000010000000500000059E1E5BFA23513413F9EFD87EB755A4159E1E5BFA23513419D6B47D5447F5A414E296B41E8F913419D6B47D5447F5A414E296B41E8F913413F9EFD87EB755A4159E1E5BFA23513413F9EFD87EB755A41'::geometry)"
"  Heap Blocks: exact=64"
"  ->  Bitmap Index Scan on jarvinemo_geom_geom_idx  (cost=0.00..4.58 rows=39 width=0) (actual time=0.039..0.039 rows=73 loops=1)"
"        Index Cond: (geom && '0103000000010000000500000059E1E5BFA23513413F9EFD87EB755A4159E1E5BFA23513419D6B47D5447F5A414E296B41E8F913419D6B47D5447F5A414E296B41E8F913413F9EFD87EB755A4159E1E5BFA23513413F9EFD87EB755A41'::geometry)"
"Planning Time: 0.142 ms"
"Execution Time: 0.106 ms"

I can see that the bbox (POLYGON) in your query is big, something like 1500-2500 kilometres wide, and covers almost the whole EXTENT of your data. That means that the spatial filter is ineffective because it does not filter out anything. Have a try with a small polygon as a filter and compare the execution plan with the one that my database makes. That helps you to find out if the spatial index kicks in at some time. Of course, that does not help when the map covers the whole area. Then all the data must be read. But spending 5 seconds (the old db) or 15 seconds (the new db) for that as you have written before feels very slow. I wonder if the slowness comes from the connection to the database rather than from the database itself. It is some hosted database, cartodb perhaps by some logs that you have showed. Have you made any tests with a local db?

-Jukka Rahkonen-

Lähettäjä: MapServer-users <mapserver-users-bounces at lists.osgeo.org> Puolesta Rob Dennett via MapServer-users
Lähetetty: tiistai 6. helmikuuta 2024 21.02
Vastaanottaja: mapserver-users at lists.osgeo.org
Aihe: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?

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<mailto:traviskirstine at gmail.com>>
Sent: Monday, February 5, 2024 10:50 AM
To: Rob Dennett <Rob.Dennett at twdb.texas.gov<mailto:Rob.Dennett at twdb.texas.gov>>
Cc: mapserver-users at lists.osgeo.org<mailto:mapserver-users at lists.osgeo.org> <mapserver-users at lists.osgeo.org<mailto: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/83fc79e7/attachment-0001.htm>