[postgis-users] Overlap Queries - speed

[Obe, Regina DND\MIS]

For the work_mem how high have you increased it and for each change you need
to restart postmaster or pg_ctl for the work_mem change to take.

It usually makes a big difference for me.  I usually run with at least
131072 (128 MB), but then that is when I have 1-2gig on on board ram.

I would try boosting yours to 64 MB - 65536  and see if that makes a
difference.



-----Original Message-----
From: rb [mailto:rburghol at chesapeakebay.net] 
Sent: Tuesday, October 04, 2005 8:10 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Overlap Queries - speed


Everyone,
Thanks so much for all the suggestions. Of course, I have some questions,
such as:

> The only important thing is to add bounding boxes in the fields.

How do I do this?

>  Another check?  How much memory do you have allocated for worker_mem?
I have allocated 8192 for work_mem in postgresql.conf. Any suggestions?

I have been playing with those parameters a bit, and to be honest, I don't
understand them particularly well, and seem to no be able to make much
headway with them, that is, I don't notice any improvement.

Thanks again,
r.b.

On Mon, 2005-10-03 at 19:59, challu at fas.harvard.edu wrote:
> >From what I gathered from the documentation (postgis.pdf), && tests 
> >overlapping
> of bounding boxes. If my understanding is correct, then, it doesn't 
> matter the actual shape of the forms. The only important thing is to 
> add bounding boxes in the fields. That was in fact my experience: a 
> similar query (although perhaps with fewer cases) took too long to 
> perform. I didn't matter the two geometry fields were indexed. What 
> made the difference was redoing the geometry field with a bounding 
> box. After that the query took a couple of seconds, not 10 minutes and 
> it sped up considerably other spatial operations such as contains(). I 
> hope this helps... Amilcar
> 
> 
> Quoting Robert Burgholzer <rburghol at chesapeakebay.net>:
> 
> > I have a question regarding performance, running postGIS 1.0.4 on 
> > postgreSQL 8.0.3. running on RedHat Enterprise ES, 1.113 GHz Inter 
> > Pentium III, 376 Megs of RAM
> >
> > I am performing an intersection calculation on two spatial tables, 
> > the tables are as follows:
> >
> > bmp_multiseg : 6579 records
> > p5lrsegs : 2364 records
> >
> > QUERY:
> >    select a.msid, b.lrseg, b.landseg, b.riverseg,
> >    area2d(intersection(a.the_geom, b.the_geom))/area2d(b.the_geom) from
> >    bmp_multiseg as a, p5lrsegs as b where a.the_geom && b.the_geom;
> >
> > basically, there are several inputs in bmp_multiseg at a number of 
> > overlapping resolutions that I am distributing amongst the second 
> > table p5lrsegs, with an average of ~ 6 overlaps from bmp_multiseg to 
> > p5lrsegs, making a total of ~ 15,000 records in the resulting query. 
> > This query takes over an hour to complete with spatial indices on 
> > both tables geometry field.
> >
> > The geometries are not simple, they range in size from between 5-20K 
> > per row, perhaps this is part of the problem?
> >
> > Any insight would be appreciated. Even if you tell me that this is 
> > reasonable and I have to just suck it up, or look for another tool 
> > to do my calculations, those kinds of insights are good to know.
> >
> > Thanks,
> > r.b.
> >
> >
> > --
> > Non-point Source Data Analyst
> > University of Maryland, College Park
> > Chesapeake Bay Program Office
> > 410 Severn Avenue, Suite 305B
> > Annapolis, MD, 21403
> > Phone: (410) 267-5779
> >
> > rburghol at chesapeakebay.net
> >
> > _______________________________________________
> > 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
-- 
Non-point Source Data Analyst
University of Maryland, College Park
Chesapeake Bay Program Office
410 Severn Avenue, Suite 305B
Annapolis, MD, 21403
Phone: (410) 267-5779

rburghol at chesapeakebay.net

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