count with high allocation

Hello

2010/4/7 paulo matadr <saddoness@yahoo.com.br>:

Hi all,
Monitoring "top" in database server  , i could  noticed an query with
reserved  8GB on physical memory.

select count(field) from big_table  1 inner join big_table2...

There is the possibility of using another function with less memory
allocation?
Is there a way to limit the memory usage of the count?

probably you have unactualised statistic. What is result of EXPLAIN
ANALYZE select count(... ?

I think, so planner uses hash arrays

try to

set enable_hashagg to off;
and again SELECT ...

Regards
Pavel Stehule

On Wed, Apr 7, 2010 at 8:11 AM, paulo matadr <saddoness@yahoo.com.br> wrote:

Hi all,
Monitoring "top" in database server  , i could  noticed an query with
reserved  8GB on physical memory.

You are likely seeing the SHR and VIRT columns saying that. Lemme
guess, you've got 8G of shared memory allocated to pgsql?

If not, please show the lines from top that have you worried.

paulo matadr <saddoness@yahoo.com.br> writes:

Monitoring "top" in database server , i could noticed an query with reserved 8GB on physical memory.

select count(field) from big_table 1 inner join big_table2...

There is the possibility of using another function with less memory allocation?
Is there a way to limit the memory usage of the count?

It seems quite likely that what top is telling you just reflects the
process touching all shared buffers, and has nothing to do with any
real "memory consumption". What do you have shared_buffers set to?

regards, tom lane

EXPLAIN/TOP

EXPLAIN:
Aggregate (cost=3710076.58..3710076.59 rows=1 width=4)
-> Hash Join (cost=2668820.36..3692200.16 rows=7150570 width=4)
Hash Cond: (osunidade.attp_id = art.attp_id)
-> Hash Join (cost=2668819.29..3593878.75 rows=7150570 width=8)
Hash Cond: (osunidade.orse_id = os.orse_id)
-> Seq Scan on ordem_servico_unidade osunidade (cost=0.00..429514.00 rows=23418900 width=8)
-> Hash (cost=2598702.48..2598702.48 rows=4033665 width=4)
-> Hash Left Join (cost=1372486.83..2598702.48 rows=4033665 width=4)
Hash Cond: (os.cbdo_id = cobra.cbdo_id)
-> Merge Join (cost=0.00..880392.67 rows=4033665 width=8)
Merge Cond: (os.rgat_id = ra.rgat_id)
-> Index Scan using xfk1_ordem_servico on ordem_servico os (cost=0.00..879051.89 rows=13210693 width=12)
-> Index Scan using registro_atendimento_pkey on registro_atendimento ra (cost=0.00..548171.12 rows=5369913 width=4)
Filter: (rgat_id IS NOT NULL)
-> Hash (cost=897238.26..897238.26 rows=27340126 width=4)
-> Seq Scan on cobranca_documento cobra (cost=0.00..897238.26 rows=27340126 width=4)
-> Hash (cost=1.03..1.03 rows=3 width=4)
-> Seq Scan on atendimento_relacao_tipo art (cost=0.00..1.03 rows=3 width=4)

"TOP"
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

26809 postgres 16 0 16.5g 8.6g 8.6g S 1 27.5 1:28.84 postgres: user database 10.1.1.7(54033) SELECT

________________________________
De: Tom Lane <tgl@sss.pgh.pa.us>
Para: paulo matadr <saddoness@yahoo.com.br>
Cc: GENERAL <pgsql-general@postgresql.org>
Enviadas: Quarta-feira, 7 de Abril de 2010 11:31:27
Assunto: Re: [GENERAL] count with high allocation

paulo matadr <saddoness@yahoo.com.br> writes:

Monitoring "top" in database server , i could noticed an query with reserved 8GB on physical memory.

select count(field) from big_table 1 inner join big_table2...

There is the possibility of using another function with less memory allocation?
Is there a way to limit the memory usage of the count?

It seems quite likely that what top is telling you just reflects the
process touching all shared buffers, and has nothing to do with any
real "memory consumption". What do you have shared_buffers set to?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

____________________________________________________________________________________
Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com

please, EXPLAIN ANALYZE

and try to execute

set enable_hashagg to off before as second variant. It have to take less memory

regards
Pavel Stehule

2010/4/7 paulo matadr <saddoness@yahoo.com.br>:

with
set enable_hashagg to off , I give the same allocation.

________________________________
De: Pavel Stehule <pavel.stehule@gmail.com>
Para: paulo matadr <saddoness@yahoo.com.br>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; GENERAL <pgsql-general@postgresql.org>
Enviadas: Quarta-feira, 7 de Abril de 2010 12:10:23
Assunto: Re: [GENERAL] count with high allocation

please, EXPLAIN ANALYZE

and try to execute

set enable_hashagg to off before as second variant. It have to take less memory

regards
Pavel Stehule

2010/4/7 paulo matadr <saddoness@yahoo.com.br>:

On Wed, Apr 7, 2010 at 10:41 AM, paulo matadr <saddoness@yahoo.com.br> wrote:

with
set enable_hashagg to off , I give the same allocation.

So what is your setting for shared_buffers? Cause this looks pretty
normal to me.

2010/4/7 paulo matadr <saddoness@yahoo.com.br>:

with
set enable_hashagg to off , I give the same allocation.

ok, then problem will be other.

what is result of:

show shared_buffers;
show work_mem;

Regards
Pavel Stehule

postgres=# show shared_buffers;
shared_buffers
----------------
16GB
(1 row)

postgres=# show work_mem;
work_mem
----------
5MB
(1 row)

________________________________
De: Pavel Stehule <pavel.stehule@gmail.com>
Para: paulo matadr <saddoness@yahoo.com.br>
Cc: pgsql-general@postgresql.org
Enviadas: Quarta-feira, 7 de Abril de 2010 13:51:24
Assunto: Re: [GENERAL] count with high allocation

2010/4/7 paulo matadr <saddoness@yahoo.com.br>:

with
set enable_hashagg to off , I give the same allocation.

ok, then problem will be other.

what is result of:

show shared_buffers;
show work_mem;

Regards
Pavel Stehule

________________________________
De: Pavel Stehule <pavel.stehule@gmail.com>
Para: paulo matadr <saddoness@yahoo.com.br>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; GENERAL <pgsql-general@postgresql.org>
Enviadas: Quarta-feira, 7 de Abril de 2010 12:10:23
Assunto: Re: [GENERAL] count with high allocation

please, EXPLAIN ANALYZE

and try to execute

set enable_hashagg to off before as second variant. It have to take less
memory

regards
Pavel Stehule

2010/4/7 paulo matadr <saddoness@yahoo.com.br>:

EXPLAIN/TOP

EXPLAIN:
Aggregate (cost=3710076.58..3710076.59 rows=1 width=4)
-> Hash Join (cost=2668820.36..3692200.16 rows=7150570 width=4)
Hash Cond: (osunidade.attp_id = art.attp_id)
-> Hash Join (cost=2668819.29..3593878.75 rows=7150570 width=8)
Hash Cond: (osunidade.orse_id = os.orse_id)
-> Seq Scan on ordem_servico_unidade osunidade
(cost=0.00..429514.00 rows=23418900 width=8)
-> Hash (cost=2598702.48..2598702.48 rows=4033665 width=4)
-> Hash Left Join (cost=1372486.83..2598702.48
rows=4033665 width=4)
Hash Cond: (os.cbdo_id = cobra.cbdo_id)
-> Merge Join (cost=0.00..880392.67
rows=4033665
width=8)
Merge Cond: (os.rgat_id = ra.rgat_id)
-> Index Scan using xfk1_ordem_servico on
ordem_servico os (cost=0.00..879051.89 rows=13210693 width=12)
-> Index Scan using
registro_atendimento_pkey on registro_atendimento ra
(cost=0.00..548171.12
rows=5369913 width=4)
Filter: (rgat_id IS NOT NULL)
-> Hash (cost=897238.26..897238.26
rows=27340126
width=4)
-> Seq Scan on cobranca_documento cobra
(cost=0.00..897238.26 rows=27340126 width=4)
-> Hash (cost=1.03..1.03 rows=3 width=4)
-> Seq Scan on atendimento_relacao_tipo art
(cost=0.00..1.03
rows=3 width=4)

"TOP"
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

26809 postgres 16 0 16.5g 8.6g 8.6g S 1 27.5 1:28.84 postgres:
user
database 10.1.1.7(54033) SELECT
________________________________
De: Tom Lane <tgl@sss.pgh.pa.us>
Para: paulo matadr <saddoness@yahoo.com.br>
Cc: GENERAL <pgsql-general@postgresql.org>
Enviadas: Quarta-feira, 7 de Abril de 2010 11:31:27
Assunto: Re: [GENERAL] count with high allocation

paulo matadr <saddoness@yahoo.com.br> writes:

Monitoring "top" in database server , i could noticed an query with
reserved 8GB on physical memory.

select count(field) from big_table 1 inner join big_table2...

There is the possibility of using another function with less memory
allocation?
Is there a way to limit the memory usage of the count?

It seems quite likely that what top is telling you just reflects the
process touching all shared buffers, and has nothing to do with any
real "memory consumption". What do you have shared_buffers set to?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

________________________________
Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 -
Celebridades - Música - Esportes

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

____________________________________________________________________________________
Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com

---------- Forwarded message ----------
From: paulo matadr <saddoness@yahoo.com.br>
Date: 2010/4/7
Subject: Res: [GENERAL] count with high allocation
To: pgsql-general@postgresql.org

shared_buffer is too large. It is good for server with 64GB RAM. It
can be about 1/2 RAM for dedicated server. PostgreSQL allocate shared
memory after start - before your query, and use it as memory cache.

postgres=# show shared_buffers;
 shared_buffers
----------------
 16GB
(1 row)

shared_buffers + work_mem * max_connection < 90% of dedicated memory

Regards
Pavel Stehule

postgres=# show work_mem;
 work_mem
----------
 5MB
(1 row)

________________________________
De: Pavel Stehule <pavel.stehule@gmail.com>
Para: paulo matadr <saddoness@yahoo.com.br>
Cc: pgsql-general@postgresql.org
Enviadas: Quarta-feira, 7 de Abril de 2010 13:51:24
Assunto: Re: [GENERAL] count with high allocation

2010/4/7 paulo matadr <saddoness@yahoo.com.br>:

with
set enable_hashagg to off , I give the same allocation.

ok, then problem will be other.

what is result of:

show shared_buffers;
show work_mem;

Regards
Pavel Stehule

________________________________
De: Pavel Stehule <pavel.stehule@gmail.com>
Para: paulo matadr <saddoness@yahoo.com.br>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; GENERAL <pgsql-general@postgresql.org>
Enviadas: Quarta-feira, 7 de Abril de 2010 12:10:23
Assunto: Re: [GENERAL] count with high allocation

please, EXPLAIN ANALYZE

and try to execute

set enable_hashagg to off before as second variant. It have to take less
memory

regards
Pavel Stehule

2010/4/7 paulo matadr <saddoness@yahoo.com.br>:

EXPLAIN/TOP

EXPLAIN:
Aggregate  (cost=3710076.58..3710076.59 rows=1 width=4)
  ->  Hash Join  (cost=2668820.36..3692200.16 rows=7150570 width=4)
        Hash Cond: (osunidade.attp_id = art.attp_id)
        ->  Hash Join  (cost=2668819.29..3593878.75 rows=7150570 width=8)
              Hash Cond: (osunidade.orse_id = os.orse_id)
              ->  Seq Scan on ordem_servico_unidade osunidade
(cost=0.00..429514.00 rows=23418900 width=8)
              ->  Hash  (cost=2598702.48..2598702.48 rows=4033665 width=4)
                    ->  Hash Left Join  (cost=1372486.83..2598702.48
rows=4033665 width=4)
                          Hash Cond: (os.cbdo_id = cobra.cbdo_id)
                          ->  Merge Join  (cost=0.00..880392.67
rows=4033665
width=8)
                                Merge Cond: (os.rgat_id = ra.rgat_id)
                                ->  Index Scan using xfk1_ordem_servico on
ordem_servico os  (cost=0.00..879051.89 rows=13210693 width=12)
                                ->  Index Scan using
registro_atendimento_pkey on registro_atendimento ra
(cost=0.00..548171.12
rows=5369913 width=4)
                                      Filter: (rgat_id IS NOT NULL)
                          ->  Hash  (cost=897238.26..897238.26
rows=27340126
width=4)
                                ->  Seq Scan on cobranca_documento cobra
(cost=0.00..897238.26 rows=27340126 width=4)
        ->  Hash  (cost=1.03..1.03 rows=3 width=4)
              ->  Seq Scan on atendimento_relacao_tipo art
(cost=0.00..1.03
rows=3 width=4)

"TOP"
PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

26809 postgres  16   0 16.5g 8.6g 8.6g S    1 27.5   1:28.84 postgres:
user
database 10.1.1.7(54033) SELECT
________________________________
De: Tom Lane <tgl@sss.pgh.pa.us>
Para: paulo matadr <saddoness@yahoo.com.br>
Cc: GENERAL <pgsql-general@postgresql.org>
Enviadas: Quarta-feira, 7 de Abril de 2010 11:31:27
Assunto: Re: [GENERAL] count with high allocation

paulo matadr <saddoness@yahoo.com.br> writes:

Monitoring "top" in database server  , i could  noticed an query with
reserved  8GB on physical memory.

select count(field) from big_table  1 inner join big_table2...

There is the possibility of using another function with less memory
allocation?
Is there a way to limit the memory usage of the count?

It seems quite likely that what top is telling you just reflects the
process touching all shared buffers, and has nothing to do with any
real "memory consumption".  What do you have shared_buffers set to?

            regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

________________________________
Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 -
Celebridades - Música - Esportes

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

________________________________
Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 -
Celebridades - Música - Esportes