[postgis-users] Another Question on PostGIS Performance

[david blasby]

I'm not sure if I'm correct here, but I think your performance problem is
based on a "loose" join between veg and veg_atr (pg is not unique).

Here's an example of what I mean.  Lets suppose you delete all but one row
from veg (so you only have 1 polygon).
when you do a query like:
    select veg.the_geom from veg, veg_atr where veg.pg = veg_atr.pg

You'll get about 160 rows (6591 rows / 40 unique pg values)!!!

Here's a more specific example:

dblasby=# select * from t;
 name | pg
------+----
 dave |  1
(1 row)

dblasby=# select * from u;
 pg | data
----+------
  1 |    1
  1 |    2
  1 |    3
  1 |    4
  2 |    1
  2 |    2
  2 |    3
  2 |    4
(8 rows)

dblasby=# select name from t,u where t.pg= u.pg;
 name
------
 dave
 dave
 dave
 dave
(4 rows)

So, it looks like you're drawing every feature 160 times (thats 72'000'000
points) - no wonder its slow!

Usually you join tables on a unique attribute - in your case you would
connect a unique column in veg (ie. gid) to another table that relates
gid->hs_zoller (or whatever attribute you're interested in).

Hope this help you look in the right direction.  The type of query you're
doing shouldnt take much more than a few seconds (depending on how many rows
you have in your geometry [veg] table).

dave
ps. I was asking how many rows were in your geometry table - but to get the
number of points you can
    SELECT sum(npoints(the_geom)) FROM veg;