[postgis-users] Memory problem with ST_Within

[Birgit Laggner]

Hallo Regina, hallo Frank,

thanks for your suggestions!

Leaving out the_geom didn't solve the problem - I still ran out of 
memory. I attached my postgresql.conf settings and the result of the 
EXPLAIN ANALYSE SELECT to this e-mail.

Would it be an option to work without the gist?

Thanks again,

Birgit.



Obe, Regina schrieb:
> Birgit,
>
> Adding to Franks point
>
> It seems you are adding the_geom to your list since you need that as part of your result. You may want to leave it out of your list (in the select and group by)  and then join back with the polygon_id field to get it back once you are done.
>
> But still that is a large set you are tackling with.  What do your postgresql.conf settings look like?
>
> Sadly you may just have to partition your dataset into quadrants and then group each quadrant separately and union the results.
>
> It would help to see an 
>
> EXPLAIN ANALYZE SELECT ....
>
> of  a smaller subset to see what it is doing.
>
> You also of course want to make sure you have 
>
> vacuum analyze bfn.ni_hoehendaten;
> vacuum analyze bfn.ni_polygone2;
>
> before you get started.  A recently loaded non-vacuumed set of data does yield very poor performance.
>
> Hope that helps,
> Regina
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Frank Koormann
> Sent: Wednesday, August 20, 2008 11:50 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Memory problem with ST_Within
>
> * Birgit Laggner <birgit.laggner at vti.bund.de> [080820 17:30]:
>   
>> Here is my query:
>>
>> SELECT
>> polygon_id,
>> count(hoehe) as hoehe_count,
>> min(hoehe) as hoehe_min,
>> max(hoehe) as hoehe_max,
>> avg(hoehe) as hoehe_avg,
>> count(neigung) as neig_count,
>> min(neigung) as neig_min,
>> max(neigung) as neig_max,
>> avg(neigung) as neig_avg,
>> count(exposition) as expos_count,
>> min(exposition) as expos_min,
>> max(exposition) as expos_max,
>> avg(exposition) as expos_avg,
>> p.the_geom
>> FROM bfn.ni_hoehendaten h, bfn.ni_polygone2 p
>> WHERE ST_Within(h.the_geom, p.the_geom)
>> GROUP BY p.polygon_id, p.the_geom
>> ORDER BY p.polygon_id ASC;
>>
>> Does anybody know a method to reduce the memory usage? Or are there other 
>> suggestions how this problem could be solved?
>>     
>  
> Why do you group by p.the_geom? Just a wild guess that this may cause
> your memory problems. 
>
> HTH,
>
>         Frank
>
>   
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080821/cda075de/attachment.html>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080821/cda075de/attachment-0001.html>