Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Better performance for (table.field = :param or :param = -1) in where clause [CORE3076] #3455

Closed
firebird-automations opened this issue Jul 20, 2010 · 19 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Alex Kurov (alex.kurov)

Relate to CORE4293

Votes: 1

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.

Commits: bb1f7ad

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Yes, I suppose this particular case can be optimized.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sérgio Alexandre Gianezini Júnior (sergioalexandre)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Dmitry,

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...

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Component: Engine [ 10000 ]

Version: 2.0.6 [ 10303 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE4293 [ CORE4293 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done with caveats

Test Details: 1) Probably it will be good to re-implement this test and use 'mon-gather-***.fbk' instead of database that is used currently;
2) Read carefully comments inside .fbt about 3.0 when decide to do "1)"!

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done with caveats => Done successfully

Test Details: 1) Probably it will be good to re-implement this test and use 'mon-gather-***.fbk' instead of database that is used currently;
2) Read carefully comments inside .fbt about 3.0 when decide to do "1)"!

=>

@firebird-automations
Copy link
Collaborator Author

Commented by: Vladimir Arkhipov (arkinform)

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

@vladimir : So far I think your point 3.1 will be impossible. Others should be doable though.

@firebird-automations
Copy link
Collaborator Author

Commented by: Vladimir Arkhipov (arkinform)

For 3.1 lets look at my example. There is table M_INVOICE - warehouse invoices.
Fields: INVDATE - date, RID - warehouse or room id, DOCCODE - doctor id.
There are indices: M_INVOICE_INVDATE, M_INVOICE_RID_INVDATE, M_INVOICE_DOCCODE_INVDATE

Condition:
(invdate = :invdate or :invdate is null) and (rid = :rid or :rid = -1) and (doccode = :doccode or :doccode = -1)

Cases:
1. invdate = 01.01.2020, rid = -1, doccode = -1 - it should use index M_INVOICE_INVDATE (01.01.2020)
2. invdate = 01.01.2020, rid = 123, doccode = -1 - it should use index M_INVOICE_RID_INVDATE (123 and 01.01.2020)
3. invdate = 01.01.2020, rid = -1, doccode = 456 - it should use index M_INVOICE_DOCCODE_INVDATE (456 and 01.01.2020)

Is it impossible? Please, give more details.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

It looks possible. But I need to invest some time looking at the internals to be sure.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment