Performance hints please

[Stephen Davies]

Thanks for the feedback. I have been distracted by easter and other 
priorities and have only just got back to this question.
Here is a sample explain output. I think it says that my DB structure is 
OK. (The statement is taken from a log of a mapserver session.)

The biggest geometry table in this database has only 166 rows.

I don't know whether the connection is persistent or not. How does 
mapserv manage connections?

The TIFF rasters that I am using are included in their entirety. That 
is, the map extent is the same or bigger than the image. Are overviews 
still relevant in this context?

What is the significance of the OID use? This table (and all others) has 
a proper unique key.

Cheers and thanks,
Stephen

sdc=# explain SELECT 
type::text,asbinary(force_collection(force_2d(geom)),'NDR'),OID::text 
from cad WHERE (gtype='T' and client_id=14) and (geom && 
setSRID( 'BOX3D(139.2003702 -35.7073913851792,139.2325063 
-35.675186053051)'::BOX3D,find_srid('','cad','geom') ));
                                                                                                                          
QUERY PLAN                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on cad  (cost=5.52..18.78 rows=3 width=76)
   Recheck Cond: (client_id = 14)
   Filter: ((gtype = 'T'::text) AND (geom && 
'0103000020BB1000000100000005000000FA02C46E696661408D6808CD8BDA41C0FA02C46E69666140E148207F6CD641C0A6530DB170676140E148207F6CD641C0A6530DB1706761408D6808CD8BDA41C0FA02C46E696661408D6808CD8BDA41C0'::geometry))
   ->  BitmapAnd  (cost=5.52..5.52 rows=6 width=0)
         ->  Bitmap Index Scan on cad_geox  (cost=0.00..1.06 rows=17 
width=0)
               Index Cond: (geom && 
'0103000020BB1000000100000005000000FA02C46E696661408D6808CD8BDA41C0FA02C46E69666140E148207F6CD641C0A6530DB170676140E148207F6CD641C0A6530DB1706761408D6808CD8BDA41C0FA02C46E696661408D6808CD8BDA41C0'::geometry)
         ->  Bitmap Index Scan on cadclix  (cost=0.00..4.21 rows=59 
width=0)
               Index Cond: (client_id = 14)
(8 rows)


On Friday 07 April 2006 03:09, Clint Johnson wrote:
> I don't know of any.  Optimizing the map server configuration is
> could be the wrong way of going about solving your problem
>
> Look into optimizing your database.  Sure there are indexes; but are
> they being used? "explain" can help you determine that
> (ie
> http://www.redhat.com/docs/manuals/database/RHDB-7.1.3-Manual/sql/sql
>-explain.html)
>
>
> - Are there joins or subqueries going on "under the hood"?  Joins are
> an expensive operation whether there is an index or no index.
> - Perhaps an expensive trigger is getting fired for every select?
> - etc.
>
> Ed is right, "The amount of geometry involved is so small, the
> bottleneck has to be the raster processing" --  is a bad assumption.
> Retrieving a small amount of data from a large table will take while
> (indexes help).  Performing a join takes even longer.
>
> Explain will explain.
>
> Stephen Davies wrote:
> > G'day all.
> >
> > Does anybody have a howto on Mapserver performance enhancement?
> >
> > My particular environment of interest is a relatively small set of
> > geometries in a PostGIS database and a collection of geotiff aerial
> > photography images onto which I overlay the geometries.
> >
> > The geometries are fully indexed, relatively static and vacuum
> > analyzed every night.
> >
> > One of the options that users have is to request a "movie" of up to
> > 20 frames; each of which consists of a background raster plus up to
> > about five circular geometries, about five points and maybe ten
> > other lines/polygons.
> >
> > The amount of geometry involved is so small, the bottleneck has to
> > be the raster processing. The images are 2m/pixel and are typically
> > 20 - 150 Mb each. The frame extents include most if not all of an
> > image so tiling does not seem relevant.
> >
> > I have specified 80% JPEG as the output to minimise final image
> > size and this seems successful (at least in terms of size and hence
> > download time)
> >
> > I am looking for ways to reduce the time taken in the image
> > generation phase.
> >
> > TIA,
> > Stephen Davies
> >
> > ===================================================================
> >===== This email is for the person(s) identified above, and is
> > confidential to the sender and the person(s).  No one else is
> > authorised to use or disseminate this email or its contents.
> >
> > Stephen Davies Consulting                            Voice: 08-8177
> > 1595 Adelaide, South Australia.                             Fax:
> > 08-8177 0133 Computing & Network solutions.                      
> > Mobile:0403 0405 83

-- 
 =======================================================================
This email is for the person(s) identified above, and is confidential to
the sender and the person(s).  No one else is authorised to use or
disseminate this email or its contents.

Stephen Davies Consulting                            Voice: 08-8177 1595
Adelaide, South Australia.                             Fax: 08-8177 0133
Computing & Network solutions.                       Mobile:0403 0405 83