MapServer PostGIS query performance

ms-postgis-query-performance.md

Overview

• works on 6.4.x
• times out on 7.0.2

Environment

MapServer 7.0.2 PostgreSQL 9.3.14 PostGIS 2.2

MapServer LAYER object

LAYER
 NAME "totalozone"
 STATUS ON
 DEBUG 5
 CONNECTION "host=localhost dbname=foo user=foo password=foo"
 CONNECTIONTYPE POSTGIS
 PROCESSING "CLOSE_CONNECTION=DEFER"
 DATA "the_geom from (select * from totalozone order by instance_datetime) as subquery using unique data_payload_id using srid=4326"
 TYPE POINT
 DUMP TRUE
 TEMPLATE "ttt.html"
 PROJECTION
  "init=epsg:4326"
 END
 METADATA
  "ows_title" "totalozone"
  "gml_include_items" "all"
 END
END

Request:

http://example.org/ows?map=/tmp/foo.map&service=WFS&version=1.1.0&request=GetFeature&outputformat=GeoJSON&typename=totalozone&filter=<ogc:Filter><ogc:And><ogc:BBOX><PropertyName>msGeometry</PropertyName><Box srsName="EPSG:4326"><coordinates>-188.4375,-85.67541458664353 190.546875,83.44032649527307</coordinates></Box></ogc:BBOX><ogc:PropertyIsBetween><ogc:PropertyName>instance_datetime</ogc:PropertyName><ogc:LowerBoundary>1924-01-01 00:00:00</ogc:LowerBoundary><ogc:UpperBoundary>2016-12-31 23:59:59</ogc:UpperBoundary></ogc:PropertyIsBetween></ogc:And></ogc:Filter>&startindex=0&maxfeatures=1

Filter pretty-printed

<ogc:Filter>
 <ogc:And>
  <ogc:BBOX>
   <PropertyName>msGeometry</PropertyName>
   <Box srsName="EPSG:4326">
    <coordinates>-188.4375,-85.67541458664353 190.546875,83.44032649527307</coordinates>
   </Box>
  </ogc:BBOX>
  <ogc:PropertyIsBetween>
   <ogc:PropertyName>instance_datetime</ogc:PropertyName>
   <ogc:LowerBoundary>1924-01-01 00:00:00</ogc:LowerBoundary>
   <ogc:UpperBoundary>2016-12-31 23:59:59</ogc:UpperBoundary>
  </ogc:PropertyIsBetween>
 </ogc:And>
</ogc:Filter>

MapServer 6.4.2

select "data_payload_id","data_payload_uri","instance_datetime","url","agency","platform_type","platform_id","platform_name","gaw_id","instrument_name","instrument_model","instrument_number","monthly_date","monthly_columno3","monthly_stddevo3","monthly_npts","daily_date","daily_wlcode","daily_obscode","daily_columno3","daily_stddevo3","daily_utc_begin","daily_utc_end","daily_utc_mean","daily_nobs","daily_mmu","daily_columnso2","latest_observation",encode(ST_AsBinary(ST_Force2D("the_geom"),'NDR'),'hex') as geom,"data_payload_id" from (select * from totalozone order by instance_datetime ASC) as subquery where the_geom && ST_GeomFromText('POLYGON((-188.4375 -85.6754145866435,-188.4375 83.4403264952731,190.546875 83.4403264952731,190.546875 -85.6754145866435,-188.4375 -85.6754145866435))',4326) and ( ("instance_datetime" BETWEEN '1924-01-01 00:00:00' AND '2016-12-31 23:59:59')) limit 1 offset 0

MapServer 7.0.2

select "data_payload_id","data_payload_uri","instance_datetime","url","agency","platform_type","platform_id","platform_name","gaw_id","instrument_name","instrument_model","instrument_number","monthly_date","monthly_columno3","monthly_stddevo3","monthly_npts","daily_date","daily_wlcode","daily_obscode","daily_columno3","daily_stddevo3","daily_utc_begin","daily_utc_end","daily_utc_mean","daily_nobs","daily_mmu","daily_columnso2","latest_observation",encode(ST_AsBinary(ST_Force2D("the_geom"),'NDR'),'hex') as geom,"data_payload_id" from (select * from totalozone order by instance_datetime ASC) as subquery where the_geom && ST_GeomFromText('POLYGON((-85.6754145866435 -188.4375,-85.6754145866435 190.546875,83.4403264952731 190.546875,83.4403264952731 -188.4375,-85.6754145866435 -188.4375))',4326) and ((st_intersects(the_geom,ST_GeomFromText('POLYGON ((-85.6754145866435266 -188.4375000000000000, -85.6754145866435266 190.5468750000000000, 83.4403264952730694 190.5468750000000000, 83.4403264952730694 -188.4375000000000000, -85.6754145866435266 -188.4375000000000000))',4326)) = TRUE and ("instance_datetime" >= date_trunc('second',timestamp '1924-01-01 00:00:00') and "instance_datetime" <= (date_trunc('second',timestamp '2016-12-31 23:59:59') + interval '1 second' - interval '1 second')))) ORDER BY "instance_datetime"

BTW There's a little cut and paste error in your 7.0.2 SQL snippet. If I fix and run that in pgAdmin against my data, which may be way off from yours (for example, I'm missing data_payload_uri column) it runs fine. My dataset has 25,000 rows.

TODO:

• change WFS filter to update srsName="urn:ogc:def:crs:OGC:1.3:CRS:84"
• test SQL without the extra st_intersects query

cc @rouault

It turns out that the issue is 6.4 ignored the WFS sortby parameter, whereas 7.0 does not. This results in a double order by clauses in the SQL if the users specifies a subquery which includes an order by in their LAYER.DATA directive. So in summary a workaround is not including order by in LAYER.DATA statements and delegate to WFS proper.