Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?
Something that's curious is that while the old db takes about 5 seconds to run the queries it generates, the actual web page loads in about 2 seconds. Another mystery to add to the pile. Thanks, Rob ___ MapServer-users mailing list MapServer-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?
Resending this since it was too large the first time Thanks to everyone for their help so far. There are only 3 things that changed when we upgraded. One, the Postgres version, two, the PostGIS version and three, we went from Aurora serverless v1 to serverless v2. MapServer itself runs on Amazon ECS which in this case has 8 Fargate instances all running an identical version of MapServer in a Docker container. These instances are load balanced. The table in question has 3479 rows. I am not clear how to determine how many features it has. Is it one per row? My URLs look like https://mapserver.mydomain.org/?map=/path/to/my/mapfile.map I actually made two copies of the original map file and changed the config for where the MS_ERRORFILE should be written and added DEBUG 2. One of those copies had the CONNECTION string set to point at the old db. The extent listed in the map section of the .map file is EXTENT -11871597.4858696 2978893.85839647 -10409341.4604357 4369793.96473184 It does seem to zoom in a little as it loads, but I am not sure where it's getting the polygon information in the queries it's generating. I hit the two URLs again, and this time got a debug file from the .mapfile pointed at the old db that is 226 lines long and one from the one pointed at the new db that is almost 24,000 lines long. The map file for the new db has thousands of lines like this (as did the .map file pointed at the old db the 1st time I ran it): [Tue Feb 6 20:37:34 2024].128829 msPostGISLayerNextShape called. [Tue Feb 6 20:37:34 2024].128833 msPostGISReadShape called. [Tue Feb 6 20:37:34 2024].128838 msPostGISLayerNextShape called. [Tue Feb 6 20:37:34 2024].128842 msPostGISReadShape called. [Tue Feb 6 20:37:34 2024].129474 msPostGISLayerNextShape called. [Tue Feb 6 20:37:34 2024].129480 msPostGISReadShape called. The queries are the same for the new db as the were the last time I loaded the page although they occur in a different order for each. However, the .map file pointed at the old db had different values for the polygons and all the queries returned 0 rows, even though the map rendered was fine. To be clear, running either set in pgAdmin is much slower against the new db compared to the old. Additionally, the set of old queries which all return 0 results took less than 1 sec on the old db and about 15 on the new. Additinonally, I tried adding an index in the new db, but while it seems slightly faster, it still takes twice as long as the old db does without a spatial index. Here's what I ran: CREATE INDEX idx_the_table_in_question_geom ON the_table_in_question USING gist (geom); Thanks, Rob ___ MapServer-users mailing list MapServer-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?
Hi Rob, Are you using the same MapServer machine to make the queries? If the MapServer version, and dependencies are all identical then the generated SQL queries should also be identical. You wrote about "hitting the web page" - but if the requested extents are different then it is likely the client requests are different. Are you using WMS services? What do these requests look like? Also you mention 4 different layers, so I'd try testing them one by one - are they all generating different SQL and response times? Seth -- web:https://geographika.net & https://mapserverstudio.net twitter: @geographika On Tue, Feb 6, 2024, at 8:53 PM, Rob Dennett via MapServer-users wrote: > Both DBs are hosted on AWS Aurora. The old one is serverless v1 and the new > one is serverless v2. We were thinking that perhaps the geom column for my > table needed a spatial index even though the old db doesn't have one. Is > that possible? Also, would it make sense that the same query run on both > should take the same amount of time (roughly)? > > Thanks, > Rob > > > > > ___ > MapServer-users mailing list > MapServer-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/mapserver-users > ___ MapServer-users mailing list MapServer-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?
Hi, If spatial index is needed depends on the data. It is certainly needed when the table is large (million features or so) and requests typically cover a small area. If for example 1000 features gets selected it means that the spatial index can filter out 999 thousand features. If the table is small like one thousand features, then it is not so expensive to read everything without spatial filter. You are giving us little by little more information. I do not believe that the PostgreSQL 13 vs version 11 is the main thing at all. However, I do not have databases hosted on AWS and I can not make tests in the similar environment that you have. -Jukka Rahkonen- Lähettäjä: MapServer-users Puolesta Rob Dennett via MapServer-users Lähetetty: tiistai 6. helmikuuta 2024 21.53 Vastaanottaja: mapserver-users@lists.osgeo.org Aihe: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11? Both DBs are hosted on AWS Aurora. The old one is serverless v1 and the new one is serverless v2. We were thinking that perhaps the geom column for my table needed a spatial index even though the old db doesn't have one. Is that possible? Also, would it make sense that the same query run on both should take the same amount of time (roughly)? Thanks, Rob ___ MapServer-users mailing list MapServer-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?
Both DBs are hosted on AWS Aurora. The old one is serverless v1 and the new one is serverless v2. We were thinking that perhaps the geom column for my table needed a spatial index even though the old db doesn't have one. Is that possible? Also, would it make sense that the same query run on both should take the same amount of time (roughly)? Thanks, Rob ___ MapServer-users mailing list MapServer-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?
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 && '0103000100050059E1E5BFA23513413F9EFD87EB755A4159E1E5BFA23513419D6B47D5447F5A414E296B41E8F913419D6B47D5447F5A414E296B41E8F913413F9EFD87EB755A4159E1E5BFA23513413F9EFD87EB755A41'::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 && '0103000100050059E1E5BFA23513413F9EFD87EB755A4159E1E5BFA23513419D6B47D5447F5A414E296B41E8F913419D6B47D5447F5A414E296B41E8F913413F9EFD87EB755A4159E1E5BFA23513413F9EFD87EB755A41'::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 Puolesta Rob Dennett via MapServer-users Lähetetty: tiistai 6. helmikuuta 2024 21.02 Vastaanottaja: mapserver-users@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-i...@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 mailto:traviskirst...@gmail.com>> Sent: Monday, February 5, 2024 10:50 AM To: Rob Dennett mailto:rob.denn...@twdb.texas.gov>> Cc: mapserver-users@lists.osgeo.org<mailto:mapserver-users@lists.osgeo.org> mailto:mapserver-users@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 cove
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-i...@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 Sent: Monday, February 5, 2024 10:50 AM To: Rob Dennett Cc: mapserver-users@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') ___ MapServer-users mailing list MapServer-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?
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') On Mon, 5 Feb 2024 at 10:35, Rob Dennett via MapServer-users < mapserver-users@lists.osgeo.org> wrote: > So, looking at this further, this is the bounding box for what's happening > is that when pointed at the old db, the .map file is making the calls using > these coordinates > > -12520996.7293382 2507134.52775378,-12520996.7293382 > 3754586.82936786,-11273544.4277241 3754586.82936786,-11273544.4277241 > 2507134.52775378,-12520996.7293382 2507134.52775378 > > and then again with these > > -12518550.744433 2509580.51265891,-12518550.744433 > 5004485.11588706,-10023646.1412049 5004485.11588706,-10023646.1412049 > 2509580.51265891,-12518550.744433 2509580.51265891 > > When pointed at the new db (which does indeed have identical data and > structure), the polygons are > > -11268652.4579138 2507134.52775378,-11268652.4579138 > 3754586.82936786,-10021200.1562997 3754586.82936786,-10021200.1562997 > 2507134.52775378,-11268652.4579138 2507134.52775378 > > and > > -12068252.5062205 2978893.85839647,-12068252.5062205 > 4369793.96473184,-10212686.4400848 4369793.96473184,-10212686.4400848 > 2978893.85839647,-12068252.5062205 2978893.85839647 > > I have no idea where these polygons are coming from, nor do I understand > why they are different when pointed at the old vs. new db nor why the > queries are called in a different order. The .map file has a line defining > the extent: > > EXTENT -11871597.4858696 2978893.85839647 -10409341.4604357 > 4369793.96473184 > > but that doesn't match up with these polygons. The new polygons appear to > be a bit smaller than the old ones, if I understand correctly. Does anyone > know why I am seeing what I am seeing? > > Thanks, > Rob > -- > *From:* Rahkonen Jukka > *Sent:* Monday, February 5, 2024 1:25 AM > *To:* Rob Dennett ; > mapserver-users@lists.osgeo.org > *Subject:* Re: [MapServer-users] Why is Mapserver slower in Postgres 13 > than in Postgres 11? > > > External: Beware of links/attachments. > > > Hi, > > > > See https://mapserver.org/input/vector/postgis.html, there is an example > about how to define the SRID on the DATA line: > DATA "the_geom from the_database using unique gid using srid=4326" > > > > The SRID value to use is the native SRID of the PostGIS table. > > What Mapserver does here is that with “&&” operator it selects those > features from the table which intersect with the reference geometry and > because of that it needs to know the SRID of the table. The reference > geometry (POLYGON) is either the BBOX of the request or the EXTENT used in > the mapfile, I cannot say for sure with this information. I believe that > find_srid is rather fast but it is good to include “using srid=” anyway. > Adding “unique” for defining the primary key of the table cannot make any > harm either. > > You wrote “There are 8 select statements in each, but the statements > aren't the same” and then you showed one of the statements. It would help > to see also the other statement. But if the tables have the same data and > the output from the new db is much larger, and there is no other filter in > the SQL query than the &&, then I quess that the reference polygon of the > latter case is bigger for some reason. > > > > -Jukka Rahkonen- > > > > *Lähettäjä:* MapServer-users > *Puolesta > *Rob Dennett via MapServer-users > *Lähetetty:* maanantai 5. helmikuuta 2024 2.54 > *Vastaanottaja:* mapserver-users@lists.osgeo.org > *Aihe:* Re
Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?
So, looking at this further, this is the bounding box for what's happening is that when pointed at the old db, the .map file is making the calls using these coordinates -12520996.7293382 2507134.52775378,-12520996.7293382 3754586.82936786,-11273544.4277241 3754586.82936786,-11273544.4277241 2507134.52775378,-12520996.7293382 2507134.52775378 and then again with these -12518550.744433 2509580.51265891,-12518550.744433 5004485.11588706,-10023646.1412049 5004485.11588706,-10023646.1412049 2509580.51265891,-12518550.744433 2509580.51265891 When pointed at the new db (which does indeed have identical data and structure), the polygons are -11268652.4579138 2507134.52775378,-11268652.4579138 3754586.82936786,-10021200.1562997 3754586.82936786,-10021200.1562997 2507134.52775378,-11268652.4579138 2507134.52775378 and -12068252.5062205 2978893.85839647,-12068252.5062205 4369793.96473184,-10212686.4400848 4369793.96473184,-10212686.4400848 2978893.85839647,-12068252.5062205 2978893.85839647 I have no idea where these polygons are coming from, nor do I understand why they are different when pointed at the old vs. new db nor why the queries are called in a different order. The .map file has a line defining the extent: EXTENT -11871597.4858696 2978893.85839647 -10409341.4604357 4369793.96473184 but that doesn't match up with these polygons. The new polygons appear to be a bit smaller than the old ones, if I understand correctly. Does anyone know why I am seeing what I am seeing? Thanks, Rob From: Rahkonen Jukka Sent: Monday, February 5, 2024 1:25 AM To: Rob Dennett ; mapserver-users@lists.osgeo.org Subject: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11? External: Beware of links/attachments. Hi, See https://mapserver.org/input/vector/postgis.html, there is an example about how to define the SRID on the DATA line: DATA "the_geom from the_database using unique gid using srid=4326" The SRID value to use is the native SRID of the PostGIS table. What Mapserver does here is that with “&&” operator it selects those features from the table which intersect with the reference geometry and because of that it needs to know the SRID of the table. The reference geometry (POLYGON) is either the BBOX of the request or the EXTENT used in the mapfile, I cannot say for sure with this information. I believe that find_srid is rather fast but it is good to include “using srid=” anyway. Adding “unique” for defining the primary key of the table cannot make any harm either. You wrote “There are 8 select statements in each, but the statements aren't the same” and then you showed one of the statements. It would help to see also the other statement. But if the tables have the same data and the output from the new db is much larger, and there is no other filter in the SQL query than the &&, then I quess that the reference polygon of the latter case is bigger for some reason. -Jukka Rahkonen- Lähettäjä: MapServer-users mailto:mapserver-users-boun...@lists.osgeo.org>> Puolesta Rob Dennett via MapServer-users Lähetetty: maanantai 5. helmikuuta 2024 2.54 Vastaanottaja: mapserver-users@lists.osgeo.org<mailto:mapserver-users@lists.osgeo.org> Aihe: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11? I don't think so. These queries are generated somehow. Here's what the PolygonSources layer looks like, and as you can see, the query I am specifying is just "geom from the_table_in_question": LAYER NAME "PolygonSources" CONNECTIONTYPE POSTGIS CONNECTION "xx" TYPE POLYGON STATUS ON DATA "geom FROM the_table_in_question" UTFITEM "cartodb_id" UTFDATA "{\"cartodb_id\":\"[cartodb_id]\",\"sourceid\":\"[sourceid]\",\"name\":\"[name]\",\"sourcetype\":\"[sourcetype]\",\"drawingord\":\"[drawingord]\",\"featuretyp\":\"[featuretyp]\",\"isnew\":\"[isnew]\"}" TEMPLATE WMSGetFeatureInfo FILTERITEM "featuretyp" CLASSITEM "sourceid" FILTER "polygon" CLASS NAME "polygon" EXPRESSION ([sourceid] != 169 AND [sourceid] != 412 AND [sourceid] != 820 AND [sourceid] != 1067) STYLE COLOR "#0B3A71B3" OUTLINECOLOR "#AFBFD0B3" OUTLINEWIDTH1.5 END END METADATA "wms_title" "Polygon Source Features" "wms_include_items" "all" "wms_abstract" "Layer of all polygon geometry sources." "wfs_title" "Polygon Source Features" "wfs_s
Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?
Hi, See https://mapserver.org/input/vector/postgis.html, there is an example about how to define the SRID on the DATA line: DATA "the_geom from the_database using unique gid using srid=4326" The SRID value to use is the native SRID of the PostGIS table. What Mapserver does here is that with “&&” operator it selects those features from the table which intersect with the reference geometry and because of that it needs to know the SRID of the table. The reference geometry (POLYGON) is either the BBOX of the request or the EXTENT used in the mapfile, I cannot say for sure with this information. I believe that find_srid is rather fast but it is good to include “using srid=” anyway. Adding “unique” for defining the primary key of the table cannot make any harm either. You wrote “There are 8 select statements in each, but the statements aren't the same” and then you showed one of the statements. It would help to see also the other statement. But if the tables have the same data and the output from the new db is much larger, and there is no other filter in the SQL query than the &&, then I quess that the reference polygon of the latter case is bigger for some reason. -Jukka Rahkonen- Lähettäjä: MapServer-users mailto:mapserver-users-boun...@lists.osgeo.org>> Puolesta Rob Dennett via MapServer-users Lähetetty: maanantai 5. helmikuuta 2024 2.54 Vastaanottaja: mapserver-users@lists.osgeo.org<mailto:mapserver-users@lists.osgeo.org> Aihe: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11? I don't think so. These queries are generated somehow. Here's what the PolygonSources layer looks like, and as you can see, the query I am specifying is just "geom from the_table_in_question": LAYER NAME "PolygonSources" CONNECTIONTYPE POSTGIS CONNECTION "xx" TYPE POLYGON STATUS ON DATA "geom FROM the_table_in_question" UTFITEM "cartodb_id" UTFDATA "{\"cartodb_id\":\"[cartodb_id]\",\"sourceid\":\"[sourceid]\",\"name\":\"[name]\",\"sourcetype\":\"[sourcetype]\",\"drawingord\":\"[drawingord]\",\"featuretyp\":\"[featuretyp]\",\"isnew\":\"[isnew]\"}" TEMPLATE WMSGetFeatureInfo FILTERITEM "featuretyp" CLASSITEM "sourceid" FILTER "polygon" CLASS NAME "polygon" EXPRESSION ([sourceid] != 169 AND [sourceid] != 412 AND [sourceid] != 820 AND [sourceid] != 1067) STYLE COLOR "#0B3A71B3" OUTLINECOLOR "#AFBFD0B3" OUTLINEWIDTH1.5 END END METADATA "wms_title" "Polygon Source Features" "wms_include_items" "all" "wms_abstract" "Layer of all polygon geometry sources." "wfs_title" "Polygon Source Features" "wfs_srs" "EPSG:3857 EPSG:4326" "wfs_enable_request" "*" "wfs_abstract" "Layer of all polygon geometry sources." "gml_include_items" "all" "gml_featureid" "cartodb_id" END PROJECTION "init=epsg:3857" END PROCESSING "CLOSE_CONNECTION=DEFER" END From: James Gardner mailto:j...@internode.on.net>> Sent: Sunday, February 4, 2024 6:44 PM To: Rob Dennett mailto:rob.denn...@twdb.texas.gov>> Cc: Travis Kirstine mailto:traviskirst...@gmail.com>>; mapserver-users@lists.osgeo.org<mailto:mapserver-users@lists.osgeo.org> mailto:mapserver-users@lists.osgeo.org>> Subject: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11? External: Beware of links/attachments. Could you try replacing find_srid with a hard coded srid... I found it had to run find_srid on every tuple... -James Gardner ___ MapServer-users mailing list MapServer-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?
I don't think so. These queries are generated somehow. Here's what the PolygonSources layer looks like, and as you can see, the query I am specifying is just "geom from the_table_in_question": LAYER NAME "PolygonSources" CONNECTIONTYPE POSTGIS CONNECTION "xx" TYPE POLYGON STATUS ON DATA "geom FROM the_table_in_question" UTFITEM "cartodb_id" UTFDATA "{\"cartodb_id\":\"[cartodb_id]\",\"sourceid\":\"[sourceid]\",\"name\":\"[name]\",\"sourcetype\":\"[sourcetype]\",\"drawingord\":\"[drawingord]\",\"featuretyp\":\"[featuretyp]\",\"isnew\":\"[isnew]\"}" TEMPLATE WMSGetFeatureInfo FILTERITEM "featuretyp" CLASSITEM "sourceid" FILTER "polygon" CLASS NAME "polygon" EXPRESSION ([sourceid] != 169 AND [sourceid] != 412 AND [sourceid] != 820 AND [sourceid] != 1067) STYLE COLOR "#0B3A71B3" OUTLINECOLOR "#AFBFD0B3" OUTLINEWIDTH1.5 END END METADATA "wms_title" "Polygon Source Features" "wms_include_items" "all" "wms_abstract" "Layer of all polygon geometry sources." "wfs_title" "Polygon Source Features" "wfs_srs" "EPSG:3857 EPSG:4326" "wfs_enable_request" "*" "wfs_abstract" "Layer of all polygon geometry sources." "gml_include_items" "all" "gml_featureid" "cartodb_id" END PROJECTION "init=epsg:3857" END PROCESSING "CLOSE_CONNECTION=DEFER" END From: James Gardner Sent: Sunday, February 4, 2024 6:44 PM To: Rob Dennett Cc: Travis Kirstine ; mapserver-users@lists.osgeo.org Subject: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11? External: Beware of links/attachments. Could you try replacing find_srid with a hard coded srid... I found it had to run find_srid on every tuple... -James Gardner On 5 Feb 2024 5:39 am, Rob Dennett via MapServer-users wrote: Running select * from pg_indexes where tablename like '%the_table_in_question%' yields the same results for both the old and new dbs. As mentioned, the mapserver debug output is very different from the old and new db are very different, even though the .map files are identical except for the connection string. There are 8 select statements in each, but the statements aren't the same and the output from the new db is twice as large, about 1.9MB, as the old one. I compiled the queries each one runs and ran them as a group against both dbs. The old db ran both sets of queries in about 5 seconds each, whereas the new db ran the new set in about 15 seconds and the old set in about 10 seconds. I haven't yet run VACUUM on the new db, and will be doing so I Monday, but I am just as confused as to why the debug output would be so different. Can anyone shed some light on that? Thanks, Rob From: Travis Kirstine Sent: Sunday, February 4, 2024 10:18 AM To: Rob Dennett Cc: mapserver-users@lists.osgeo.org Subject: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11? External: Beware of links/attachments. First thought is to check if the table has a spatial index. https://postgis.net/workshops/postgis-intro/indexing.html On Fri, 2 Feb 2024 at 18:37, Rob Dennett via MapServer-users mailto:mapserver-users@lists.osgeo.org>> wrote: I recently upgraded our db for our Mapserver from Postgres 11 to Postgres 13 and also had to update Postgis from 2.4 to 3.4, as well as remove the postgis 2.4 raster queries and add the new postgis_raster extension. Now I have web request which takes over 30 seconds where it used to take about 2. I made copies of the .map file and question and pointed one at the old db and set the debug level for the map to 2 for both. These .map files were created by someone who no longer works here and I am not a GIS professional, just a software developer, so I am having trouble understanding the output. After hitting the web page for both, I see the debug file is twice as large for the new db as it was for the old one. The .map file has 4 layers, three of which have a status of on, one for polygons, one for lines and one for point and one called "AllSources" (which is off). For the first 3, they contain DATA "geom from the_table_in_question" which is a field of type geometry. In the logs I see lines like select "sourceid"::text,"featuretyp"
Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?
Could you try replacing find_srid with a hard coded srid... I found it had to run find_srid on every tuple...-James GardnerOn 5 Feb 2024 5:39 am, Rob Dennett via MapServer-users wrote: Running select * from pg_indexes where tablename like '%the_table_in_question%' yields the same results for both the old and new dbs. As mentioned, the mapserver debug output is very different from the old and new db are very different, even though the .map files are identical except for the connection string. There are 8 select statements in each, but the statements aren't the same and the output from the new db is twice as large, about 1.9MB, as the old one. I compiled the queries each one runs and ran them as a group against both dbs. The old db ran both sets of queries in about 5 seconds each, whereas the new db ran the new set in about 15 seconds and the old set in about 10 seconds. I haven't yet run VACUUM on the new db, and will be doing so I Monday, but I am just as confused as to why the debug output would be so different. Can anyone shed some light on that? Thanks, Rob From: Travis Kirstine Sent: Sunday, February 4, 2024 10:18 AM To: Rob Dennett Cc: mapserver-users@lists.osgeo.org Subject: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11? External: Beware of links/attachments. First thought is to check if the table has a spatial index. https://postgis.net/workshops/postgis-intro/indexing.html On Fri, 2 Feb 2024 at 18:37, Rob Dennett via MapServer-users <mapserver-users@lists.osgeo.org> wrote: I recently upgraded our db for our Mapserver from Postgres 11 to Postgres 13 and also had to update Postgis from 2.4 to 3.4, as well as remove the postgis 2.4 raster queries and add the new postgis_raster extension. Now I have web request which takes over 30 seconds where it used to take about 2. I made copies of the .map file and question and pointed one at the old db and set the debug level for the map to 2 for both. These .map files were created by someone who no longer works here and I am not a GIS professional, just a software developer, so I am having trouble understanding the output. After hitting the web page for both, I see the debug file is twice as large for the new db as it was for the old one. The .map file has 4 layers, three of which have a status of on, one for polygons, one for lines and one for point and one called "AllSources" (which is off). For the first 3, they contain DATA "geom from the_table_in_question" which is a field of type geometry. In the logs I see lines like 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') There are 8 of these in each output file, but their contents don't match. I am not sure what's generating them. I do note that they, along with SELECT geom from ...; statements take roughly equal time whether executed against the old db or the new one. What is generating these log statements? Why does what is for all intents and purposes the same .map file produce different output? I know it's because I changed the db engine and postgis, but if you could be more specific, that would be very helpful. Thanks, Rob ___ MapServer-users mailing list MapServer-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapserver-users ___ MapServer-users mailing list MapServer-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?
So, after further analysis of the queries, when pointed at the old db, the mapserver output contains 4 queries which map to the 4 layers in the .map file, one for polygon, then line, then point, then centroid. These 4 are then repeated with different polygons. The queries all look similar, e.g. 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('','the_table_in_question','geom')) and ("featuretyp"::text = 'polygon'); When run against the new db (with the same data, mind you, just a different version of Postgres and Postgis), the queries are polygon, polygon, line, line, point, point and centroid, centroid. While the contents of the POLYGON() function are the same for each set of 4 queries, they don't completely match what's seen in the other debug output file. That is, only every other number matches: old -12520996.7293382 2507134.52775378,-12520996.7293382 3754586.82936786,-11273544.4277241 3754586.82936786,-11273544.4277241 2507134.52775378,-12520996.7293382 2507134.52775378 new -11268652.4579138 2507134.52775378,-11268652.4579138 3754586.82936786,-10021200.1562997 3754586.82936786,-10021200.1562997 2507134.52775378,-11268652.4579138 2507134.52775378 What is POLYGON in the above query? Is it the same as ST_Polygon? What do the numbers mean? Thanks, Rob ____ From: MapServer-users on behalf of Rob Dennett via MapServer-users Sent: Sunday, February 4, 2024 3:39 PM To: Travis Kirstine Cc: mapserver-users@lists.osgeo.org Subject: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11? External: Beware of links/attachments. Running select * from pg_indexes where tablename like '%the_table_in_question%' yields the same results for both the old and new dbs. As mentioned, the mapserver debug output is very different from the old and new db are very different, even though the .map files are identical except for the connection string. There are 8 select statements in each, but the statements aren't the same and the output from the new db is twice as large, about 1.9MB, as the old one. I compiled the queries each one runs and ran them as a group against both dbs. The old db ran both sets of queries in about 5 seconds each, whereas the new db ran the new set in about 15 seconds and the old set in about 10 seconds. I haven't yet run VACUUM on the new db, and will be doing so I Monday, but I am just as confused as to why the debug output would be so different. Can anyone shed some light on that? Thanks, Rob ____________ From: Travis Kirstine Sent: Sunday, February 4, 2024 10:18 AM To: Rob Dennett Cc: mapserver-users@lists.osgeo.org Subject: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11? External: Beware of links/attachments. First thought is to check if the table has a spatial index. https://postgis.net/workshops/postgis-intro/indexing.html On Fri, 2 Feb 2024 at 18:37, Rob Dennett via MapServer-users mailto:mapserver-users@lists.osgeo.org>> wrote: I recently upgraded our db for our Mapserver from Postgres 11 to Postgres 13 and also had to update Postgis from 2.4 to 3.4, as well as remove the postgis 2.4 raster queries and add the new postgis_raster extension. Now I have web request which takes over 30 seconds where it used to take about 2. I made copies of the .map file and question and pointed one at the old db and set the debug level for the map to 2 for both. These .map files were created by someone who no longer works here and I am not a GIS professional, just a software developer, so I am having trouble understanding the output. After hitting the web page for both, I see the debug file is twice as large for the new db as it was for the old one. The .map file has 4 layers, three of which have a status of on, one for polygons, one for lines and one for point and one called "AllSources" (which is off). For the first 3, they contain DATA "geom from the_table_in_question" which is a field of type geometry. In the logs I see lines like 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
Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?
Running select * from pg_indexes where tablename like '%the_table_in_question%' yields the same results for both the old and new dbs. As mentioned, the mapserver debug output is very different from the old and new db are very different, even though the .map files are identical except for the connection string. There are 8 select statements in each, but the statements aren't the same and the output from the new db is twice as large, about 1.9MB, as the old one. I compiled the queries each one runs and ran them as a group against both dbs. The old db ran both sets of queries in about 5 seconds each, whereas the new db ran the new set in about 15 seconds and the old set in about 10 seconds. I haven't yet run VACUUM on the new db, and will be doing so I Monday, but I am just as confused as to why the debug output would be so different. Can anyone shed some light on that? Thanks, Rob From: Travis Kirstine Sent: Sunday, February 4, 2024 10:18 AM To: Rob Dennett Cc: mapserver-users@lists.osgeo.org Subject: Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11? External: Beware of links/attachments. First thought is to check if the table has a spatial index. https://postgis.net/workshops/postgis-intro/indexing.html On Fri, 2 Feb 2024 at 18:37, Rob Dennett via MapServer-users mailto:mapserver-users@lists.osgeo.org>> wrote: I recently upgraded our db for our Mapserver from Postgres 11 to Postgres 13 and also had to update Postgis from 2.4 to 3.4, as well as remove the postgis 2.4 raster queries and add the new postgis_raster extension. Now I have web request which takes over 30 seconds where it used to take about 2. I made copies of the .map file and question and pointed one at the old db and set the debug level for the map to 2 for both. These .map files were created by someone who no longer works here and I am not a GIS professional, just a software developer, so I am having trouble understanding the output. After hitting the web page for both, I see the debug file is twice as large for the new db as it was for the old one. The .map file has 4 layers, three of which have a status of on, one for polygons, one for lines and one for point and one called "AllSources" (which is off). For the first 3, they contain DATA "geom from the_table_in_question" which is a field of type geometry. In the logs I see lines like 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') There are 8 of these in each output file, but their contents don't match. I am not sure what's generating them. I do note that they, along with SELECT geom from ...; statements take roughly equal time whether executed against the old db or the new one. What is generating these log statements? Why does what is for all intents and purposes the same .map file produce different output? I know it's because I changed the db engine and postgis, but if you could be more specific, that would be very helpful. Thanks, Rob ___ MapServer-users mailing list MapServer-users@lists.osgeo.org<mailto:MapServer-users@lists.osgeo.org> https://lists.osgeo.org/mailman/listinfo/mapserver-users ___ MapServer-users mailing list MapServer-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?
First thought is to check if the table has a spatial index. https://postgis.net/workshops/postgis-intro/indexing.html On Fri, 2 Feb 2024 at 18:37, Rob Dennett via MapServer-users < mapserver-users@lists.osgeo.org> wrote: > I recently upgraded our db for our Mapserver from Postgres 11 to Postgres > 13 and also had to update Postgis from 2.4 to 3.4, as well as remove the > postgis 2.4 raster queries and add the new postgis_raster extension. Now I > have web request which takes over 30 seconds where it used to take about > 2. I made copies of the .map file and question and pointed one at the old > db and set the debug level for the map to 2 for both. > > These .map files were created by someone who no longer works here and I am > not a GIS professional, just a software developer, so I am having trouble > understanding the output. > > After hitting the web page for both, I see the debug file is twice as > large for the new db as it was for the old one. The .map file has 4 > layers, three of which have a status of on, one for polygons, one for lines > and one for point and one called "AllSources" (which is off). For the > first 3, they contain > > DATA "geom from the_table_in_question" > > which is a field of type geometry. In the logs I see lines like > > 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') > > There are 8 of these in each output file, but their contents don't match. > I am not sure what's generating them. I do note that they, along with > SELECT geom from ...; statements take roughly equal time whether executed > against the old db or the new one. > > What is generating these log statements? > > Why does what is for all intents and purposes the same .map file produce > different output? I know it's because I changed the db engine and postgis, > but if you could be more specific, that would be very helpful. > > Thanks, > Rob > ___ > MapServer-users mailing list > MapServer-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/mapserver-users > ___ MapServer-users mailing list MapServer-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapserver-users
[MapServer-users] Why is Mapserver slower in Postgres 13 than in Postgres 11?
I recently upgraded our db for our Mapserver from Postgres 11 to Postgres 13 and also had to update Postgis from 2.4 to 3.4, as well as remove the postgis 2.4 raster queries and add the new postgis_raster extension. Now I have web request which takes over 30 seconds where it used to take about 2. I made copies of the .map file and question and pointed one at the old db and set the debug level for the map to 2 for both. These .map files were created by someone who no longer works here and I am not a GIS professional, just a software developer, so I am having trouble understanding the output. After hitting the web page for both, I see the debug file is twice as large for the new db as it was for the old one. The .map file has 4 layers, three of which have a status of on, one for polygons, one for lines and one for point and one called "AllSources" (which is off). For the first 3, they contain DATA "geom from the_table_in_question" which is a field of type geometry. In the logs I see lines like 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') There are 8 of these in each output file, but their contents don't match. I am not sure what's generating them. I do note that they, along with SELECT geom from ...; statements take roughly equal time whether executed against the old db or the new one. What is generating these log statements? Why does what is for all intents and purposes the same .map file produce different output? I know it's because I changed the db engine and postgis, but if you could be more specific, that would be very helpful. Thanks, Rob ___ MapServer-users mailing list MapServer-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/mapserver-users