Why order by column not using index with distinct keyword in select clause?

[Arup Rakshit]

I have define a simple B Tree index on column *country* for users table. I 
don’t understand why the order by column not using the index scan when using 
*distinct* keyword in the select clause. Can anyone explain what is happening 
here?

aruprakshit=# \d users;
                                     Table "public.users"
   Column   |         Type          | Collation | Nullable |              
Default
------------+-----------------------+-----------+----------+-----------------------------------
 city       | character varying     |           |          |
 last_name  | character varying(50) |           |          |
 country    | character varying(50) |           |          |
 sequence   | integer               |           |          |
 first_name | character varying(50) |           |          |
 state      | character varying(50) |           |          |
 email      | character varying     |           |          |
 id         | smallint              |           | not null | 
nextval('users_id_seq'::regclass)
Indexes:
    "users_pk" PRIMARY KEY, btree (id)

aruprakshit=# explain analyze select distinct country from users order by 
country asc;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Sort  (cost=283.19..283.85 rows=263 width=11) (actual time=10.525..10.555 
rows=263 loops=1)
   Sort Key: country
   Sort Method: quicksort  Memory: 38kB
   ->  HashAggregate  (cost=269.99..272.62 rows=263 width=11) (actual 
time=8.469..8.521 rows=263 loops=1)
         Group Key: country
         ->  Seq Scan on users  (cost=0.00..244.99 rows=9999 width=11) (actual 
time=0.022..3.428 rows=9999 loops=1)
 Planning time: 0.358 ms
 Execution time: 10.634 ms
(8 rows)

aruprakshit=# explain analyze select country from users order by country asc;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using country on users  (cost=0.29..886.27 rows=9999 width=11) 
(actual time=0.083..7.581 rows=9999 loops=1)
   Heap Fetches: 9999
 Planning time: 0.118 ms
 Execution time: 8.332 ms
(4 rows)

aruprakshit=# explain analyze select * from users order by country asc;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Index Scan using country on users  (cost=0.29..886.27 rows=9999 width=73) 
(actual time=0.015..8.432 rows=9999 loops=1)
 Planning time: 0.213 ms
 Execution time: 9.086 ms
(3 rows)

aruprakshit=#


Thanks,

Arup Rakshit
a...@zeit.io