Res: count with high allocation

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(at)sss(dot)pgh(dot)pa(dot)us>
Para: paulo matadr <saddoness(at)yahoo(dot)com(dot)br>
Cc: GENERAL <pgsql-general(at)postgresql(dot)org>
Enviadas: Quarta-feira, 7 de Abril de 2010 11:31:27
Assunto: Re: [GENERAL] count with high allocation

paulo matadr <saddoness(at)yahoo(dot)com(dot)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(at)postgresql(dot)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