X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 313CD9DCA13 for ; Sat, 28 Jan 2006 11:55:02 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 99091-01 for ; Sat, 28 Jan 2006 11:55:06 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 1A6BD9DC825 for ; Sat, 28 Jan 2006 11:54:58 -0400 (AST) 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 k0SFt2xE011815; Sat, 28 Jan 2006 10:55:02 -0500 (EST) To: "Mike Biamonte" cc: pgsql-performance@postgresql.org Subject: Re: Huge Data sets, simple queries In-reply-to: <007101c623a9$82207c10$0200a8c0@videobox> References: <007101c623a9$82207c10$0200a8c0@videobox> Comments: In-reply-to "Mike Biamonte" message dated "Fri, 27 Jan 2006 20:23:55 -0500" Date: Sat, 28 Jan 2006 10:55:02 -0500 Message-ID: <11814.1138463702@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.1 required=5 tests=[AWL=0.100] X-Spam-Score: 0.1 X-Spam-Level: X-Archive-Number: 200601/459 X-Sequence-Number: 16937 "Mike Biamonte" writes: > The queries I need to run on my 200 million transactions are relatively > simple: > select month, count(distinct(cardnum)) count(*), sum(amount) from > transactions group by month; count(distinct) is not "relatively simple", and the current implementation isn't especially efficient. Can you avoid that construct? Assuming that "month" means what it sounds like, the above would result in running twelve parallel sort/uniq operations, one for each month grouping, to eliminate duplicates before counting. You've got sortmem set high enough to blow out RAM in that scenario ... regards, tom lane