Thank you Greg for your time, Here are the details you pointed out:
PostgreSQL 8.1.4 for Windows with the PostGIS included in the installation (POSTGIS="1.0.6" GEOS="2.2.1" PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS DBPROC="0.3.0" RELPROC="0.3.0"). MMS query for data is simple, no joins or links: "select the_geom from arch_geo.dusaf" (I left out the using unique clauses that mapserver needs... could that have an effect?) the EXPLAIN ANALYZE on that sql query results in the following string: "Seq Scan on dusaf (cost=0.00..36047.94 rows=431094 width=818) (actual time=0.566..5265.241 rows=431094 loops=1)" Total runtime: 5417.809 ms Takes more than 5 seconds though to show in my mapserver image, around 36 seconds... You can view this at the following link (very experimental): http://cartografia01.cogeme.com:81/cogeme/start.php?root=/cogeme/&program=/cgi-bin/mapserv.exe&comune=cogeme&mapname=cogeme Check the box on "DUSAF" voice. Cheers, Francesco Pirotti > 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: [EMAIL PROTECTED] on behalf of > [EMAIL PROTECTED] > 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
