On 1 May 2003 at 14:49, Svein Erling Tysvær wrote:

> It surprised me that Fb chose to use an index for the smaller table if
> it can choose which table to index.

> Also, I didn't understand why
>
> >SELECT *FROM JOB J JOIN COUNTRY C ON J.JOB_COUNTRY =C.COUNTRY ;
>
> produce a plan that references the tables,

You are a very careful reader :-) I've just edited the statement to make
it shorter to avoid line wrapping, and left plan as it was for clarity.

> Still, I don't understand your original answer to the question that
> was asked when you said (or at least, I read) that Firebird couldn't
> choose a plan that avoided using natural. Sure, it chose a plan using
> natural for one table (which may or may not be the best solution),
> but it ought to have been possible for Fb to have chosen another plan
> using indexes for all tables all the time there were a limiting
> where-clause covering indexed fields on the left side of the left
> join.

Well, you seems puzzled by how server actually use indices for joins and
where filter conditions. They may or may not use index, but in either
case, the basic algorythm for data retrieval and join is the same. If
index is used, it's used just to create a bitmap of relevant rows that
are then read by normal way. So server actually always use "natural" scan
in the sense how it's understand by many developers. That is, indices are
used BEFORE rows are read to construct a (sparse) bitmap of relevant
rows, not as part of actual lookup (i.e. get a row from left, take a
value and look it up in index then go for row in right set and so on, how
index lookup loop are usually constructed). Keyword NATURAL in plan means
that no index would be used to narrow the set, so all rows are evaluated.
NATURAL may be an outcome of filter condition absence, absence of index
that match the condition or bad characteristics of matching index.

Of course, indices could also be used to read rows in the order of index,
but that's related to order by/group by, not joins and where clause (in
plan you'll see it as ORDER instead INDEX).

So the contribution of index in Firebird to speed up data retrieval is in
possibility to identify relevant rows beforehand (with less I/O
operations) and then go just for relevant data pages, not in the speed of
search in index itself. What really matter for speed is the total number
of pages read. With joins it come into play also the size of all data
sets in pages (including intermediate products) and the order in which
they are processed (smaller are better on the right - as pages of right
set are usually accessed multiple times). What may look faster is not
always really faster when you count the I/O operations needed. Of course,
optimizer works only with approximate numbers, false numbers (outdated
index statistics) and pure speculations (size of intermediate products).
It was also broken and used bogus plans for some conditions, but Arno
fixed a lot of them.

Best regards
Pavel Cisar

See you at the First European Firebird Conference in May in Fulda,
Germany
http://www.firebird-conference.com

http://www.ibphoenix.com
For all your upto date Firebird and
InterBase information