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
Comments
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Commented by: @dyemanov Yes, I suppose this particular case can be optimized. |
Modified by: @dyemanovFix Version: 3.0 Alpha 1 [ 10331 ] |
Commented by: Sérgio Alexandre Gianezini Júnior (sergioalexandre) If you use something like: select * You also don't get the optimize to use the index. |
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. |
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... |
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. |
Modified by: @dyemanovstatus: Open [ 1 ] => In Progress [ 3 ] |
Modified by: @dyemanovstatus: In Progress [ 3 ] => Open [ 1 ] |
Modified by: @pavel-zotovstatus: 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; |
Modified by: @pavel-zotovstatus: 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; => |
Commented by: Vladimir Arkhipov (arkinform) Case with multiple conditions does not work: In this case only first index is used but we need to use the index depending on the param values: |
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. |
Commented by: Vladimir Arkhipov (arkinform) For 3.1 lets look at my example. There is table M_INVOICE - warehouse invoices. Condition: Cases: Is it impossible? Please, give more details. |
Commented by: @dyemanov It looks possible. But I need to invest some time looking at the internals to be sure. |
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
The text was updated successfully, but these errors were encountered: