Stephen Boyd wrote:

>
> I am issuing the following query:
>
> SELECT *
> FROM BILLING_IMG_TABLE
> WHERE LOCATION = 'XX' AND NUMBER = 123 AND IMAGE_TYPE = 'XX'
> ORDER BY AS$IMAGE_NUMBER;
>
> Firebird generates this plan:
>
> PLAN (BILLING_IMG_TABLE ORDER RDB$PRIMARY17)
>
> I thought myself, Ah Ha! FB isn't using BILLING_PRO_KEY to do the lookup, that's why it is slow. In fact adding:
>
> PLAN (BILLING_IMG_TABLE INDEX (BILLING_PRO_KEY))
>
> to the query speeds it up.
>
> Removing the ORDER BY clause causes FB to use BILLING_PRO_KEY and the query returns almost immediately.
>
> The problem is this:
>
> 1) The original query generates the same plan on every other 1.5.5 machine I have tried it on but there doesn't seem to be a performance hit on any machine but this one.
> 2) Shouldn't FB be generating a plan that includes BILLING_PRO_KEY on its own?
>
> I have tried this same query on a FB 2.0.5 database and it generates a different plan:
>
> PLAN (BILLING_IMG_TABLE ORDER RDB$PRIMARY17 INDEX (BILLING_PRO_KEY))
>
> which is more what I would have expected.

Actually, the real plan is the same in both v1.5 and v2.0. Versions
prior to v2.0 simply cannot report its INDEX part, although execute it.
The real difference is that the INDEX part seems really missing on the
problematic host, hence the performance issue there and lack of problems
on other v1.5 hosts. It cannot be validated visually, but symptoms speak
for this suspicion. The possible reasons are: (1) disabled index
BILLING_PRO_KEY or (2) outdated index statistics.


Dmitry