System index is not being used when running a SOQL query with DAY_ONLY()

Question

CreateDate is a system index field maintained by Salesforce. I'd like to know when you running a query like this:

 Select Id from Account Where Day_Only(CreatedDate) > exampleDate

Does the Day_Only function make the query un-selective? Is the System index used when using soql functions? Salesforce query plan says CreatedDate is indexed, but i'm not sure it is used when running the code.

I believe Salesforce does not use the index when you use Day_Only function with a custom index.

Answer 1

Analysis

Using Developer Console, you can gather the following evidence for yourself when you have this type of question. Long story short, fields will not be optimized via index if they are wrapped in an expression.

---

The plot thickens. You actually can get an index while using an expression if you use Date Literals. The following queries do use an index:

SELECT Id FROM User WHERE DAY_ONLY(CreatedDate) >= LAST_N_DAYS:42
SELECT Id FROM User WHERE DAY_ONLY(CreatedDate) = THIS_YEAR

---

Evidence

Query:

SELECT Id FROM User WHERE CreatedDate > 2016-01-01T00:00:00Z

Leading Operation Type:

Index

Notes:

None

---

Query:

SELECT Id FROM User WHERE DAY_ONLY(CreatedDate) > 2016-01-01

Leading Operation Type:

TableScan

Notes:

Not considering filter for optimization because isExpression. Table: User Fields: ["CreatedDate"]