WHERE ST_Intersects causes VM to balloon

[darkblueb]

a particular query causes allocations of VM that eventually cause the process to be killed, before completion. The same query on the same data on another host does not show this behavior. Test query, sample data and machine descriptions are enclosed.

One workaround is to use

 ... WHERE  a.wkb_geometry && b.wkb_geometry;

instead, but that causes NULL and POINT results along with the desired intersection results to be generated. Another workaround may be to setting RLIMIT_VMEM for Postgres under Linux/POSIX ?

CREATE TABLE lt_grid_intersect_marin_intersmethod         
 as                                                                                              
SELECT                                                                                          
   ST_Intersection(g.wkb_geometry, l.wkb_geometry) as wkb_geometry,                        
        id_grid,                                                                                
        landtype,                                                                               
        fmmp_class                                                                              
FROM grid150m_marin_subset as g, lt_marin_subset as l
WHERE ST_Intersects(g.wkb_geometry, l.wkb_geometry);

[darkblueb]

data set at http light42 DOT com SLASH m_data.tgz

[darkblueb]

downgrading GEOS to 3.3.3 and rebuilding PostGIS 1.5.4

resulted in a successfully completed query, using fairly large amounts of resident RAM and VM, but not "blowing up"

[strk]

Ouch, _same_ postgresql version, you say ? This is pretty annoying, I was pretty sure GEOS 3.4.0SVN was having a better memory use than 3.3.4 !!

[strk]

I'm looking at a memory killer Buffer input right now. It's a monster MULTIPOLYGON containing +7000 tiny polygons…

[strk]

Can you reproduce the "crazy memory" with this query:

SELECT id_grid FROM grid150m_marin_subset as g, lt_marin_subset as l
WHERE ST_Intersects(g.wkb_geometry, l.wkb_geometry);

[darkblueb]

that query also blows up by using all 16G of available VM on this 8G RAM, 8G swap 'Precise' box

PostgGIS 1.5.4 built on GEOS 3.3.3, Pg 9.1

[strk]

Uhm, in comment 2 you mentioned "downgrading to GEOS 3.3.3 fixed it" ? So you're now saying that both with 3.3.3 and 3.4.0SVN you have the problem ?

[darkblueb]

I have some diverse hardware setup - it now appears that the new GEOS versions may be exhibiting this bug under PostGIS 1.5x and not under PostGIS 2.0.. I wasnt controlling for that in the comments above.. so I will re-run as I am able..