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 "xxxxxxxxxx"
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"
OUTLINEWIDTH 1.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 <j...@internode.on.net>
Sent: Sunday, February 4, 2024 6:44 PM
To: Rob Dennett <rob.denn...@twdb.texas.gov>
Cc: Travis Kirstine <traviskirst...@gmail.com>; mapserver-users@lists.osgeo.org
<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
<mapserver-users@lists.osgeo.org> 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 <traviskirst...@gmail.com>
Sent: Sunday, February 4, 2024 10:18 AM
To: Rob Dennett <rob.denn...@twdb.texas.gov>
Cc: mapserver-users@lists.osgeo.org <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<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
