Fastest way to PostgreSQL Distinct and Format

Question

I have a 3.5 million rows in table acs_objects and I need to retrieve column creation_date with year only format and distinct.

My first attempt : 180~200 Sec (15 Rows Fetched)

SELECT DISTINCT to_char(creation_date,'YYYY') FROM acs_objects

My second attempt : 35~40 Sec (15 Rows Fetched)

SELECT DISTINCT to_char(creation_date,'YYYY')
FROM (SELECT DISTINCT creation_date FROM acs_objects) AS distinct_date

Is there any way to make it faster? -"I need to use this in ADP Website"

Answer 1

I think you shouldn't select distinct from this huge table. Instead try to generate a short years sequence (say from 1900 to 2100) and select from this sequence only years that exist in acs_objects table. Result set will be the same but I think it will be faster. EXISTS subquery have to run fast on an indexed field creation_date.

SELECT y 
FROM
(
   select generate_series(1900,2100) as y
) as t
WHERE EXISTS (SELECT 1 FROM acs_objects 
                    WHERE creation_date >= DATE (''||t.y||'-01-01')     
                           AND  creation_date < DATE (''||t.y + 1||'-01-01'))

SQLFiddle demo

Answer 2

In your second attempt you get distinct dates from the sub-query which you then all convert to a string representation and then you select the distinct ones. That is rather inefficient. Better is it to first extract the distinct years from the creation_date in a sub-query and simply cast those to text in the main query:

SELECT year::text
FROM (
  SELECT DISTINCT extract(year FROM creation_date) AS year FROM acs_objects
) AS distinct_years;

If you create an INDEX on the table, the query should run much faster still:

CREATE INDEX really_fast ON acs_objects((extract(year FROM creation_date)));

However, this may impact other uses of your table, in particular if you have many modifying statements (insert, update, delete). And this will only work if creation_date has a data type of date or timestamp (specifically not timestamp with timezone).

The below option looked promising because it does not use a sub-query, but it is in fact much slower (see comments below), probably because the DISTINCT clause is applied on a string:

SELECT DISTINCT extract(year FROM creation_date)::text
FROM acs_objects;

Answer 3

I'm no sure what you use it for. I probably would consider using a using a materialized view.

Now you can refresh the view when needed and have a very fast way to retrieve the (distinct) year list (since the data is basically static stored).

Have a look here:

• http://www.postgresql.org/docs/current/static/sql-creatematerializedview.html
• http://www.postgresql.org/docs/current/static/sql-refreshmaterializedview.html

Answer 4

Is there any way to make it faster?

Oh yes, much faster. (Updated 2021.)

Basic assessment

If you need this often and fast, and writes to the table are either few or predictable (like: new rows always have current time), a materialized view would be fastest, like @Roger suggested. But you still need a query to implement it. And the queries I am going to suggest are so fast that you might skip the MV ...

In related cases there is often a look-up table with candidate values allowing for a much faster query:

• Optimize groupwise maximum query

Assumptions for this case:

• Postgres 9.4 or later.
• creation_date is data type timestamp (works for date or timestamptz too).
• Actual range of timestamps is unknown.
• There is a btree index on acs_objects(creation_date).

Emulate loose index scan with rCTE

If you neither have a look-up table nor a derived table with candidate values, there is still a very fast alternative. Basically, you need to emulate an "index skip scan", a.k. a."loose index scan". This query works in any case:

WITH RECURSIVE cte AS (
   SELECT date_trunc('year', max(creation_date)) AS y
   FROM   acs_objects

   UNION ALL
   SELECT (SELECT date_trunc('year', max(creation_date))
           FROM   acs_objects
           WHERE  creation_date < cte.y)
   FROM   cte
   WHERE  cte.y IS NOT NULL
   )
SELECT to_char(y, 'YYYY') AS year
FROM   cte
WHERE  cte.y IS NOT NULL;

Probably fastest: top-down, and truncate each timestamp to the start of the year, then find the latest of the earlier rows; repeat.

Details for this technique:

• SELECT DISTINCT is slower than expected on my table in PostgreSQL
• Optimize GROUP BY query to retrieve latest row per user
• Postgres Wiki

Based on generate_series()

valex's idea can be implemented more efficiently with generate_series() producing timestamp values based on the actual range of existing years:

SELECT to_char(y, 'YYYY') AS year
FROM  (
   SELECT generate_series(date_trunc('year', min(creation_date))
                        , max(creation_date)
                        , interval  '1 year')
   FROM   acs_objects
   ) t(y)
WHERE  EXISTS (
   SELECT FROM acs_objects 
   WHERE creation_date >= y
   AND   creation_date <  y + interval '1 year'
   );

db<>fiddle here demonstrating both.
_{Old slfiddle}

If there are few gaps in the range of years, this may be faster, yet. But either should only take a few milliseconds or less, regardless of the table size.

Related:

• Generate_series in Postgres from start and end date in a table
• Generating time series between two dates in PostgreSQL