---------- Original Message -----------
From: Dmitry Yemanov <dimitr@...>

> This is known as a nested loop join. And "restart from the root" costs
> just a couple of page reads that are likely to be satisfied using the
> cache. Not something really wasteful.
> 2) Streams are read in the index order that allows to avoid an extra
> sorting. But navigating the whole tables in the index order will cause
> extremely random I/O (at least for the FK driven table) so it's also
> going to be expensive. Firebird never chooses such an option at all.

------- End of Original Message -------

Thank you for the insight on performance considerations; it helps!

select 1 from bt_dchronexpl inner join bt_ref on bt_ref.file_num =
bt_dchronexpl.filenumber order by bt_dchronexpl.FILENUMBER asc;
--> PLAN JOIN (BT_DCHRONEXPL ORDER IX_DCHRONEXPL_FILENUM, BT_REF INDEX
(IX_BT_REF_FILE_NUM_ASC))

I gather you're saying it won't use the index on the driving table in the absence
of a compelling reason, but a group-by or order-by might cause it to do an
indexed read, though that's unrelated to the join proper; it just avoids a final
SORT in temporary space, but it still performs the join through a nested-loop
algorithm. Two indices in a PLAN don't make a merge-sort.

-Philip