--- In firebird-support@yahoogroups.com, "KIMURA, Meiji"
<kimura804@...> wrote:

>
> Hi All,
>
> The specific JOIN SQL became slow then I checked the SQL with

PLAN (set plan on in isql

> environment). PLAN shows the SQL using 3 indexes. After I

backup/restore the database,

> I run the same JOIN SQL. PLAN shows the SQL using 1 index and it

become fast. In my knoledge,

> backup and restore optimize *physical* data, not *logical* data. I

wonder why backup/restore

> affect the PLAN of JOIN SQL.
>

Perhaps the database before the restore, the database statistics for
some of the indices were incorrect. This made the original plan look
more attractive to the optimiser than the plan that performs faster.

Whilst in theory there is no difference, the fact is that a newly
restored database contains a perfectly balanced index, where the
original database may have had a significant skew in the index.

A newly restored database will contain no garbage. The original
database may contain garbage that it cleans up as it goes. Do not
dismiss this fact as unrelated to the slowness you may experience as
the original plan may be just as good if there was garbage to collect.

> I have two question.
>
> Q1. This situation often occurs in operation of firebird ?
> Q2. How do I avoid that JOIN SQL become slow? Can I predict with

some tools?

I see the optimiser as a feature. It allows me to write queries and
have some confidence that even when the shape of the data changes and
a different plan may be better, I do not have to re-code anything.

The rule of optimisation is to work on the most significant problems.
A query that is run once that performs a table scan on a table of 200
records instead of an indexed lookup will save you 5ms once and so is
not as worthy of attention as a query that may be nearly optimised and
is continuously run.

You can change your query to prevent it from using a particular index.

Consider this query.

select e.*
from employee e
join Country c on (e.CountryID = c.ID)
where c.Name = 'Japan';

If c.Name is indexed, then Firebird may choose to use this index then
join across to the employee table. If (in your database), 99% of
employees are in Japan, then this may be slower than simply checking
all employees, so by making a small change to the foreign key in the
query, you prevent the foreign key index from being used.

eg.

select e.*
from employee e
join Country c on (e.CountryID+0 = c.ID)
where c.Name = 'Japan';

This will not affect the results, because X + 0 = X

The difference is that now the optimiser can not use the index on
e.CountryID, so will most likely do a natural scan of the employee
table and for each employee join to the country table doing a primary
key lookup, eliminating each record that does not match as it goes.

Hope that helps

Adam