Improve performance in Mapserver on Postgis-Layer using
FILTER (workaround for the TOAST-Problem)
dietze at GEOINFORM.FH-MAINZ.DE
Tue Jan 31 03:24:38 EST 2006
thanks for the fast reply from last week. I tried
several options with "!BOX!" but it seems to me
that unformtunately it does't bring much
I got the impression that the use of the subselect
and the !BOX!-Parameter improves the performance
of the query but also needs more time to execute
because it is more complex. I also had to add
setSRID( !BOX! , 4326 ) in the DATA-String,
because mapserver gave me an error:
DATA "the_geom from (select the_geom from thetable
where the_geom && setSRID(!BOX!, 4326) ) as foo
using unique gid using srid=4326"
The original Data-String was:
DATA "the_geom from thetable as foo using unique
gid using srid=4326"
The second one takes about 18 ms for the query,
and the one with !BOX! needs around 23 ms. So I
think that it is better to keep the first simple
query and try to optimize somewhere else.
Paul Ramsey schrieb:
> 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.
More information about the mapserver-users