SQL Firebird conditional where not using index

Question

I am using Firebird SQL version 2.1.

I have a sql query string in which parameters are passed. If a value is passed is not in my hand, but if it is passed, it is the right type (Timestamp or else..)

Now my query looks like this:

select r.* 
from TableA 
where r.ACTION_DATE >= iif('2019-10-09 00:00:00' is null or '2019-10-09 00:00:00' = '', r.ACTION_DATE, '2019-10-09 00:00:00')

In words, when a value is passed I want to load all records that have an action date greater then '2019-10-09 00:00:00', else get all records.

I have an Index on column ACTION_DATE. For the query I mentioned the index is not applied!

For the same query without the condition the index is applied:

select r.* 
from TableA 
where r.ACTION_DATE >= '2019-10-09 00:00:00'

Answer 1

Expressions on columns generally kill the use of indexes. Some databases can handle an explicit OR:

select r.* 
from TableA 
where r.ACTION_DATE >= '2019-10-09 00:00:00' OR
      '2019-10-09 00:00:00' IS NULL;

Otherwise, UNION ALL can handle it:

select r.* 
from TableA r
where r.ACTION_DATE >= '2019-10-09 00:00:00' 
union all
select r.*
from TableA r
where '2019-10-09 00:00:00' IS NULL;

Note that the first subquery returns no rows if the value is NULL so there is no danger of duplicate rows.

Answer 2

Haven't worked with Interbase/Firebird in decades, but but my guess would be that the right side is not sargable.

The DBMS has to get the row first, to get the value of r.ACTION_DATE, to evaluate the expression on the right side, and only then can it determine whether to keep the row in the result, at which point it's too late to use the index (we have already retrieved the row so there is no need to seek for it through the index).

Try pre-evaluating the expression and then using the resulting value in your WHERE clause (with parameter binding and all that, of course).

---

Another thing to watch out for is that an index is helpful only if the selectivity is good enough. If you retrieve too many rows (as a rule of thumb: more than 10% of the entire table), then it's generally faster to just linearly traverse the entire table even if an index is usable.