Thread: Postgres Out of Memory

Hello

When I run the following query, I have the error "Out of memory". I
believe that is memory problem, but before changing any parameter in
Postgres configuration files I remember that under ~data/pgsql_temp/
Temporary files used to appear when I ran a big query each temporary was
approximately about 1 gigabyte.

Here is my query

SELECT proceso, id_mail, to_mail, temp
FROM (
    SELECT proceso, id_mail, to_mail, count (*) AS temp
    FROM SENDMAIL2
    GROUP BY proceso, id_mail, to_mail
    HAVING proceso = 'Automatas'
     ) AS TAB_TEMP
HAVING temp != 1

The output of Explain is here

QUERY PLAN
Subquery Scan tab_temp  (cost=622577.68..622612.73 rows=2003 width=118)
  ->  HashAggregate  (cost=622577.68..622592.70 rows=2003 width=110)
        Filter: (count(*) <> 1)
        ->  Seq Scan on sendmail2  (cost=0.00..622327.31 rows=20029
width=110)
              Filter: ((proceso)::text = 'Automatas'::text)



My /etc/system file is this
set shmsys:shminfo_shmmax = 1073741824
set shmsys:shminfo_shmmin = 1
set shmsys:shminfo_shmmni = 100
set semsys:seminfo_semmni = 100
set shmsys:shminfo_shmseg = 10
set semsys:seminfo_semmsl = 100
set semsys:seminfo_semmns = 2500

set semsys:seminfo_semopm=10
set semsys:seminfo_semaem=16384
set semsys:seminfo_semume=10
set semsys:seminfo_semmap=1
set semsys:seminfo_semusz=96
set semsys:seminfo_semmnu=30
set semsys:seminfo_semvmx=32767
forceload: sys/semsys
forceload: sys/shmsys


My postgresql.conf is this
shared_buffers = 50000
work_mem = 2097151
maintenance_work_mem = 2097151



Please help me!!!

Thanks in advanced

--
If I have seen further it is by standing on the shoulders of the giants
Isaac Newton

Hi,
I think the problem is with "HashAggregate  (cost=622577.68..622592.70
rows=2003 width=110)"
Have you done VACUUM ANALYZE soon?
If it still don't work try turnig hash agregate off : SET enable_hashagg
= OFF

I think this will solve your problem. I have the same problem a month or
two ago.

Good Luck

Kaloyan


Eduardo Vбzquez Rodrнguez wrote:

> Hello
>
> When I run the following query, I have the error "Out of memory". I
> believe that is memory problem, but before changing any parameter in
> Postgres configuration files I remember that under
> ~data/pgsql_temp/    Temporary files used to appear when I ran a big
> query each temporary was approximately about 1 gigabyte.
>
> Here is my query
>
> SELECT proceso, id_mail, to_mail, temp
> FROM (
>    SELECT proceso, id_mail, to_mail, count (*) AS temp
>    FROM SENDMAIL2
>    GROUP BY proceso, id_mail, to_mail
>    HAVING proceso = 'Automatas'
>     ) AS TAB_TEMP
> HAVING temp != 1
>
> The output of Explain is here
>
> QUERY PLAN
> Subquery Scan tab_temp  (cost=622577.68..622612.73 rows=2003 width=118)
>  ->  HashAggregate  (cost=622577.68..622592.70 rows=2003 width=110)
>        Filter: (count(*) <> 1)
>        ->  Seq Scan on sendmail2  (cost=0.00..622327.31 rows=20029
> width=110)
>              Filter: ((proceso)::text = 'Automatas'::text)
>
>
>
> My /etc/system file is this
> set shmsys:shminfo_shmmax = 1073741824
> set shmsys:shminfo_shmmin = 1
> set shmsys:shminfo_shmmni = 100
> set semsys:seminfo_semmni = 100
> set shmsys:shminfo_shmseg = 10
> set semsys:seminfo_semmsl = 100
> set semsys:seminfo_semmns = 2500
>
> set semsys:seminfo_semopm=10
> set semsys:seminfo_semaem=16384
> set semsys:seminfo_semume=10
> set semsys:seminfo_semmap=1
> set semsys:seminfo_semusz=96
> set semsys:seminfo_semmnu=30
> set semsys:seminfo_semvmx=32767
> forceload: sys/semsys
> forceload: sys/shmsys
>
>
> My postgresql.conf is this
> shared_buffers = 50000
> work_mem = 2097151
> maintenance_work_mem = 2097151
>
>
>
> Please help me!!!
>
> Thanks in advanced
>