X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 52EF43A411D for ; Tue, 9 Nov 2004 00:56:21 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01562-10 for ; Tue, 9 Nov 2004 00:56:07 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 6817C3A3AE2 for ; Tue, 9 Nov 2004 00:56:07 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iA90u47Y012782; Mon, 8 Nov 2004 19:56:04 -0500 (EST) To: Erik Norvelle Cc: pgsql-performance@postgresql.org Subject: Re: Slow performance with Group By In-reply-to: References: Comments: In-reply-to Erik Norvelle message dated "Tue, 09 Nov 2004 01:09:58 +0100" Date: Mon, 08 Nov 2004 19:56:04 -0500 Message-ID: <12781.1099961764@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/154 X-Sequence-Number: 9108 Erik Norvelle writes: >>> it=> explain select codelemm, sectref, count(codelemm) from indethom > group by codelemm, sectref; >>> QUERY PLAN >>> ----------------------------------------------------------------------- > --------- >>> GroupAggregate (cost=2339900.60..2444149.44 rows=1790528 width=13) >>> -> Sort (cost=2339900.60..2364843.73 rows=9977252 width=13) >>> Sort Key: codelemm, sectref >>> -> Seq Scan on indethom (cost=0.00..455264.52 rows=9977252 > width=13) Actually the painful part of that is the sort. If you bump up sort_mem enough it will eventually switch over to a HashAggregate with no sort, which may be a better plan if there's not too many groups (is the estimate of 1.79 million on the mark at all??) regards, tom lane