Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 76B369FB25A for ; Tue, 9 Jan 2007 22:14:57 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 85882-05 for ; Tue, 9 Jan 2007 22:14:55 -0400 (AST) X-Greylist: delayed 00:55:17.137907 by SQLgrey-1.7.4 Received: from pillette.com (adsl-67-119-5-202.dsl.snfc21.pacbell.net [67.119.5.202]) by postgresql.org (Postfix) with ESMTP id 06E3C9FB259 for ; Tue, 9 Jan 2007 22:14:52 -0400 (AST) Received: from dhcp202.pillette.com (dhcp202.pillette.com [192.168.1.202]) by pillette.com (8.11.6/8.11.6) with ESMTP id l0A1JUk26166; Tue, 9 Jan 2007 17:19:30 -0800 Date: Tue, 9 Jan 2007 17:41:27 -0800 From: Andrew Lazarus X-Mailer: The Bat! (v3.95.6) UNREG / CD5BF9353B3B7091 Organization: Pillette Investment Management X-Priority: 3 (Normal) Message-ID: <1961410679.20070109174127@pillette.com> To: tsuraan CC: pgsql-performance Subject: Re: group by will not use an index? In-Reply-To: <1168386124.20602.185.camel@state.g2switchworks.com> References: <84fb38e30701091505n59e85c74i27e6ab0e5cdfdd03@mail.gmail.com> <1168386124.20602.185.camel@state.g2switchworks.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/127 X-Sequence-Number: 22728 For the reasons indicated (that is, MVCC), PG can not do a DISTINCT or the equivalent GROUP BY from index values alone. If this table is large, perhaps you could denormalize and maintain a summary table with date (using truncation) and count, updated with triggers on the original table. This table will presumably have a small number of rows at the cost of doubling the times for updates, inserts, and deletes.