Optimizing PostGIS.

[Eijnden, B. van den (Bart)]

You won't get it to be as fast as shapefiles, whatever you try. 

Some thoughts though:
-you could try Mapserver FastCGI but it won't bring you much since
connection overhead is low
-did you give PostgreSQL more than the default 64 Mb of memory to use?

For point query performance, did you create an index on the unique key? If
you did not specify USING UNIQUE in your map file, then Mapserver
automatically uses oid. Make sure it is indexed. If you use a different
unique column, make sure it is indexed as well.

Best regards,
Bart

-----Oorspronkelijk bericht-----
Van: UMN MapServer Users List
[mailto:MAPSERVER-USERS at LISTS.UMN.EDU]Namens Luca Manganelli
Verzonden: woensdag 12 april 2006 11:46
Aan: MAPSERVER-USERS at LISTS.UMN.EDU
Onderwerp: [UMN_MAPSERVER-USERS] Optimizing PostGIS.


Hi,

I'm trying to optimizie a postgis table.

I did "shp2pgsql" command to convert a large shapefile (17 MB, over
5000 features) then I imported it into postgres 8.1.
I created spatial index and I did VACUUM ANALYZE on that table.
I followed the steps in "Performance tips" on Postgis Documentation at
http://postgis.refractions.net/docs/ch05.html (expecially in 5.2 and
5.3).

BUT, in my mapserver the shapefile drawn at max extent is somewhat
50%-100% faster
than Postgis. That performance gain reduces to a 10%-30% in lower scales.
If I do a point query on Postgis table, it is not much faster (but the
performances are often equals!!)...

Any idea?

--
"Work only as many hours as you can be productive and only as many hours as
 you can sustain."  - Kent Beck, in "Extreme Programming Explained"

http://www.trapanator.com/blog


Disclaimer
************************************************************************
Aan dit bericht kunnen geen rechten worden ontleend. Dit bericht is
uitsluitend bestemd voor de geadresseerde. Als u dit bericht per abuis
hebt ontvangen, wordt u verzocht het te vernietigen en de afzender te 
informeren. Wij adviseren u om bij twijfel over de juistheid of de 
volledigheid van de mail contact met afzender op te nemen.

This message shall not constitute any rights or obligations.
This message is intended solely for the addressee.
If you have received this message in error, please delete it and
notify the sender immediately. When in doubt whether this message
is correct or complete, please contact the sender.
************************************************************************