Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2201E9FA351 for ; Wed, 10 Jan 2007 13:14:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 34020-05 for ; Wed, 10 Jan 2007 13:14:14 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id D17569FA344 for ; Wed, 10 Jan 2007 13:14:14 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so153189ugf for ; Wed, 10 Jan 2007 09:14:13 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Dwx4XZID41MedDyjFP11LPEEmBlnWqssWDZq+6HAg4QhIsWkgVU5yV79ichfvhDO0JZyuQ9WCO2Nm2noSXMD+GfOc6ya9k2IeimH1aWsnCwdCnDeu5o5fn+uaxkcQAV2wyZlDxx2WMBwXnlFyHy0O+5iBG8Pmplj94VskAOephE= Received: by 10.78.181.13 with SMTP id d13mr169490huf.1168449253585; Wed, 10 Jan 2007 09:14:13 -0800 (PST) Received: by 10.78.179.8 with HTTP; Wed, 10 Jan 2007 09:14:13 -0800 (PST) Message-ID: <84fb38e30701100914y63010d26n8401d74729d12380@mail.gmail.com> Date: Wed, 10 Jan 2007 11:14:13 -0600 From: tsuraan To: "Andrew Lazarus" Subject: Re: group by will not use an index? Cc: pgsql-performance In-Reply-To: <1961410679.20070109174127@pillette.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9065_17665927.1168449253512" References: <84fb38e30701091505n59e85c74i27e6ab0e5cdfdd03@mail.gmail.com> <1168386124.20602.185.camel@state.g2switchworks.com> <1961410679.20070109174127@pillette.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/132 X-Sequence-Number: 22733 ------=_Part_9065_17665927.1168449253512 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline > For the reasons indicated (that is, MVCC), PG can not do a DISTINCT or the > equivalent > GROUP BY from index values alone. Ok, that makes sense. Thanks for the help everybody! 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. Well, the inserted time, at least, is never updated, and deletions are very rare (never, so far), so I'll have a look at doing things that way. Thanks! ------=_Part_9065_17665927.1168449253512 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
For the reasons indicated (that is, MVCC), PG can not do a DISTINCT or the equivalent
GROUP BY from index values alone.

Ok, that makes sense.  Thanks for the help everybody! 

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.

Well, the inserted time, at least, is never updated, and deletions are very rare (never, so far), so I'll have a look at doing things that way.  Thanks!
 


------=_Part_9065_17665927.1168449253512--