I expect Ufirma to be defined with a longer field length than 2? To me, this seems like a (quite common) situation where the optimizer has to choose between two different plans which appears to be quite similar in performance (based on what is available to the optimizer at the time of prepare, not based on knowing the actual result). If the index statistics differ between two databases (e.g. if one of them have more duplicate entries or a table contains more records), then the optimizer may choose a different plan, so I'm not surprised to learn that you have a similar database with better performance. Unlike Dmitry, I'm not able to tell what happens inside Firebird, but I do know that small changes to your query (adding +0 to the right numeric fields, ||'' to the right character fields or add 'LEFT ' to the correct JOIN clause) may prevent Firebird from using the plan he currently intends to use, and hopefully make Firebird choose a better option:

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'

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Roland M. Eppelt
Sent: 3. juni 2009 14:17
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] STARTING WITH does not use index

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