Performance problem with drawing several layers using Postgis

[Andre Karp]

Alex and Norbert,

thank you very much for your advice. Maybe the "toasted" rows problem is
mine, but I found in the meantime another way to organize and query my
data - by using variable substitution in the data string -, so I could drop
13 of my 14 layers, so its fast enough now. But nevertheless, thank you very
much, I guess I will have to build a system with a couple of layers from
postgre sooner or later, so then I will try your advice

Regards, Andre


----- Original Message ----- 
From: "Alexander Mayrhofer" <axelm-mapserver at nona.net>
To: "Andre Karp" <karp at MSP-DORTMUND.DE>
Cc: <MAPSERVER-USERS at LISTS.UMN.EDU>
Sent: Wednesday, November 02, 2005 3:16 PM
Subject: Re: [UMN_MAPSERVER-USERS] Performance problem with drawing several
layers using Postgis


> Andre Karp wrote:
> > thank you for your reply. I also tried the postgis-connection with the
> > PROCESSING "CLOSE_CONNECTION=DEFER" option, unfortunatly with no
significant
> > improvement.
> > I have not indexed my classification coloums yet, since finishing a map
> > request via postgis-connection took already 5 seconds without any
> > filtering/classifying which is far to slow for my purpose. So the
problem
> > cannot be the filtering/classifying, but must have something to do with
my
> > postgis-connection, I think.
>
> Andre,
>
> perhaps a look on what your box does while rendering would be useful. run
> "top" in one window, "vmstat 1" (or, preferred, "iostat 1") in another
> window, an look where the bottleneck is.
>
> If postgres is doing a lot of I/O you might want to check you Postgres
> buffer memory settings, make sure that all relevant columns are indexed
> (many distributions have very conservative buffer memory settings, which
> leads to lots of disk i/o). I've seen a lot of improvement when moving to
> Postgres8.1 (beta) since this version is much more efficient in terms of
> page caching and index usage - but that's probably overkill if you are not
> going to upgrade anyway.
>
> There is a problem with the postgres query planner with so called
"toasted"
> rows which affects tables with only a few but large geometries, read
> http://postgis.refractions.net/docs/ch05.html how to potentially avoid
this.
>
> (Btw, you did "VACUUM ANALYZE" the tables after loading the shapes?)
>
> If that does not help, turn on statement and planner logging in postgres,
> and examine each query with "EXPLAIN .." (that could also indicate if your
> tables suffer the "toast" problem).
>
> You could also try to remove unneccessary attribute columns to reduce
record
> size.
>
> hope that helps
>
> Alex Mayrhofer
>