FirebirdSQL
Plan/Performance regression when using special construct for IN in FB5.x compared to FB3.x
The following query results in full table scan instead of using the dedicated indexes on the two fields in the IN clause
SELECT e.*
FROM Employees e
WHERE :SomeID IN (e.LeaderID, e.DispEmpID)
which as expected leads to big degradation in the performance compared to FB3.x.
If we modify the query syntax to use
e.LeaderID = :SomeID OR e.DispEmpID = :SomeID
the plan changes to
PLAN (E INDEX (EMPLOYEESBYLEADERID, EMPLOYEESBYDISPEMPID))
and expectedly the performance is great in both versions of Firebird.
Here is а snippet of the table definition `CREATE TABLE Employees( EmpID BIGINT NOT NULL,
LeaderID BIGINT, DispEmpID BIGINT, .... CONSTRAINT PK_EmpID PRIMARY KEY (EmpID) );
.... CREATE INDEX EmployeesByLeaderID ON Employees(LeaderID); CREATE INDEX EmployeesByDispEmpID ON Employees(DispEmpID); ....`
Such usage was surely not considered in the new IN implementation. However, I will take a look whether it's possible to re-optimize this case.
Great, thank you. In the meantime is there by any chance a configuration option in FB5 that we can use to switch back to the old implementation?
@dyemanov, Did you manage to have a look at this issue? Is there any chance you will release a fix soon? This regression stops us from migrating to FB5 and this is starting to become a problem for us. 😔
I have the same issue. SELECT FIELD FROM MYTABLE WHERE 'some value' in ( FIELD1,FIELD2)
In FB2.5 it use the individual indexes of each field. In FB5 it will always PLAN AS NATURAL.
This kind of query we use a lot in my Software. Have to change all the code would take a lot of time to migrate do FB 5.
A workaround would be great.
I tried to solve it before the v5.0.1 release but it wasn't straightforward, so I had to postpone it. Will retry again soon.
Hello All! if anyone else is affected by this issue, please contact me, we are testing the solution in HQbird.
