Improve performance in Mapserver on Postgis-Layer using FILTER (workaround for the TOAST-Problem)

[Paul Ramsey]

Leonhard,
You might be able to make some headway with the evil "!BOX!" hack.

In your data statement, if you compose it thusly:

DATA "the_geom from (select gid,the_geom from thetable where the_geom  
&& !BOX!) using unique gid"

When the postgis driver encounters the evil !BOX! it will replace it  
with the current mapserver bounding box.  I think it also eschews  
building an extra WHERE clause with it, although I am not certain of  
that.  Anyways, you could use !BOX! to put in an extra && clause on  
your untoasted column.

P

On Jan 27, 2006, at 4:38 AM, Leonhard Dietze wrote:

> Hi,
>
> I have several layers with rather large (detailed)
> Polygon-geometries in PostGIS. While searching for
> possible solutions to improve the performance of
> my mapping-application I read about the TOAST-
> Problem. (I am using MapServer version 4.6.1 and
> Postgres 8.0.1)
>
> After further research I read about a possible
> Workaround for TOAST in PostGIS: I created a new
> column in my Postgres-Table where only the
> BoundingBox (bbox) is stored and use this as a
> filter-attribute while selecting the geometry
> (See:
> http://postgis.refractions.net/docs/ch05.html#id2789728
> If I do this manually, then the query only takes
> 40% of the time needed before.
>
> Unfortunately I do not know if (or how) it is
> possible to tell the mapserver to use this "bbox"
> for the filter. If I use the parameter
>   'FILTER "bbox"'
> in the map-file then this is inserted at the wrong
> position in the SQL-Query String. The full
> SQL-String can be found below together with the
> explanation of the parameters.
>
> The normal FILTER within the SQL-String looks as
> follows:
> "SELECT ... WHERE staat_geom_test && setSRID('BOX3D(137493  
> 4817476,616506
> 5222523)'::BOX3D, 4326 );"
>
> And with the FILTER-param from the map-file:
> SELECT ... WHERE (bbox) and (staat_geom_test && setSRID('BOX3D(137493
> 4817476,616506 5222523)'::BOX3D,4326) );
>
> But it should look like this:
> SELECT ... WHERE bbox && setSRID('BOX3D(137493 4817476,616506  
> 5222523)'::
> BOX3D, 4326 );
>
> Can somebody tell me if this is possible or
> do I have to dig into the source code?
>
> Thanks in advance.
> Leo
>
>
> --------------------------------------------
> FULL SQL-Query String:
>
> - "staat_geom_test" is the column with
>   the full geometry
> - "bbox" is the column with the BoundingBox
>
> SELECT
> h::text,asbinary(force_collection(force_2d 
> (staat_geom_test)),'XDR'),gid::
> text
>
>
> from u_g1_1820 as foo WHERE bbox &&
> setSRID('BOX3D(137493.827160494
> 4817476.39796659,616506.172839506
> 5222523.60203341)'::BOX3D, 4326 );
>
> --------------------------------------------
> Excerpt from the map-file
>
>   LAYER
>         CONNECTIONTYPE postgis
>         NAME          laender_33n_1820
>         CONNECTION    "user=xxxx
>        password=xxxx dbname=hgis_germany host=xxx"
>         DATA          "staat_geom_test from
> u_g1_1820 as foo using unique gid using srid=4326"
>         STATUS        OFF
>         TYPE          POLYGON
>         TRANSPARENCY  95
>         DUMP          true
>         CLASSITEM  h
>         CLASS
>               EXPRESSION /2/
>               NAME    "Länder innen 1820"
>               COLOR   255 235 190
>               OUTLINECOLOR 150 150 150
>               SIZE    8
>               TEMPLATE "templates/tpl_combined.html"
>         END
>         CLASS
>               EXPRESSION /5/
>               NAME    "Länder 1820 besond."
>               COLOR 204 204 204
>               OUTLINECOLOR  235 235 235
>               SIZE    8
>               TEMPLATE "templates/tpl_combined.html"
>         END
>         CLASS
>               NAME    "Länder aussen 1820"
>               COLOR   235 235 235
>               OUTLINECOLOR  235 235 235
>               SIZE    8
>               TEMPLATE "templates/tpl_combined.html"
>         END
>         METADATA
>               "title"     "Länder 1820"
>               "wms_name"  "laender_33n_1820"
>               "wms_title" "Länder 1820"
>               "wms_srs"   "EPSG:4326"
>               "wms_server_version"  "1.1.0"
>               "wms_format"   "image/png"
>         END
>   END
>
>
>
> * * * * * * * * * * * * * * * * * * * *
> Institut i3mainz
> Fachhochschule Mainz
> Leonhard Dietze
> Holzstraße 36
> 55116 Mainz
> Tel.: 06131-2859-686
> dietze at geoinform.fh-mainz.de
> * * * * * * * * * * * * * * * * * * * *