Subject RE: [IB-Architect] order by not using index
Author Dmitry Yemanov
Dmitry and Sean,

> The reason that the index is not used is that generally speaking it is
> more efficient to navigate a table in the natural order and then sort
> the results, then to use the index and then hop back-and-forth through
> the table. The natural order approach involves less disk I/O and
> therefore is a faster approach.

I would disagree. Actually it depends on a variety of things. With ORDER
plan an index is scanned sequentially and each data page is read many times
to construct a result set, and often it's considered to have bad
performance. But all reads can be done from the cache (if available) and
IMHO it's not a problem to have many page reads. But in the case of really
big datasets (especially with long varchar fields which are expanded to
their full size on disk) external temporary file sorting may involve much
more disk I/O than scanning the index.

This question was already discussed a long while ago and I believe there's
no right answer is it good to perform natural scan or not.

In my case (one production database) a query like the following:

from table1 a left join table2
where a.field1 = b.field1
order by 1;

is executed in 100 times slower than the one that follows now:

(select field1 from table2 where field1 = a.field1),
(select field2 from table2 where field1 = a.field1),
(select field3 from table2 where field1 = a.field1)
from table1 a
order by 1;

The second statement uses indices in both table1 and table2 to order, the
first does not. Such a trick allowed me to finish and successfully sell one
of my projects.

Just an example.