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 299A99DC944 for ; Sat, 28 Jan 2006 13:08:57 -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 05166-10 for ; Sat, 28 Jan 2006 13:08:55 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail4.sea5.speakeasy.net (mail4.sea5.speakeasy.net [69.17.117.6]) by postgresql.org (Postfix) with ESMTP id D61B69DC81E for ; Sat, 28 Jan 2006 13:08:54 -0400 (AST) Received: (qmail 9438 invoked from network); 28 Jan 2006 17:08:54 -0000 Received: from dsl081-060-184.sfo1.dsl.speakeasy.net (HELO noodles) (jwbaker@[64.81.60.184]) (envelope-sender ) by mail4.sea5.speakeasy.net (qmail-ldap-1.03) with RC4-MD5 encrypted SMTP for ; 28 Jan 2006 17:08:54 -0000 Subject: Re: Huge Data sets, simple queries From: "Jeffrey W. Baker" To: Tom Lane Cc: pgsql-performance@postgresql.org In-Reply-To: <11814.1138463702@sss.pgh.pa.us> References: <007101c623a9$82207c10$0200a8c0@videobox> <11814.1138463702@sss.pgh.pa.us> Content-Type: text/plain Date: Sat, 28 Jan 2006 09:08:53 -0800 Message-Id: <1138468133.9336.5.camel@noodles> Mime-Version: 1.0 X-Mailer: Evolution 2.5.4 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200601/460 X-Sequence-Number: 16938 On Sat, 2006-01-28 at 10:55 -0500, Tom Lane wrote: > > 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 ... Hrmm, why is it that with a similar query I get a far simpler plan than you describe, and relatively snappy runtime? select date , count(1) as nads , sum(case when premium then 1 else 0 end) as npremium , count(distinct(keyword)) as nwords , count(distinct(advertiser)) as nadvertisers from data group by date order by date asc QUERY PLAN ----------------------------------------------------------------------------------------------- GroupAggregate (cost=0.00..14452743.09 rows=721 width=13) -> Index Scan using data_date_idx on data (cost=0.00..9075144.27 rows=430206752 width=13) (2 rows) =# show server_version; server_version ---------------- 8.1.2 (1 row) -jwb