I've got an odd problem at 1 site. I am running 1.5.5.4926 on a Windows 2003 SP2 server. I run this same combination of software on dozens of different servers with no problems. On this server certain queries run very slowly. Consider the following table:

CREATE TABLE BILLING_IMG_TABLE (
AS$IMAGE_NUMBER INTEGER,
LOCATION VARCHAR(6),
NUMBER INTEGER,
SUFFIX, VARCHAR(2),
IMAGE_TYPE VARCHAR(2),
CONSTRAINT BILLING_PRIMARY PRIMARY KEY (AS$IMAGE_NUMBER));
CREATE INDEX BILLING_PRO_KEY
ON BILLING_IMG_TABLE (LOCATION, NUMBER, SUFFIX, IMAGE_TYPE);

In the worst case, there might be 20 entries for each LOCATION / NUMBER combination in BILLING_PRO_KEY.

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.

Any ideas on what the problem with this one machine might be? Or what I should look for to diagnose the problem.

If I decide to upgrade this machine to 2.0.5, do I need to unload and reload the database? The database is rather large (155GB) and it would take an extremely long time to unload and reload.