Hi,

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

<snip>

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

The natural scan is correct here, because the sub-query in the IN predicate uses already an index and depends
location.location_id. The IN predicate is handled the same as the EXISTS predicate.

In Firebird 2.0 you could change the query to:

SELECT
l.Location_ID
FROM
Location l
JOIN (SELECT DISTINCT f.Location_ID
FROM Finding f
JOIN Survey_Finding sf ON (sf.Finding_ID = f.Finding_ID)
WHERE sf.Survey_ID = '536313bd-427e-42db-964e-7a8c9092c455') dt ON (dt.Location_ID = l.Location_ID)

When the result of sub-select is small compared to the location table this should speed-up the query.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
http://www.firebirdsql.org
http://www.firebirdsql.info

Support list for Interbase and Firebird users:
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep:
news://newsgroups.firebirdsql.info