Hi,

We are working on porting large database from Interbase to Firebird. We noticed some
differences in using of indexes. It seems that in some of SQL select statements Firebird does not use
indexes while Interbase did that.

Indicies weren't used in procedures which
- use FOR SELECT statement
- table index has recalculated selectivity index (with use of IB Expert)
- the same statement executed outside procedure uses PLAN (according to IB Expert)
- procedure was created or recompiled after adding index
- database was restarted before check

After explicit adding PLAN clause in FOR SELECT statement use of index succeed (visible in plan in IB Expert, runs correctly and as expected much faster)

An example code is the following:

SELECT z_id
FROM maps
WHERE m_id = :m_id
AND :r_id BETWEEN map__start AND map__end
AND z_id != 0
PLAN (maps INDEX (RANGES))
INTO :home_id;

RANGES is a composite index: M_ID,MAP__START,MAP__END

Unfortunatelly such situation appears in many places in our code. Thus we would like to know if we
need to explicitly add PLAN eveywhere or there is a more general solution. I would expect that there should be a procedure inside Firebird code which automathically decides which plan to use. Perhaps
simple change of it could restore behaviour known from Interbase.

Another problem we encountered is also related with indices. While we can enforce explicit plan in
select statements we can not do that in views. Although Firebird SQL syntax allows such a possibility it simply does not work. This is a serious performance issue which could slow our code over 100 times in
comparison with Interbase.

I would be grateful for any comments and suggestions

Best regards

Pawel