[Mapserver-users] Optimising PostGIS relates/subselects/views

[Antti.Roppola at brs.gov.au]

Thanks, the indexing reduced the map render time from 82 seconds
to 9 seconds.

Cheers,

Antti

-----Original Message-----
From: David Blasby [mailto:dblasby at refractions.net]
Sent: Tuesday, 4 February 2003 11:41 AM
To: Antti.Roppola at brs.gov.au
Cc: mapserver-users at lists.gis.umn.edu
Subject: Re: [Mapserver-users] Optimising PostGIS
relates/subselects/views


>         DATA "the_geom FROM
>                         (SELECT mylayer.oid AS oid,
>                                 mylayer.the_geom AS the_geom,
>                                 mytable.value AS value
>                         FROM mylayer, mytable
>                         WHERE mylayer.lut=mytable.lut)
>                         AS mylayer
>                         USING oid
>                         USING srid=-1"
>
>         CLASSITEM "value"

Ensure you have an index on mylayer.lut and mytable.lut:

CREATE INDEX mytable_lut_idx ON mytable (lut);
CREATE INDEX mylayer_lut_idx ON mylayer (lut);

You might want to change "...AS mylayer..." to "..AS foo..." so you're not
equivocating "mylayer".

If this doesnt speed things up, you'll have to use the new
index-selectivity support in the CVS version of PostGIS.  See the postgis
mailing list for more details.

dave