Received: from mta2.snet.net (mta2.snet.net [204.60.203.71]) by postgresql.org (Postfix) with ESMTP id BBDAC47593E for ; Wed, 27 Feb 2002 18:12:36 -0500 (EST) Received: from pop.snet.net ([204.60.203.73]) by mta2.snet.net (8.12.1/8.12.1/SNET-smtp-1.1/D-1.1/O-1.1) with ESMTP id g1RNCZjt021401; Wed, 27 Feb 2002 18:12:35 -0500 (EST) Received: from kencast.com (167.95.252.64.snet.net [64.252.95.167]) by pop.snet.net (8.12.1/8.12.1/SNET-pop-1.2/D-1.2/O-1.3) with ESMTP id g1RNCWa0009551; Wed, 27 Feb 2002 18:12:32 -0500 (EST) Message-ID: <3C7D69B6.4070301@kencast.com> Date: Wed, 27 Feb 2002 18:20:22 -0500 From: Wei Weng Reply-To: wweng@kencast.com User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.8) Gecko/20020205 X-Accept-Language: en-us MIME-Version: 1.0 To: Josh Berkus Cc: pgsql-sql@postgresql.org Subject: Re: How slow is DISTINCT? References: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200202/445 X-Sequence-Number: 6866 Josh Berkus wrote: > Wei, > > >>SELECT DISTINCT table1.tid, table1.name, table1.description FROM ... >> >>Does it equal to the scenario 2 or 3? >> > > Three. > > >>I am thinking SELECT DISTINCT table1.tid is just a variation (or the >> other way around) of SELECT DISTINCT ON (table1.tid), is that right? >> > > Wrong. SELECT DISTINCT ON table1.tid takes the table1.tid field and > the first related other data it can find. SELECT DISTINCT looks for > the unique combinaiton of all fields selected. In a multi-table, many > field query, the former is faster than the latter bceause less data is > being checked for uniqueness, and extra data is simply discarded. > > Tom, please correct me if I'm totally out on a limb, here. Is that(DISTINCT ON) SQL standard compliant or a Postgresql extension? -- Wei Weng Network Software Engineer KenCast Inc.