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@xxxxxxxxxxxx>: > 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@xxxxxxxxxxxxx> > Para: paulo matadr <saddoness@xxxxxxxxxxxx> > Cc: GENERAL <pgsql-general@xxxxxxxxxxxxxx> > Enviadas: Quarta-feira, 7 de Abril de 2010 11:31:27 > Assunto: Re: count with high allocation > > paulo matadr <saddoness@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
