X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2AA4732A088 for ; Thu, 7 Oct 2004 18:08:20 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 35483-08 for ; Thu, 7 Oct 2004 17:08:17 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id D7EE332A063 for ; Thu, 7 Oct 2004 18:08:15 +0100 (BST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1CFbkS-0001VQ-00; Thu, 07 Oct 2004 13:08:12 -0400 To: =?iso-8859-1?q?Pierre-Fr=E9d=E9ric_Caillaud?= Cc: "Tom Lane" , "Greg Stark" , pgsql-performance@postgresql.org Subject: Re: sequential scan on select distinct References: <200410061130.58625.ole@freiheit.com> <874ql7ztnb.fsf@stark.xeocode.com> <29194.1097091506@sss.pgh.pa.us> In-Reply-To: From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 07 Oct 2004 13:08:11 -0400 Message-ID: <87pt3u78xw.fsf@stark.xeocode.com> Lines: 21 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/83 X-Sequence-Number: 8559 Pierre-Frédéric Caillaud writes: > I see this as a minor annoyance only because I can write GROUP BY > instead of DISTINCT and get the speed boost. It probably annoys people > trying to port applications to postgres though, forcing them to rewrite > their queries. Yeah, really DISTINCT and DISTINCT ON are just special cases of GROUP BY. It seems it makes more sense to put the effort into GROUP BY and just have DISTINCT and DISTINCT ON go through the same code path. Effectively rewriting it internally as a GROUP BY. The really tricky part is that a DISTINCT ON needs to know about a first() aggregate. And to make optimal use of indexes, a last() aggregate as well. And ideally the planner/executor needs to know something is magic about first()/last() (and potentially min()/max() at some point) and that they don't need the complete set of tuples to calculate their results. -- greg