Обсуждение: ERROR: Memory exhausted in AllocSetAlloc(188)

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)

Is this a view?

On Wed, 14 May 2003, Tilo Schwarz wrote:

> 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)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>