Not knowing how the non-PK indexes are defined, I suggest starting by
playing with these joins:

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

Firebird can't use an index if the field is manipulated, so add zero to
the field:

inner join cokis_erweiterungen e on k.gruppenschluessel+0 = e.kfz_id

With your knowledge of the keys, disable the use of the fields that the
new plan is trying to use. I've had to do this when I have a query
where I know that an index with good selectivity will actually be awful
due to the filter value I need to use matches almost all values in the
index.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of m.kochheim
Sent: Friday, November 12, 2010 4:18 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Different versions, different PLANs?

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




------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links




Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.