Issue Details (XML | Word | Printable)

Key: CORE-3076
Type: Improvement Improvement
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Alex Kurov
Votes: 1
Watchers: 2

If you were logged in you would be able to see more operations.
Firebird Core

Better performance for (table.field = :param or :param = -1) in where clause

Created: 20/Jul/10 01:37 PM   Updated: 15/Apr/20 06:06 AM
Component/s: Engine
Affects Version/s: 2.5 RC1, 2.5 RC2
Fix Version/s: 3.0 Alpha 1

Issue Links:

QA Status: Done successfully

 Description  « Hide
For example we've got a table TABLE with field FIELD. There is a primary key (or index) on it.
When executing such query
select *
from table
where table.field = :param
Optimizer uses index on table.field

But when executing

select *
from table
where table.field = :param or :param = -1

The index on table.field is not used

Is there any way to optimize the execution of the second statement? Such where clause is often used in stored procedures with input parameters with default values.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 20/Jul/10 04:01 PM
Yes, I suppose this particular case can be optimized.

Sérgio Alexandre Gianezini Júnior added a comment - 21/Jan/11 02:14 PM
If you use something like:

select *
from table
where table.field = coalesce(:param, table.field)

You also don't get the optimize to use the index.

Dmitry Yemanov added a comment - 21/Jan/11 02:28 PM
Sergio, this is absolutely impossible. Before performing an indexed lookup on table.field, its value must already be known (and coalesce evaluated). Doesn't it sound like a chicken and egg problem to you? It can be possible only once you have the table accessed, thus making the index lookup already late.

Sean Leyne added a comment - 21/Jan/11 04:50 PM

Given the current design, I understand how this is not possible...

However, if the determination of the value to be searched for was left to a later point in the execution, then it should be possible. Afterall, COALESCE is a function, the engine should be able to treat the result of a function as a variable, with the function result evaluated just before the index search occurs...

Dmitry Yemanov added a comment - 21/Jan/11 06:44 PM
The COALESCE result has to be known before the index scan occurs. In order to evaluate it, both ":param" and "table.field" has to be known. The latter one is a priori unknown, as we're only about to access the table. So the only possible way would be to evaluate ":param" and depending on its value (null or not) choose between NATURAL and INDEX plans. It means that the query has to be re-optimized at runtime. This is currently impossible and generally, I don't like it much, although in some situations it could be beneficial.

Vladimir Arkhipov added a comment - 24/Mar/20 10:15 AM
Case with multiple conditions does not work:
(field1 = :param1 or :param1 = -1) and (field2 = :param2 or :param2 = -1) and (field3 = :param3 or :param3 = -1)

In this case only first index is used but we need to use the index depending on the param values:
1. param1 = 123, param2 = -1, param3 = -1 - index1 should be used
2. param1 = -1, param2 = 456, param3 = -1 - index2 should by used
3. param1 = -1, param2 = 456, param3 = 789 - index2 and index3 should be used
 3.1. if there is composite index on field2 and field2, than composite index should be used

Dmitry Yemanov added a comment - 24/Mar/20 10:25 AM
The goal for this feature was to avoid a full table scan, if possible. I believe it could be improved further, but this is outside the original task. Some other day, perhaps.

Dmitry Yemanov added a comment - 15/Apr/20 06:06 AM
@Vladimir : So far I think your point 3.1 will be impossible. Others should be doable though.