Thread: Why distinct so slow ?

Why distinct so slow ?

From
Hervé Piedvache
Date:
Hi,

I have a strange (for me) result ... Why the second request is really quicker
with a Seq Scan than the first one with a DISTINCT and using an index !?

The table have really 183957 rows ... not like the Seq Scan seems to
expect ... !? I understand nothing here ...

Thanks for your explanations ...

# explain analyze SELECT distinct s.id_category FROM site s;

                                                                        QUERY
PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=0.00..11903.15 rows=56 width=4) (actual time=0.147..1679.170
rows=68 loops=1)
   ->  Index Scan using ix_site_id_category on site s  (cost=0.00..11496.38
rows=162706 width=4) (actual time=0.143..1452.611 rows=183957 loops=1)
 Total runtime: 1679.496 ms
(3 rows)

Time: 1680,810 ms


# explain analyze SELECT s.id_category FROM site s group by id_category;

                                                       QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=7307.83..7307.83 rows=56 width=4) (actual
time=1198.968..1199.084 rows=68 loops=1)
   ->  Seq Scan on site s  (cost=0.00..6901.06 rows=162706 width=4) (actual
time=0.097..921.676 rows=183957 loops=1)
 Total runtime: 1199.260 ms
(3 rows)

--
Bill Footcow