--- In firebird-support@yahoogroups.com, "emb_blaster"
<EMB_Blaster@...> wrote:

>
> Hi all,
>
> I understand that sometimes, an indexed search can be slowest than
> natural. But I still don´t understand why this in every case.
> for example:
>
> I had TABLE_A with columns:
> PK_CODE - primary key
> FK_CODE1- fk
> FK_CODE2- another fk
> ... others columns
>
> so query:
>
> Select * from TABLE_A;
>
> or query:
>
> Select PK_CODE, FK_CODE1- fk from TABLE_A;
>
> result this Plan
> PLAN (TABLE_A NATURAL)
> Prepare time = 0ms
> Execute time = 15ms
> Avg fetch time = 0,83 ms
>
> First Question here... a why Select doesn´t use a index? it is because
> there´s no where and/or join clause, or because optimizier decided
> that natural is fastest, or another thing?

You need to remind yourself what an index actually is. An index is a
data structure holding an ordered set of the indexed field(s). There
is nothing magical about them. They take up space on the disk and
require the dbms to keep them up to date with changes.

But if you have a condition in your query (eg a where clause or join
condition), the dbms can read the index for the appropriate field, and
(if the index is good) significantly narrow down the amount of data
that must be read from the hard disk by eliminating irrelevant records.

Looking at your query, there is no condition. So no matter what plan
is selected, all of the records must be read from the hard disk.

Let me ask you what is faster?

* Reading every record from the hard disk in storage order
* Reading every record from the hard disk in storage order AND reading
the index from the hard disk.

Of course the first option would be faster (this is called NATURAL in
your plan)

>
> .assuming PK_CODE is > 0
>
> then Query:
> Select PK_CODE, FK_CODE1- fk from TABLE_A
> where PK_CODE >0;
>
> result Plan:
> PLAN (TABLE_A INDEX (RDB$PRIMARY29))
>
> Prepare time = 0ms
> Execute time = 16ms
> Avg fetch time = 0,89 ms
>
> there are second question is... why?

Optimisation is not a clean science. Your goal is to reduce the time a
query takes to run. Part of the time a query takes to run is to work
out what the fastest way to run the query would be. Obviously, you
don't want to spend a lot of time in this step or it would defeat the
whole purpose. They are called trade-offs.

Maybe in an ideal world, Firebird should have recognised that your
query condition would not benefit from the index, but one can imagine
there is additional time for the optimiser to spend in estimating each
path if it checked this. Some dbms do keep histograms for each index
that could be used to determine this particular index; while useful
for some queries, would be useless here. Firebird does not keep
histograms, and consequently it can not differentiate between the
usefulness of a unique index depending on parameter values.

And for most of the values one would usually use for ? in the query
below, the index would be very useful.

Select PK_CODE, FK_CODE1- fk from TABLE_A
where PK_CODE >?;

Adam