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));

Answer 1

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)