Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 74A9A1337B4E for ; Sat, 6 Nov 2010 05:58:09 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 18315-03 for ; Sat, 6 Nov 2010 08:58:03 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from 30.mail-out.ovh.net (30.mail-out.ovh.net [213.186.62.213]) by mail.postgresql.org (Postfix) with SMTP id 906D91337B49 for ; Sat, 6 Nov 2010 05:58:01 -0300 (ADT) Received: (qmail 19638 invoked by uid 503); 6 Nov 2010 08:36:17 -0000 Received: from b6.ovh.net (HELO mail639.ha.ovh.net) (213.186.33.56) by 30.mail-out.ovh.net with SMTP; 6 Nov 2010 08:36:17 -0000 Received: from b0.ovh.net (HELO queueout) (213.186.33.50) by b0.ovh.net with SMTP; 6 Nov 2010 10:58:09 +0200 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO apollo13) (lists%peufeu.com@88.161.102.87) by ns0.ovh.net with SMTP; 6 Nov 2010 10:58:08 +0200 Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes To: pgsql-performance@postgresql.org, "Jon Nelson" Subject: Re: postmaster consuming /lots/ of memory with hash aggregate. why? References: Date: Sat, 06 Nov 2010 09:57:57 +0100 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit From: "Pierre C" Message-ID: In-Reply-To: User-Agent: Opera Mail/10.62 (Linux) X-Ovh-Tracer-Id: 1774418255312717361 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|U 0.5/N X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201011/82 X-Sequence-Number: 41106 > 2. Why do both HashAggregate and GroupAggregate say the cost estimate > is 40000 rows? I've reproduced this : CREATE TABLE popo AS SELECT (x%1000) AS a,(x%1001) AS b FROM generate_series( 1,1000000 ) AS x; VACUUM ANALYZE popo; EXPLAIN ANALYZE SELECT a,b,count(*) FROM (SELECT * FROM popo UNION ALL SELECT * FROM popo) AS foo GROUP BY a,b; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=43850.00..44350.00 rows=40000 width=8) (actual time=1893.441..2341.780 rows=1000000 loops=1) -> Append (cost=0.00..28850.00 rows=2000000 width=8) (actual time=0.025..520.581 rows=2000000 loops=1) -> Seq Scan on popo (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.025..142.639 rows=1000000 loops=1) -> Seq Scan on popo (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.003..114.257 rows=1000000 loops=1) Total runtime: 2438.741 ms (5 lignes) Temps : 2439,247 ms I guess the row count depends on the correlation of a and b, which pg has no idea about. In the first example, there is no correlation, now with full correlation : UPDATE popo SET a=b; VACUUM FULL popo; VACUUM FULL popo; ANALYZE popo; EXPLAIN ANALYZE SELECT a,b,count(*) FROM (SELECT * FROM popo UNION ALL SELECT * FROM popo) AS foo GROUP BY a,b; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=43850.00..44350.00 rows=40000 width=8) (actual time=1226.201..1226.535 rows=1001 loops=1) -> Append (cost=0.00..28850.00 rows=2000000 width=8) (actual time=0.008..518.068 rows=2000000 loops=1) -> Seq Scan on popo (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.007..128.609 rows=1000000 loops=1) -> Seq Scan on popo (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.005..128.502 rows=1000000 loops=1) Total runtime: 1226.797 ms