sql - Fastest way to PostgreSQL Distinct and Format -

i have 3.5 million rows in table acs_objects , need retrieve column creation_date year format , distinct.

my first attempt : 180~200 sec (15 rows fetched)

select distinct to_char(creation_date,'yyyy') acs_objects 

my second attempt : 35~40 sec (15 rows fetched)

select distinct to_char(creation_date,'yyyy') (select distinct creation_date acs_objects) distinct_date 

is there way make faster? -"i need use in adp website"

in second attempt distinct dates sub-query convert string representation , select distinct ones. rather inefficient. better first extract distinct years creation_date in sub-query , cast text in main query:

select year::text (   select distinct extract(year creation_date) year acs_objects ) distinct_years; 

if create index on table, query should run faster still:

create index really_fast on acs_objects((extract(year creation_date))); 

however, may impact other uses of table, in particular if have many modifying statements (insert, update, delete). , work if creation_date has data type of date or timestamp (specifically not timestamp timezone).

the below option looked promising because not use sub-query, in fact slower (see comments below), because distinct clause applied on string:

select distinct extract(year creation_date)::text acs_objects;