Why order by column not using index with distinct keyword in selectclause? - Mailing list pgsql-general
| From | Arup Rakshit |
|---|---|
| Subject | Why order by column not using index with distinct keyword in selectclause? |
| Date | |
| Msg-id | 9B9E43A3-5FA7-4FF4-8444-73C24DEE4107@zeit.io Whole thread Raw |
| Responses |
Re: Why order by column not using index with distinct keyword in select clause?
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: Why order by column not using index with distinct keyword inselect clause? (Geoff Winkless <pgsqladmin@geoff.dj>) |
| List | pgsql-general |
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=#
pgsql-general by date: