pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Andreas Schmitz <mailinglist@longimanus.net>
To: Tarlika Elisabeth Schmitz <postgresql2@numerixtechnology.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: identifying duplicates in table with redundancies
Date: Wed, 29 Sep 2010 10:40:03 +0200
Message-ID: <4CA2FB63.2030102@longimanus.net> (raw)
In-Reply-To: <20100928213600.4db60ba4@dick.coachhouse>
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>
On 09/28/2010 10:36 PM, Tarlika Elisabeth Schmitz wrote:
> On Tue, 28 Sep 2010 11:34:31 +0100
> "Oliveiros d'Azevedo Cristina"<oliveiros.cristina@marktest.pt> wrote:
>
>> ----- Original Message -----
>> From: "Tarlika Elisabeth Schmitz"<postgresql@numerixtechnology.de>
>> To:<pgsql-sql@postgresql.org>
>> 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<oliveiros.cristina@gmail.com> 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
view thread (11+ messages) latest in thread
Message-ID: <4CA2FB63.2030102@longimanus.net> Permalink: ../4CA2FB63.2030102@longimanus.net/ Also on: postgresql.org/message-id/4CA2FB63.2030102@longimanus.net ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-sql@postgresql.org Cc: mailinglist@longimanus.net, postgresql2@numerixtechnology.de Subject: Re: identifying duplicates in table with redundancies In-Reply-To: <4CA2FB63.2030102@longimanus.net> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox