Hi,

after an update to the version "ISQL Version: WI-V2.5.0.26074 Firebird 2.5" I got a huge problem. Even though I use the exact same database and the same query, I get a different PLAN and the execution time raised from ~30 sec to ~340 sec.

Here is the query command:
-- ---
select
m.bezeichnung, e.modell, s.typsearch, k.nennlkw4, e.verk, k.vierxvier, e.baujahr, e.marke, k.hsntsnausf
from
sreif_dim sd
join
sreif_dim_to_kba sk on sd.id = sk.dim_id
inner join
kba_neu k on k.hsntsnausf = sk.hsntsnausf
inner join
cokis_erweiterungen e on k.gruppenschluessel = e.kfz_id
inner join
kfz_marke m on e.marke = m.id
inner join
kfz_gruppen_schluessel s on s.id = k.gruppenschluessel
where
sd.breit = 185 and
sd.quer = 65 and
sd.durch = 15
group by
m.bezeichnung, e.modell, s.typsearch, e.verk, k.nennlkw4, e.baujahr, k.vierxvier, e.marke, k.hsntsnausf;
-- ---

This is the old plan:

PLAN SORT (JOIN (SD INDEX (T1), SK INDEX (SREIF11), K INDEX
(KBA_NEU_HSNTSNAUSF), E INDEX (COKIS_ERWEITERUNGEN_IDX2), M INDEX (RDB$PRIMARY164), S INDEX (RDB$PRIMARY18)))

And that's the new one:

PLAN SORT (JOIN (E NATURAL, S INDEX (RDB$PRIMARY18), M INDEX
(RDB$PRIMARY164), K INDEX (KBA_NEU_GRUPPENSCHLUESSEL), SK INDEX (IDXS1), SD INDEX (RDB$PRIMARY14)))

I think the "E NATURAL" part is the problem. However I can't force FB to use the old PLAN. I just got an error message saying that T1 can't be used in this plan.

Maybe a hint: In the old plan there are a few indices over several fields (like T1). In the new plan there are only indices over a single field.

I recalculated all statistics, and I even deactivated and reactivated all indices.

I'm not sure which version of FB was used before, but I guess it was 2.x or 1.5.x. I think that's all I can say about the problem. Oh well, the tables I am using have something around 16k and 300k entries.

I honestly hope someone can help me or give me a hint. I really stuck right now after trying everything I/Google could came up with.

Greetings
Mewes Kochheim