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