From: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: Tarlika Elisabeth Schmitz <postgresql@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: Re: identifying duplicates in table with redundancies
Date: Tue, 28 Sep 2010 11:34:31 +0100
Message-ID: <5F1C28C4D61848D6A012C5DD84B22226@marktestcr.marktest.pt> (raw)
References: <20100923223940.5019475e@dick.coachhouse>
<8590CAE9E5504E23AB08C4EF31EA066F@marktestcr.marktest.pt>
<20100924171226.6000b310@dick.coachhouse>
<065CBEA1-1647-4233-BBB6-48E879FD91B0@gmail.com>
<20100927175418.162ee906@dick.coachhouse>
Hello again,
Tarlika.
In what concerns to indices, I 'm affraid I may not be the best person to
advise you, my knowledge of them hardly goes beyond the most trivial cases.
I'm sure there are plenty of other people on the list who are able to give
you better advise than me.
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)
Bear in mind, though, that this is just my 2 cents on a matter that I don't
really master. Do not take this as an expert answer.
There are many people on this list that can help you better
Best,
Oliveiros
----- 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?
>
> --
>
> Best Regards,
> Tarlika Elisabeth Schmitz
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
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: oliveiros.cristina@marktest.pt, postgresql@numerixtechnology.de
Subject: Re: identifying duplicates in table with redundancies
In-Reply-To: <5F1C28C4D61848D6A012C5DD84B22226@marktestcr.marktest.pt>
* 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