Plan/Performance regression when using special construct for IN in FB5.x compared to FB3.x

[dpankov]

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); ....`

[dyemanov]

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.

[dpankov]

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?