Thanks you Ann and Dmitry for your detailed answer - now i know exactly why this happened.

I see that better is use separate indexes then composite in Firebird for range scans.

 

2 more questions :)

 

1.

Ann you say that index have dbkey bat have it also transaction number or it must access table data for that info

i ask - if Firebird can realize many to many join without look into table(intermediate table) data e.g.

CREATE Table1(ID1 INTEGER PRIMARY KEY, FLD1 VARCHAR(10));

CREATE Table2(ID2 INTEGER PRIMARY KEY, FLD2 VARCHAR(10));

 

CREATE T1toT2(ID1 INTEGER, ID2 INTEGER); ID1 and ID2 are foreign keys (and have indexes then)

 

and 

SELECT

T1.FLD1, T2.FLD2

FROM

T1

INNER JOIN T1ToT2 TT ON TT.ID1=T1.ID1

INNER JOIN T2 ON T2.ID2=TT.T2

 

and if index contain transaction number then this join can be realized only by using pages from:

T1, T2, and index pages on ID1, ID2 foreign keys without accessing T1ToT2 table data pages

 

2.

Is this considered to change index structure for composite indexes?

Or there is no plan for it in near feature.

This have low priority i know - but i see that you consider create R-TREE indexes - maybe this can be joined together ;-)

I know that this looks like separate tasks but..

 

regards,

Karol Bieniaszewski