> My understanding of how Firebird uses Primary keys and indexes are

below.

> If there is a Primary Key or Index available, the Optimizer will use it.

Primary keys create an index to make sure it is a unique value. There
is nothing special about the index except it is implictly created, it
is still used the exact same way as a normal index.

> I come from an AS400 environment. The programmer chooses the index
> (logical) to be used not the database. How does the optimizer choose,
> and what types of problems arise from that situation?

When the query is prepared by Firebird, the optimiser will look at
what it is trying to do, what tables are involved, what fields are
joined on and used in where conditions etc. It will consider the
different indices available and do a cost estimation of each possible
path. The cheapest one it finds will be used.

The problems that arise are the same ones that arise when you program
it. Radical changes to the data shape can cause index statistics to be
out of date. This may cause the optimiser to use an index that is bad.
Of course if you hard coded the plan, this would suffer the same fate,
but worse. You would need to adjust the plan and recompile. In
Firebird, you would only need to reset the statistics which can be
quickly done.

There are optimiser glitches from time to time which cause it to
choose a bad plan. This is usually when using a where or join
condition with poor selectivity.

> Can I choose
> the index to be used rather than the optimizer?

You can using the PLAN directive, but it is not recommended. I
sometimes use the PLAN directive to test alternative access paths
against a test database, but NEVER in production.

In the case where a bad plan is chosen, a very simple +0 on the
condition where the index is of no help will prevent the optimiser
from using that index. It should be noted that for every version of
Firebird released, these bad decisions become less and less frequent.

Adam