Postgres not using index when using date() function

Question

My schema is

CREATE TABLE a (
  id     BIGINT PRIMARY KEY,
  dt     TIMESTAMP NOT NULL
);

and I have created an index on dt:

CREATE INDEX a_dt_index ON a (dt);

The index is working fine when I use a query like

SELECT *
FROM a
WHERE dt >= '2008-12-30' AND dt < '2008-12-31';

However when I use the date() function the index isn't used.

SELECT *
FROM a
WHERE date(dt) = '2008-12-30'

In my opinion the two queries a semantically the same, so why is the index not used? Why I have to create an explicit date() index?

CREATE INDEX a_date_index ON a (date(dt));

Atilla Ozgur · Accepted Answer · 2014-12-15 21:38:07Z

7

You may read following link to learn more about indexes and date. But TL;DR

functions are black boxes to the database.

Therefore

If you use any function in your where clauses, you need to create an explicit index with that function. Database does not understand your semantic equivalency.

It is similar to case that

 WHERE UPPER(NAME)

does not use index in NAME column. According to database UPPER function is not different than BLACKBOX. Replace it.

 WHERE BLACKBOX(NAME)

edited Dec 15, 2014 at 21:38

answered Dec 15, 2014 at 21:32

Atilla Ozgur

14.8k3 gold badges54 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

amacvar · Accepted Answer · 2014-12-15 21:55:22Z

Look at a sample output of using the date function

postgres# select id, date(dt), dt from a;
 id |    date    |             dt             
----+------------+----------------------------
  1 | 2014-12-15 | 2014-12-15 16:32:13.942183
  2 | 2014-12-15 | 2014-12-15 16:34:05.480178
(2 rows)

Time: 2.190 ms

The type of data stored is different.

Somebody else might use the function a different way:

postgres#  SELECT *
FROM a
WHERE dt >= '2014-12-15 16:33' AND dt < '2014-12-16 ';
 id |             dt             
----+----------------------------
  2 | 2014-12-15 16:34:05.480178
(1 row)

Time: 2.168 ms
postgres#  SELECT *
FROM a
WHERE date(dt) >= '2014-12-15 16:33' AND dt < '2014-12-16 ';
 id |             dt             
----+----------------------------
  1 | 2014-12-15 16:32:13.942183
  2 | 2014-12-15 16:34:05.480178
(2 rows)

I didn't find the function in docs!! But here is the description:

postgres# \df+ date()
                                                                                   List of functions
Schema   | Name | Result data type |     Argument data types     |  Type  | Security | Volatility |  Owner   | Language |   Source code    |               Description                
------------+------+------------------+-----------------------------+--------+----------+------------+----------+----------+------------------+------------------------------------------
 pg_catalog | date | date             | abstime                     | normal | invoker  | stable     | postgres | internal | abstime_date     | convert abstime to date
 pg_catalog | date | date             | timestamp without time zone | normal | invoker  | immutable  | postgres | internal | timestamp_date   | convert timestamp to date
 pg_catalog | date | date             | timestamp with time zone    | normal | invoker  | stable     | postgres | internal | timestamptz_date | convert timestamp with time zone to date
(3 rows)

Collectives™ on Stack Overflow

Postgres not using index when using date() function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related