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
