I have created index like
CREATE INDEX bill_open_date_idx ON bill USING btree(date(open_date));
and,
Column | Type
open_date | timestamp without time zone
And explain analyse are as follows
CASE 1
explain analyze select * from bill where open_date >=date('2018-01-01');
Seq Scan on bill (cost=0.00..345264.60 rows=24813 width=1132) (actual time=0.007..1305.730 rows=5908 loops=1)
Filter: (open_date >= '2018-01-01'::date)
Rows Removed by Filter: 3238812
Total runtime: 1306.176 ms
CASE 2
explain analyze select * from bill where open_date>='2018-01-01';
Seq Scan on bill (cost=0.00..345264.60 rows=24813 width=1132) (actual time=0.006..1220.697 rows=5908 loops=1)
Filter: (open_date>= '2018-01-01 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 3238812
Total runtime: 1221.131 ms
CASE 3
explain analyze select * from bill where date(open_date) >='2018-01-01';
Index Scan using idx_bill_open_date on bill (cost=0.43..11063.18 rows=22747 width=1132) (actual time=0.016..4.744 rows=5908 loops=1)
Index Cond: (date(open_date) >= '2018-01-01'::date)
Total runtime: 5.236 ms
(3 rows)
I did enough research on why this is happening, but there is no proper explanations anywhere. Only case 3 is using the index I have created, but not others. Why is this happening?
As far as my understanding goes, case 2 searches for string equivalent of the column open_date
and hence it is not using index. But why not case 1. Also, please correct me if I am wrong.
Thanks in advance!
Edit 1: Also, I'd be delighted to know what is happening in depth.
Following is an excerpt from the gist (https://gist.github.com/cobusc/5875282)
It is strange though that PostgreSQL rewrites the function used to create the index to a canonical form, but does not seem to do the same when the function is used in the WHERE clause (in order to match the index function).
Still, I am unclear why the developers of postgres didn't think of fetching any nearby matching index (Or is my index useless until I cast explicitly to date
as in case 3). Considering Postgres is highly evolved and scalable.
A b-tree index can only be used for a search condition if the condition looks like this:
The
<indexed expression>
must be the expression you used in theCREATE INDEX
statement.The
<operator>
must belong to the default operator class for the data type and the index access method, or to the operator class specified inCREATE INDEX
.The
<expression that is constant during the index scan>
can be a constant or can containIMMUTABLE
orSTABLE
functions and operators, but nothingVOLATILE
.All your queries satisfy the last two conditions, but only the third one satisfies the first one. That is why only that query can use the index.
For documentation that covers this in excruciating detail, see the comment for
match_clause_to_indexcol
inpostgresql/src/backend/optimizer/path/indxpath.c
: