Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 778271337B47 for ; Wed, 29 Sep 2010 05:40:29 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 66911-04 for ; Wed, 29 Sep 2010 08:40:19 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from echo917.server4you.de (echo917.server4you.de [85.25.141.73]) by mail.postgresql.org (Postfix) with ESMTP id 80A871337B2F for ; Wed, 29 Sep 2010 05:40:17 -0300 (ADT) Received: from localhost.localdomain (essn-d9bba277.pool.mediaWays.net [217.187.162.119]) (authenticated bits=0) by echo917.server4you.de (8.14.2/8.13.6) with ESMTP id o8T8dPTl005059; Wed, 29 Sep 2010 10:39:31 +0200 Message-ID: <4CA2FB63.2030102@longimanus.net> Date: Wed, 29 Sep 2010 10:40:03 +0200 From: Andreas Schmitz User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.9) Gecko/20100921 Fedora/3.1.4-1.fc13 Lightning/1.0b3pre Thunderbird/3.1.4 MIME-Version: 1.0 To: Tarlika Elisabeth Schmitz CC: pgsql-sql@postgresql.org Subject: Re: identifying duplicates in table with redundancies References: <20100923223940.5019475e@dick.coachhouse> <8590CAE9E5504E23AB08C4EF31EA066F@marktestcr.marktest.pt> <20100924171226.6000b310@dick.coachhouse> <065CBEA1-1647-4233-BBB6-48E879FD91B0@gmail.com> <20100927175418.162ee906@dick.coachhouse> <5F1C28C4D61848D6A012C5DD84B22226@marktestcr.marktest.pt> <20100928213600.4db60ba4@dick.coachhouse> In-Reply-To: <20100928213600.4db60ba4@dick.coachhouse> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201009/114 X-Sequence-Number: 34645 On 09/28/2010 10:36 PM, Tarlika Elisabeth Schmitz wrote: > On Tue, 28 Sep 2010 11:34:31 +0100 > "Oliveiros d'Azevedo Cristina" wrote: > >> ----- Original Message ----- >> From: "Tarlika Elisabeth Schmitz" >> To: >> Sent: Monday, September 27, 2010 5:54 PM >> Subject: Re: [SQL] identifying duplicates in table with redundancies >> >> >>> On Fri, 24 Sep 2010 18:12:18 +0100 >>> Oliver d'Azevedo Christina wrote: >>> >>>>>> SELECT DISTINCT trainer_id,trainer_name >>>>>> FROM ( >>>>>> SELECT trainer_name -- The field you want to test for duplicates >>>>>> FROM ( >>>>>> SELECT DISTINCT "trainer_id","trainer_name" >>>>>> FROM student >>>>>> ) x >>>>>> GROUP BY "trainer_name" -- the field you want to test for >>>>>> duplicates >>>>>> HAVING (COUNT(*)> 1) >>>>>> ) z >>>>>> NATURAL JOIN student y >>> >>> >>> What indices would you recommend for this operation? >> But, on this query in particular I would recomend an indice on >> trainer_name, as this field will be used on the join and on the group >> by. For the other query, the one you get by substituting trainer_name >> by trainer_id, place an index on trainer_id. >> Also, these indexes may help speed up the order by clause, if you use >> one. >> >> If you have a table with lots of data you can try them around and see >> how performance varies (and don't forget there's also EXPLAIN ANALYZE) > > Strangely, these indices did not do anything. > Without, the query took about 8500ms. Same with index. > > The table has 250000 records. 11000 have trainer_name = null. Only > 13000 unique trainer_names. > > It is not hugely important as these queries are not time-critical. > This is only a helper table, which I use to analyze the date prior to > populating the destination tables with the data. > > Regards, > Tarlika > I guess explain analyze shows up a seq scan. try avoiding to use distinct. use group by instead. regards Andreas