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

[francesco.pirotti at unipd.it]

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: 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>