[postgis-users] newby performance load to mapserver compared toshapefile

[Gregory Williamson]

Francesco --

It is possible, perhaps, that shapefiles are really that much faster, depending on how you are using the data.

It would help others help you if you could post some more details on specific postGIS/GEOS version, the layout of the tables and indexing of them. Perhaps a little info about the application (are you doing joins against the data, how much of the data is being requested, etc.).

When did you last analyze the tables ?  You need to do this after very major update or postgres does not "know" the true size of the table, which can lead to very bad plans (sequential scans, etc. would be used where an index might be faster if the planner thinks a table is tiny when it is fact large, etc.).

Try to capture the SQL that MMS is producing (you may need to boost the verbosity of postgres messages, or turn it on in MMS) and then from a psql prompt, or from PgAdmin if you use such a thing, try running

EXPLAIN ANALYZE <sql here>;

Then post the results, the query and the table details ... it is possible that shapefiles are faster but I'd be surprised at a 20x difference, so it is likely that some DBA work will produce improvements.

Sorry for top-posting -- using a mailer that doesn't do quoting, etc.

Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net on behalf of francesco.pirotti at unipd.it
Sent: Wed 7/18/2007 12:49 AM
To: PostGIS Users Discussion
Subject: [postgis-users] newby performance load to mapserver compared toshapefile
 
Dear Users,

I have been flirting with PostGIS data for a while, but now I have come
accross a benchmark issue which baffles me.  I loaded a big-bunch of
polygon data (431094 lines in postgres8.1) importing with shp2pgsql
utility (thus with GIST index and all)... I run the vacuum analyze on the
database, but the time mapserver takes to draw all the data is about 20
times slower than a shapfile.

Is this normal?

Thak you for your time.

Francesco Pirotti


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070718/ee11d63a/attachment.html>