Slow postGIS data query (was RE: [postgis-users] mapserver dbconnection)

Wed Oct 26 09:10:15 PDT 2005

So it seems we've come full circle. For pulling all the data out of the
table for such detailed boundaries, it is much slower than simply calling
the shape file. And there's not much to improve that.

We've just started working on connecting PostgreSQL to Mapserver so your
help is definitely appreciated. We will definitely be doing different types
of queries that will not give us such poor performance in the future. I'll
look into the simplify algorithm for the large scale viewing (which is what
I was testing on) in the near future.

Again, thank you so much for your help.

Frances

-----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: Tuesday, October 25, 2005 3:34 PM
To: PostGIS Users Discussion; PostGIS Users Discussion
Subject: Slow postGIS data query (was RE: [postgis-users] mapserver
dbconnection)

Doesn't suggest much to do -- it looks as if this table has 50 rows; there
is no index used because there spatial operator "&&" (a bounding box
overlap) is not used.

There are only 50 rows according to the stats, but they are huge
(width=978687) so some slowness is simply from pulling 50 megs of data for
the query.

50 ... country boundaries that are detailed ? Performance for retrieving a
portion of the data would possibly be improved by using a && query (see the
postGIS manual for some examples using Mapserver). 

But if you are needing to pull all the data for every query I'm not sure
what would help ... the RAM is enough that unless other apps are demanding
large amounts, the data set and indexes should be cached in RAM and not
require disk operations.

It may be that you want two data sets -- one rough for large scale viewing
and one detailed for finer work? There is simplify algorithm in postGIS that
might help do this (but be sure to have a safe copy of data at hand!).

I've renamed this thread since it seems as if the connection itself is not a
main issue in the overall performance.

Greg

-----Original Message-----
From:	postgis-users-bounces at postgis.refractions.net on behalf of Frances
Collier
Sent:	Tue 10/25/2005 2:06 PM
To:	'PostGIS Users Discussion'
Cc:	
Subject:	RE: [postgis-users] mapserver db connection
Greg,

Ok, PostgreSQL is installed on Microsoft Windows Server 2003, Web Edition.
Mapserver is installed on the same machine. The hardware is an AMD Athlon
64X2 Dual, Core Processor 4400+, 2.21 GHz, 2 GB of RAM.

I ran "explain analyze select the_geom from europebackground;" and got "Seq
Scan on europebackground (cost=0.00..1.50 rows=50 width=978687)(actual
time=0.000..0.000 rows=50 loops=1)
Total runtime: 0.000 ms
(2 rows)" back. Does this help?

Thanks for the links. I'll read through them today to see what I can use.

Frances

-----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 4:28 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] mapserver db connection

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

_______________________________________________
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:435e9c82308556073319894!

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