Thread: ERROR: Memory exhausted in AllocSetAlloc(188)

ERROR: Memory exhausted in AllocSetAlloc(188)

From
Tilo Schwarz (by way of Tilo Schwarz
Date:
Dear Postgresql gurus,

(somehow my first send attempt of this message didn't get through ... second
try)

I have a problem (7.3.1 on linux) with a query eating all my memory. First it
takes 2G RAM and then 2G swap. Now I saw, that 7.3.2 addresses some memory
issues, could my problem solved by upgrading to 7.3.2?

Thanks a lot, description follows

    Tilo

(the query involves a table with 33925848 rows, but only a few thousand rows
should be returned)


tschwarz=# explain select * from feature_point_delta_avg where sequence_id
=325058;
QUERY PLAN
-----------------------------------------------------------------------------
--- --------------------------
Subquery Scan feature_point_delta_avg  (cost=1541301.27..1551163.80
rows=43833 width=28)
->  Aggregate  (cost=1541301.27..1551163.80 rows=43833 width=28)
->  Group  (cost=1541301.27..1544588.78 rows=438334 width=28)
->  Sort  (cost=1541301.27..1542397.11 rows=438334
width=28)
Sort Key: trace.sequence_id, trace.trace_id
->  Hash Join  (cost=3424.87..1488310.69 rows=438334
width=28)
Hash Cond: ("outer".image_id =
"inner".image_id)
->  Hash Join  (cost=2797.82..1383310.62
rows=13185846 width=20)
Hash Cond: ("outer".trace_id =
"inner".trace_id)
->  Seq Scan on d_kalman
(cost=0.00..1046060.48 rows=33925848 width=12)
->  Hash  (cost=2782.76..2782.76
rows=6025 width=8)
->  Seq Scan on trace
(cost=0.00..2782.76 rows=6025 width=8)
Filter: (sequence_id =
325058)
->  Hash  (cost=624.26..624.26 rows=1111
width=8)
->  Seq Scan on label_data ld
(cost=0.00..624.26 rows=1111 width=8)
Filter: ((right_eye IS NULL) AND
(visible_features(ld.*) > 0))
(16 rows)

Time: 80.24 ms
tschwarz=# select * from feature_point_delta_avg where sequence_id
=325058;

[... waiting for ca. 10 minutes ...]

ERROR:  Memory exhausted in AllocSetAlloc(188)
tschwarz=# show sort_mem ;
sort_mem
----------
16384
(1 row)