From: Tarlika Elisabeth Schmitz <postgresql2@numerixtechnology.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: identifying duplicates in table with redundancies
Date: Tue, 28 Sep 2010 21:36:00 +0100
Message-ID: <20100928213600.4db60ba4@dick.coachhouse> (raw)
In-Reply-To: <5F1C28C4D61848D6A012C5DD84B22226@marktestcr.marktest.pt>
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>
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
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: postgresql2@numerixtechnology.de
Subject: Re: identifying duplicates in table with redundancies
In-Reply-To: <20100928213600.4db60ba4@dick.coachhouse>
* 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