Thread: count with high allocation
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?
Paul
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?
Paul
Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 - Celebridades - Música - Esportes
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 > > > > Paul > > > ________________________________ > Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 - > Celebridades - Música - Esportes
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
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
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
with
set enable_hashagg to off , I give the same allocation.
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>:
> 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
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 > > ________________________________ > 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 > >
postgres=# show shared_buffers;
shared_buffers
----------------
16GB
(1 row)
postgres=# show work_mem;
work_mem
----------
5MB
(1 row)
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: Top 10 - Celebridades - Música - Esportes
---------- 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