<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>RE: [postgis-users] newby performance load to mapserver compared toshapefile</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Francesco --<BR>
<BR>
It is possible, perhaps, that shapefiles are really that much faster, depending on how you are using the data.<BR>
<BR>
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.).<BR>
<BR>
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.).<BR>
<BR>
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<BR>
<BR>
EXPLAIN ANALYZE <sql here>;<BR>
<BR>
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.<BR>
<BR>
Sorry for top-posting -- using a mailer that doesn't do quoting, etc.<BR>
<BR>
Greg Williamson<BR>
Senior DBA<BR>
GlobeXplorer LLC, a DigitalGlobe company<BR>
<BR>
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.<BR>
<BR>
(My corporate masters made me say this.)<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of francesco.pirotti@unipd.it<BR>
Sent: Wed 7/18/2007 12:49 AM<BR>
To: PostGIS Users Discussion<BR>
Subject: [postgis-users] newby performance load to mapserver compared toshapefile<BR>
<BR>
Dear Users,<BR>
<BR>
I have been flirting with PostGIS data for a while, but now I have come<BR>
accross a benchmark issue which baffles me. I loaded a big-bunch of<BR>
polygon data (431094 lines in postgres8.1) importing with shp2pgsql<BR>
utility (thus with GIST index and all)... I run the vacuum analyze on the<BR>
database, but the time mapserver takes to draw all the data is about 20<BR>
times slower than a shapfile.<BR>
<BR>
Is this normal?<BR>
<BR>
Thak you for your time.<BR>
<BR>
Francesco Pirotti<BR>
<BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>