--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:

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

Our goal is to get a response in expectable time manner (1-3 seconds) from database and keep fbserver CPU load in acceptable ranges.
When we test a particular query we sample it on filled tables which get the similar conditions to a production enviroment.

The same query resulting one row finishes quickly on IB falls into exhausting NATURAL PLAN scan on FB. So we admit it is our responsibility to manually make query execution on FB efficient in some cases.

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

Separate indices weren't activated, but it was possible to put into the plan a particular index.
The structure of data gives better time with 3 columns in one index. We know the index is optimal and usable.

The matter is that FB uses that index in direct SQL statement execution but inside a procedure FB chooses other option. Such a behavior was discovered only in few procedures. It doesn't look like
something predictable, expectable and repeatable behavior.

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

Yes. But checking plans is a part of later investigation
why this stuff work that way. IB Expert presents a plan (or many plans) and per-table read counters. It is just a hint.
The most substantial measure is a time of execution


Pawel