[postgis-users] Re: Help: Performance of spatial query

[Jeremy Palmer]

On the machine I sent the log in from I have 384mb of RAM. But I have tried
the same query on a P4 3GHz CPU with 2gb of RAM and the same problem occurs.
Also I did try running the query without the distance function and the same
problem occurs. 
i.e 

SELECT  NOD1.id as nod_id1,
        NOD2.id as nod_id2
FROM    node NOD1,
        node NOD2
WHERE   NOD1.shape && expand(BOX3D(NOD2.shape),0.000002)
AND     NOD1.id < NOD2.id

Thanks Jeremy 

>> It maybe worth noting that I watched the postmaster process with TOP 
>> until the crash. What I noticed was that the memory usage gradually 
>> increased until it reached 95%, at which time postmaster then crashed.
>
>
>This means it almost certainly running out of memory.
>
>How much memory do you have in your machine?
>
>what happens if you remove the "  AND     distance(NOD1.shape, NOD2.shape)
<= 
>0.000002" from your query?
>
>dave