X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0874C4758F1 for ; Tue, 4 Feb 2003 10:37:08 -0500 (EST) Received: from wolff.to (wolff.to [66.93.249.74]) by postgresql.org (Postfix) with SMTP id 323064758E1 for ; Tue, 4 Feb 2003 10:37:07 -0500 (EST) Received: (qmail 4403 invoked by uid 500); 4 Feb 2003 15:50:13 -0000 Date: Tue, 4 Feb 2003 09:50:13 -0600 From: Bruno Wolff III To: Wei Weng Cc: Christoph Haller , pgsql-sql@postgresql.org Subject: Re: iceberg queries Message-ID: <20030204155013.GA4209@wolff.to> Mail-Followup-To: Wei Weng , Christoph Haller , pgsql-sql@postgresql.org References: <3E3F7C59.E6C5278C@rodos.fzk.de> <002101c2cc56$f5112330$2f437242@PASCAL> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <002101c2cc56$f5112330$2f437242@PASCAL> User-Agent: Mutt/1.3.25i X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/77 X-Sequence-Number: 11475 On Tue, Feb 04, 2003 at 09:08:56 -0500, Wei Weng wrote: > It is a query that looks like > > SELECT target1, target2... targetn, SUN(t.qty) > FROM Table t > GROUP BY target1 > HAVING SUM(t.qty)>=10 > > You can replace SUM(t.qty)>=10 with other aggregate constraints. There were some recent changes to allow groups to use hashes that may help for queries like this. This can save a sort step or using an index scan. In theory you might be able to make some other speed ups by taking advantage of properties of specific aggregate functions (in particular that several common ones monotonicly increase or decrease as they are being calculated) and if doing an index scan on the fields used for group you might be able to skip a lot of rows. I expect that this situation would come up pretty rarely though.