From: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: Tarlika Elisabeth Schmitz <postgresql2@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: Re: identifying duplicates in table with redundancies
Date: Wed, 29 Sep 2010 14:00:26 +0100
Message-ID: <547760C5672F498ABD458B4B277DE370@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>
<5F1C28C4D61848D6A012C5DD84B22226@marktestcr.marktest.pt>
<20100928213600.4db60ba4@dick.coachhouse>
<4CA2FB63.2030102@longimanus.net>
<20100929124107.245b1314@dick.coachhouse>
>
> Hallo Andreas,
> I reduced the problem to the innermost query:
>
> 1) SELECT DISTINCT trainer_id, trainer_name FROM student
> This results in a sequential table scan. Execution time 7500ms.
>
> 2) I created an INDEX ON (trainer_id, trainer_name). Then I had an index
> scan instead, which still cost 7000ms.
>
> 3) Next, I changed from DISTINCT to GROUP BY:
> SELECT trainer_id, trainer_name FROM student
> GROUP BY trainer_id, trainer_name
> This resulted in an index scan @ 6750ms
>
> 4) I filtered out NULL trainer_ids
> WHERE trainer_id IS NOT NULL
> Amazingly, this resulted in a sequential table scan, which only took
> 1300ms!!
>
> Please, explain (pun not intended)! How can this be. Only 11000/250000
> rows have a null trainer_id.
>
That's an impressive improvement...
Personally I have no idea what caused it, specially when you say it was
sequential :-|
Warmed caches ?
Best,
Oliver
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, postgresql2@numerixtechnology.de
Subject: Re: identifying duplicates in table with redundancies
In-Reply-To: <547760C5672F498ABD458B4B277DE370@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