Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id E57419FBD6B for ; Thu, 2 Aug 2007 21:48:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 62179-02 for ; Thu, 2 Aug 2007 21:48:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id AFDF79FBD5D for ; Thu, 2 Aug 2007 21:48:09 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l730m7YZ017522; Thu, 2 Aug 2007 20:48:07 -0400 (EDT) To: Gregory Stark cc: "Ron Mayer" , pgsql-performance@postgresql.org Subject: Re: Why are distinct and group by choosing different plans? In-reply-to: <87r6mlef60.fsf@oxford.xeocode.com> References: <87r6mlef60.fsf@oxford.xeocode.com> Comments: In-reply-to Gregory Stark message dated "Thu, 02 Aug 2007 23:37:43 +0100" Date: Thu, 02 Aug 2007 20:48:07 -0400 Message-ID: <17521.1186102087@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/15 X-Sequence-Number: 26145 Gregory Stark writes: > I think "distinct" just doesn't know about hash aggregates yet. That's partly > an oversight and partly of a "feature" in that it gives a convenient way to > write a query which avoids them. I think it's also partly that "distinct" is > trickier to fix because it's the same codepath as "distinct on" which is > decidedly more complex than a simple "distinct". It's not an oversight :-(. But the DISTINCT/DISTINCT ON code is old, crufty, and tightly entwined with ORDER BY processing. It'd be nice to clean it all up someday, but the effort seems a bit out of proportion to the reward... regards, tom lane