[postgis-users] Another Question on PostGIS Performance

[Paul Ramsey]

You told us everything except how much of that data your are rendering 
for your test timings -- rendering 100 things is alot different from 
rendering 100000 things. PostGIS *is* slower than raw shapefiles + 
shptree indexes in mapserver. This we know, as we have benchmarked it. 
However, it is not *alot* slower, for reasonable use cases. And the 
slowness is largely related to moving features from the db internal 
representation to the mapserver representation.  The more features 
(vertices) you render, the longer that conversion step will take.  For 
retrieving and rendering a hundred or so features on arbitrarily sized 
tables, we found timing differences of less than 10% between the PostGIS 
and Shape+ShpTree.  For 100000 or so features, we found that 
Shape+ShpTree was as much as twice as fast as PostGIS.  If you are 
rendering 100000 features in creating a map, you must ask yourself -- 
why? You will probably get much nicer cartographic results using 
multiple layers of different scale and scale-dependance to switch from 
layer to layer as you zoom in.

P.

Thomas Haechler wrote:
> Hello,
> 
> I'm quite new to PostGIS. In my mapserver application, I have added a
> postgis polygon layer for testing. Since then, it takes about 15 secs to
> render the map on a Sun Ultra 60/300. This seems rather long and I would
> appreciate any help on how to make this faster.
> 
> Some "test" results:
> 
> 1.) standard map with 1 raster & 1 line layer (shapefile) : 2 secs
> 
> 2.) dito + the addidtional polygon layer from shapefile : 7 secs
> 
> 3.) dito + DATA "the_geom from veg": 13 secs
> 
> 4.) dito + 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": 15 secs
> 
> 5.) only postgis layer 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": 15 secs
> 
> Is it normal for this layer alone to take about double the time to
> render with postgis than from shapefile?
> Yes, I have a gist index on the_geom and an oid index on the map.gid and
> data.pg columns, and I did vacuum analyze.
> 
> 
> Thank you in advance,
> 
> 
> 


-- 
       __
      /
      | Paul Ramsey
      | Refractions Research
      | Email: pramsey at refractions.net
      | Phone: (250) 885-0632
      \_