>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.

Well, InterBase and Firebird has had separate codebases for quite a few years (at least as long as you're not porting from InterBase 6.0 to Firebird 1.0). Arno Brinkman did some great work with the Firebird optimizer initially, and the optimizer has been improved since. I'd be shocked if InterBase hadn't improved their optimizer the last 10 years, and I'm not surprised if they've chosen a different path from the one Firebird chose. I've tried some of my Firebird queries on InterBase and got annoyed that they chose an index when I explicitly wanted to avoid that, I think it was queries similar to WHERE ID = :ID AND (OTHERINDEXEDFIELD =:OIF OR 2=0). Generally, I got the impression that InterBase to a larger extent than Firebird tried to use indexes, but I only tried about two queries (I'm not saying whether using many indexes is a bad or good choice, I'm too Firebirdy myself to give an unbiased answer).

>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

How did you check that no index was used and how selective is M_ID (I don't think the full index is useful in your query, it could be used for M_ID and probably MAP__START)? Have you tried with three separate indexes rather than composite? I don't know much about IB Expert, but I do know they have some kind of 'stored procedure checker' that, although useful, is not the same as Firebird and may come up with different results from what would happen if you actually used it within Firebird. Have you run the procedure in Firebird both with and without the explicit plan?

>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.

I hardly ever write plans within statements. Whenever I see Firebird choosing a poor plan, I tend to change the query to hint to the optimizer what it ought to choose (could be adding +0 or ||'', adding OR 1=0, or changing from an INNER JOIN to a LEFT JOIN).

>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 don't understand how PLANs within VIEWs is useful. When using a VIEW in a SELECT statement, it is the full select statement that should get a PLAN. If you simply do SELECT * FROM MyView, then I can see the benefit of an explicit PLAN within the view, but if you do SELECT <whatever> FROM MyView JOIN MyTable ON ... WHERE ..., then I'd expect the optimal PLAN to be different.

Generally, Firebird and InterBase are two separate databases now. Although still similar, you will find that some queries will have to be changed. This list should be a good place to optimize queries if you run into trouble.

Set