Hi Set,

> Here's the simple SQL statement I ended up with to make the
> optimizer look a fool:
>
> SELECT * /*which fields are irrelevant, but I specified fields from
> all tables*/
> FROM TableA A
> JOIN TableB B on A.PK = B.Apk
> LEFT JOIN TableC on B.PK = C.Bpk
> WHERE B.PK = 1

> A.PK and B.PK are primary keys and there are very selective indexes
> for B.Apk and C.Bpk (normally each value is repeated between 1 and 10
> times and each table is over 1 million records in total).

> To me, the obvious plan (which is suggested if I use a plain JOIN and
> not a LEFT JOIN) is
> PLAN JOIN (JOIN (B INDEX(PK_INDEXB), A INDEX(PK_INDEXA)), C INDEX
> (B_PK_INDEX))

> but the optimizer suggests
> PLAN JOIN(JOIN (A NATURAL, B INDEX(PK_INDEXB)), C INDEX (B_PK_INDEX))

That wouldn't make sense to me, but i can't reproduce your behaviour.
Would you please post (or send to me directly) the DDL for the tables/constraints and the
selectivity values (for example with the SQL below).

SELECT
i.RDB$RELATION_NAME,
i.RDB$INDEX_NAME,
ins.RDB$FIELD_NAME,
i.RDB$STATISTICS
FROM
RDB$INDICES i
JOIN RDB$INDEX_SEGMENTS ins ON (ins.RDB$INDEX_NAME = i.RDB$INDEX_NAME)
WHERE
i.RDB$RELATION_NAME IN ('TABLEA', 'TABLEB', 'TABLEC')
ORDER BY
1, 2

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