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

[Leonhard Dietze]

Hi Paul,

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
improvement.
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.

Thanks again.
Leonhard


Paul Ramsey schrieb:
> 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