Here are the statistics. Maybe these Hints are useful:
- This behaviour only occurs on one single database
- a second database (same metadata!) is ok
- index statistics are up to date
- if I omit the "JOIN Ort" everything is fine.

CASE 1: "=" GOOD
================

Preparing query: SELECT P.ID Person
FROM Person P
JOIN Strasse S ON S.ID = P.STRASSE
JOIN Ort O ON O.ID = S.Ort
WHERE P.UFirma='GU'
Prepare time: 0,004s
Field #01: PERSON.ID Alias:PERSON Type:INTEGER
PLAN JOIN (P INDEX (PERSON_UFIRMA), S INDEX (RDB$PRIMARY26), O INDEX
(RDB$PRIMARY22))


Executing...
Done.
127 fetches, 0 marks, 20 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 42 index, 0 seq.
Delta memory: -3532 bytes.
Total execution time: 0,011s
Script execution finished.


CASE 2: "STARTING WITH" BAD
===========================

Preparing query: SELECT P.ID Person
FROM Person P
JOIN Strasse S ON S.ID = P.STRASSE
JOIN Ort O ON O.ID = S.Ort
WHERE P.UFirma STARTING WITH 'GU'
Prepare time: 0,122s
Field #01: PERSON.ID Alias:PERSON Type:INTEGER
PLAN JOIN (O NATURAL, S INDEX (RDB$FOREIGN29), P INDEX (PERSON_UFIRMA,
RDB$FOREIGN58))


Executing...
Done.
34946 fetches, 0 marks, 1820 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 4200 index, 119 seq.
Delta memory: 18400 bytes.
Total execution time: 1,356s
Script execution finished.


CASE 3: "STARTING WITH" without "JOIN Ort" GOOD
===============================================

Preparing query: SELECT P.ID Person
FROM Person P
JOIN Strasse S ON S.ID = P.STRASSE
WHERE P.UFirma STARTING WITH 'GU'
Prepare time: 0,003s
Field #01: PERSON.ID Alias:PERSON Type:INTEGER
PLAN JOIN (P INDEX (PERSON_UFIRMA), S INDEX (RDB$PRIMARY26))


Executing...
Done.
140 fetches, 0 marks, 17 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 42 index, 0 seq.
Delta memory: 924 bytes.
Total execution time: 0,109s
Script execution finished.


Dmitry Yemanov schrieb:

> rme174 wrote:
>> has anyone an idea, why Firebird 2.1.2 does not use the index in Case 2? Using "LIKE" instead of "STARTING WITH" doesn't change anything. Without the "JOIN Ort" the Index is used.
>
> STARTING WITH is considered being less selective than an equality, so
> it's expected to return more rows, thus badly affecting the cost of the
> whole join chain {P->S->O} as the leading stream gets too large in terms
> of cardinality. The join order {O->S->P} is considered cheaper in this
> particular case, even without an index on its leading stream.
>
>> CASE 1: "="
>> ===========
>>
>> SELECT P.ID Person
>> FROM Person P
>> JOIN Strasse S ON S.ID = P.STRASSE
>> JOIN Ort O ON O.ID = S.Ort
>> WHERE P.UFirma='GU'
>>
>> PLAN JOIN (P INDEX (PERSON_UFIRMA), S INDEX (RDB$PRIMARY26), O INDEX (RDB$PRIMARY22))
>>
>> CASE 2: "STARTING WITH"
>> =======================
>>
>> SELECT P.ID Person
>> FROM Person P
>> JOIN Strasse S ON S.ID = P.STRASSE
>> JOIN Ort O ON O.ID = S.Ort
>> WHERE P.UFirma STARTING WITH 'GU'
>>
>> PLAN JOIN (O NATURAL, S INDEX (RDB$FOREIGN29), P INDEX (PERSON_UFIRMA, RDB$FOREIGN58))
>
> What are the execution stats for both queries? Do you have index
> statistics up-to-date?
>
>
> Dmitry