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

[Rahkonen Jukka via MapServer-users]

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
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>>
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?

[Rob Dennett via MapServer-users]

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"::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 

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

[James Gardner via MapServer-users]

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  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?

[Rob Dennett via MapServer-users]

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 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 = 

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

[Rob Dennett via MapServer-users]

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
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?

[Travis Kirstine via MapServer-users]

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