Dear Postgresql gurus,
(somehow my first two send attempts of this message didn't get through ...
third 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)
