[postgis-users] mapserver db connection

[Gregory S. Williamson]

Frances --

> Greg,
> 
> I just ran the same queries through pgAdmin III -- I'm assuming that's what
> you meant by natively -- and the queries took just as long. I'm not sure
> what you mean by the "out-of-the-box" installation. I installed postgres
> 8.0.4 with the basic Win32 installation. 
> 
  You might post details of your RAM and so on, but in general postgres comes with a number of tunable configuration settings which allow you to descibe how much memory to allocate for each connection to use for sorting (if it runs out of that space postgres goes to disk, which is of course slow); you can also provide hints to the planner about how fast disk access is what sort of caching to expect; there are some good general descriptions at <http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html> ... I haven't used 8.0 on a windows box for anything except lightweight test cases so I am not sure exactly what you want to do -- others might have more concrete suggestions ?

  A few I know of that might be useful include work_mem (this is alloted to each connection and defines how much RAM the connection gets, I think), effective_cache_size (a hint, I think to the planner), as is random_page_cost.

You might also run your query but preface it with "EXPLAIN ANALYZE ...." and then post the results -- sometimes its output can provide the necessary insight. See <http://www.postgresql.org/docs/8.0/interactive/performance-tips.html> for some more on that.

> The tables are all indexed using gist and recently analyzed. I have not read
> anything about geometry stats. Is that another version? 
> 
  The earlier versions of postGIs used a seperate command to gather statistics -- in the 1.x series this is done by the "analyze" command, so it sounds like that's ok.

> Also, what is the fastCGI? I'm still learning about all this stuff and
> obviously have much more to learn!

  I've not used it but check the MMS archives for information about fast cgi -- in some environments it can make a real difference. Sorry I can't provide more insight there.

> Thank you for your response,

NP

G
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Gregory
S. Williamson
Sent: Monday, October 24, 2005 2:03 PM
To: PostGIS Users Discussion; postgis-users at postgis.refractions.net
Subject: RE: [postgis-users] mapserver db connection

Frances --

Have you tried running the same queries in postgres natively -- it may be
that postgres is not giving you all that it can, especially if this is an
"out-of-the-box" installation, which is set to allow low end machines to
run, but definitely is not tuned for performance in terms of memory settings
and the like.

Also, are the tables indexed and recently analyzed/had geometry stats done
on them ?

Connecting to postgres can be relatively slow -- I think the fastCGI variant
of MMS may handle this better.

And of course, it *could* be inherent in MMS -- I have read that on this
list that shape files are faster for some purposes.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From:	postgis-users-bounces at postgis.refractions.net on behalf of Frances
Collier
Sent:	Mon 10/24/2005 1:29 PM
To:	postgis-users at postgis.refractions.net
Cc:	
Subject:	[postgis-users] mapserver db connection
Hello,

 

I've been trying to find out what exactly Mapserver gets back from Postgis
when querying the tables. I haven't found this to be documented anywhere.
Does Postgis return a shape file, layer, or simply data rows? The reason I'm
asking is that I noticed a huge time difference between connecting to
Postgis and simply calling the shape files - with the Postgis being
significantly slower. I know Postgis offers a lot more functionality, but
for simple comparisons I do not have any type of filter applied.

 

I'd appreciate any knowledge, advice, and/or direction.

 

Thank you,

Frances Collier

 




_______________________________________________
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

!DSPAM:435d531883811335720792!