Stephen Boyd wrote:

> This is now leading me to believe that it isn't that FB is ignoring the BILLING_PRO_KEY index but rather that using the primary key to order the result set that is causing the problem.

It looks so.

> Checking the I/O statistics shows that the PLAN selected by FB requires something in the order of 7K buffer reads, whereas the manually entered PLAN requires only about 8 buffer reads.
>
> Given that the query only returns a single record, how can the FB selected plan be so much more inefficient. There is no way it should require that many I/Os to order a single record.

The reported plan is executed in two steps. First, the index
BILLING_PRO_KEY is scanned to create a bitmap containing the record IDs
matching the search condition. Second, the index BILLING_IMG_TABLE is
scanned sequentially and the every record ID is tried against the
bitmap. Record retrieval is performed only for successful trials.

So, the primary key index is scanned entirely regardless on how many
records do you expect in the output. This is just how the ORDER plan
works (in this case).

> Is this a bug in the optimizer?

It's simply lack of its intelligence. ORDER vs SORT is always a
heuristic decision, not a cost based one. In your case, with a very
selective search condition, it looks obvious to use SORT instead, but
the optimizer is not that clever (yet).


Dmitry