Message-ID: <547760C5672F498ABD458B4B277DE370@marktestcr.marktest.pt>
From: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>
To: "Tarlika Elisabeth Schmitz" <postgresql2@numerixtechnology.de>,
	<pgsql-sql@postgresql.org>
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>
Subject: Re: identifying duplicates in table with redundancies
Date: Wed, 29 Sep 2010 14:00:26 +0100
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit


>
> 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