[postgis-users] Another Question on PostGIS Performance

[Graeme Leeming]

Thomas,

Based on the table info you have provided, it appears that you have *not*
created an index on pg in the veg table.  You'll need to add a third index
to it of the form "CREATE INDEX veg_pg_index ON veg ( pg );" as done to the
veg_atr table.  After another VACUUM ANALYZE see if that makes any
difference...

- Graeme

Thomas Haechler wrote:

> Hi Dave,
>
> Thanks for your answer. But I think there is a missunderstanding. The pg
> column of the 2nd table is unique and there is an index on it (sorry
> it's called "pg", not my choice). This is how the tables look like:
>
> Table "veg"
> gid | the_geom | pg  | ... |
> ----+----------+-----+-----+
>    0| ...      |  12 | ... |
>    1| ...      |   9 | ... |
>    2| ...      |  40 | ... |
>  ...| ...      | ... | ... |
> 6590| ...      |  33 | ... |
>
> Indexes: veg_gid_index,
>          veg_gist_index
> Check constraints: "$1" (srid(the_geom) = 4149)
>                    "$2" ((geometrytype(the_geom) = 'MULTIPOLYGON'::text)
> OR (NULL::geometry = the_geom))
>
> --> CREATE INDEX veg_gist_index ON veg USING GIST ( the_geom
> GIST_GEOMETRY_OPS );
> --> CREATE INDEX veg_gid_index ON veg ( gid );
>
> Table "veg_atr"
> pg  | hs_zoller | ... |
> ----+-----------+-----+
>    1| descr1    | ... |
>    2| descr6    | ... |
>    3| descr4    | ... |
>  ...| descr..   | ... |
>   40| descr6    | ... |
>
> Indexes: veg_atr_pg_index
>
> --> CREATE INDEX veg_atr_pg_index ON veg_atr ( pg );
>
> And the query string was:
> DATA "the_geom from ( select map.the_geom, data.hs_zoller, map.gid from
> veg as map, veg_atr as data where map.pg = data.pg ) as foo using unique
> gid"
>
> Maybe the problem is the number of vertices like Paul said, since the
> sum of points is in fact 432574. If I add another condition like:
> DATA "the_geom from ( select map.the_geom, data.hs_zoller, map.gid from
> veg as map, veg_atr as data where map.pg = data.pg and data.pg<20) as
> foo using unique gid",
> the rendering time is about 9 secs and the sum of points is only 175772.
>
> Regards,
>
> Thomas
>
> --
>
> THOMAS HAECHLER
>
> Geographic Information Systems Division
> Institute of Geography
> University of Zurich
> Switzerland
>
> thomas.haechler at geo.unizh.ch
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users