Received: from mta2.snet.net (mta2.snet.net [204.60.203.71]) by postgresql.org (Postfix) with ESMTP id 28068475991 for ; Wed, 27 Feb 2002 17:50:06 -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 g1RMo5jt018858; Wed, 27 Feb 2002 17:50:05 -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 g1RMnua0021521; Wed, 27 Feb 2002 17:50:02 -0500 (EST) Message-ID: <3C7D646A.8070804@kencast.com> Date: Wed, 27 Feb 2002 17:57:46 -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/443 X-Sequence-Number: 6864 Josh Berkus wrote: > Wei Wang, > > >>How exactly slow is DISTINCT being processed in SQL engines? (not >>limited to postgresql, though comments on postgresql would be most >> relevant) >> > > I can only give you a relative result, based exlusively on my anecdotal > experience with 7.1: > > Fast: SELECT ... > Slower: SELECT ... GROUP BY x,y,z > or: SELECT DISCTINCT ON (x) ... (Postgres non-standard extension) > SLowest: SELECT DISTINCT ... > > The reason for this is that SELECT DISTINCT is effectively a GROUP BY > on all result fields of the query, and if a few of the aren't indexed > that requires a seq scan. What if I do thing like SELECT DISTINCT table1.tid, table1.name, table1.description FROM ... Does it equal to the scenario 2 or 3? 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? Thanks -- Wei Weng Network Software Engineer KenCast Inc.