[mapserver-users] Create a tileindex for WFS

[Jeff McKenna]

Since you are using MS4W here are some specific steps for your PostGIS 
query/speed testing (I am adding this now to the documentation on 
mapserver.org also) :

- add into your MAP-level of your mapfile:

   CONFIG "CPL_DEBUG" "ON"
   CONFIG "MS_ERRORFILE" "/ms4w/tmp/ms_error.txt"
   DEBUG 5

- add into your PostGIS LAYER of your mapfile:

   DEBUG 5

- now use a WFS client such as QGIS and add your WFS PostGIS layer

- open  "/ms4w/tmp/ms_error.txt" in Notepad++

- search for "msPostGISLayerWhichShapes query:"

- that line should list the long exact query sent from MapServer to the 
PostreSQL instance, it may look like:

   [Mon Apr 12 11:27:34 2021].207000 msPostGISLayerWhichShapes query: 
SELECT 
"gid"::text,"area"::text,"perimeter"::text,"province_"::text,"province_i"::text,"status"::text,"name"::text,"name_e"::text,"name_f"::text,"reg_code"::text,"poly_featu"::text,"island"::text,"island_e"::text,"island_f"::text,ST_AsBinary(("geom"),'NDR') 
as geom,"gid"::text FROM province WHERE "geom" && 
ST_GeomFromText('POLYGON((-5814679.36987815 
-1504714.04276694,-5814679.36987815 4439806.52253364,5943763.33635122 
4439806.52253364,5943763.33635122 -1504714.04276694,-5814679.36987815 
-1504714.04276694))',3978) LIMIT 2 OFFSET 0

- now connect to that database through psql.exe

   psql -U postgres -p 5432 -d mydb

- using that error file line, grab everything from "SELECT", and inside 
your database prompt, start the command with "EXPLAIN ANALYZE" and then 
paste your full query, such as:

mydb=# EXPLAIN ANALYZE SELECT 
"gid"::text,"area"::text,"perimeter"::text,"province_"::text,"province_i"::text,"status"::text,"name"::text,"name_e"::text,"name_f"::text,"reg_code"::text,"poly_featu"::text,"island"::text,"island_e"::text,"island_f"::text,ST_AsBinary(("geom"),'NDR') 
as geom,"gid"::text FROM province WHERE "geom" && 
ST_GeomFromText('POLYGON((-5814679.36987815 
-1504714.04276694,-5814679.36987815 4439806.52253364,5943763.33635122 
4439806.52253364,5943763.33635122 -1504714.04276694,-5814679.36987815 
-1504714.04276694))',3978) LIMIT 2 OFFSET 0;

- the response will tell you how long that query took, such as:

   Execution time: 0.293 ms

Hope that helps you a little more,


Thank-you for using MS4W.
"MS4W: open doors as well as windows"

-jeff




-- 
Jeff McKenna
GatewayGeo: Developers of MS4W, MapServer Consulting and Training
co-founder of FOSS4G
http://gatewaygeo.com/





On 2021-04-12 9:00 a.m., mathias cunault via mapserver-users wrote:
> Hello,
> In a PostgreSQL 13 database, I have 3 materialized views with 2578, 
> 22013 and 62500 rows.
> I am using MS4W 4.0.3 to generate a WFS based on these 3 views.
> The aim is for users to display these WFS in QGIS.
> I have other lightweight views in PostgreSQL that can be easily 
> displayed, but these 3 materialized views seem to be too heavy to 
> display because some messages say that Downloading the layer entities 
> has failed or partially failed.
> I wonder if using a tileindex was a good idea to speed up the display. 
> Before going further in the developments, I would like your opinion 
> about two things :
> 
>   * I have to cut my views in "slices" before create a tileIndex for
>     each one.
>   * The tileindex can be stored in postgis database.
> 
> Am I right?
> If not is there a better solution ?
> Thanks for your help
> 
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
>