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