Hello,

I am using a tool that maps relational structures to programmatic
entities and it generates the SQL automatically.
When loading a hierarchy of objects it uses the IN clause extensively
in the following way (the query is simplified)

SELECT LOCATION.LOCATION_ID
FROM LOCATION
WHERE LOCATION.LOCATION_ID
IN (SELECT FINDING.LOCATION_ID
FROM FINDING INNER JOIN SURVEY_FINDING ON FINDING.FINDING_ID=
SURVEY_FINDING.FINDING_ID
WHERE SURVEY_FINDING.SURVEY_ID =
'536313bd-427e-42db-964e-7a8c9092c455')

The query plan for this type of query both in v1.5 and v2.0 is the
following

PLAN JOIN (SURVEY_FINDING INDEX (SURVEY_FINDING_SURVEY),FINDING INDEX
(FINDING_PK)) PLAN (LOCATION NATURAL)

The high-level structure of the tables involved are
LOCATION(LOCATION_ID PK)
FINDING(FINDING_ID PK, LOCATION_ID FK)
SURVEY_FINDING(FINDING_ID FK)

I would think that the optimizer would pick the indexes for LOCATION
also instead of performing a table scan.

Is there anything I can do? Such as a hint so that Firebird would
actually use the index instead.

Thank you
Cihan