Why distinct so slow ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux