Re: count with high allocation - Mailing list pgsql-general

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