This seems strange, prefer NATURAL when an index can be used... you don't by any chance have defined both a foreign key and an index that is identical (that at least used to be able to confuse the optimizer, albeit I haven't heard of such cases the last few years)? I take it that the selectivity of the indexes on the troublesome computer is similar to the others, and that e.g. the selectivity for R.BASE_SN isn't lousy?

Try

SELECT DISTINCT BR.BASE_SN, R.TITLE
FROM BASERES BR
LEFT JOIN RES R ON BR.BASE_SN = R.BASE_SN
WHERE R.STATUS=1
AND BR.STATUS=1
AND BR.BASE_SN IN (8,71,99,130,310,356,426,529,537,689,
19736,19792,19917,19929,20089,20094,
20108,20110,20115,20191)

I hope this will force the following plan:

PLAN SORT (PLAN JOIN (BR INDEX
(PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES,
PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES,
PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES,
PK_BASERES,PK_BASERES), R INDEX(FK_RESOURCE_REF_2064_BASERESO)))

By the way, I expect the plan above to already be used by the 'quick' computers, I'm just trying to force the plan on the 'slow' computer (R.STATUS = 1 in the WHERE clause contradicts LEFT JOIN, making the result identical to the [INNER] JOIN, but with less options available for the optimizer).

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of accdev3234
Sent: 31. mars 2009 05:45
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Performance decrease after running SET INDEX STATISTICS


SQL> SET PLANONLY;

SQL> SELECT DISTINCT BR.BASE_SN, R.TITLE
CON> FROM RES R
CON> JOIN BASERES BR ON BR.BASE_SN = R.BASE_SN AND
CON> BR.BASE_SN IN
(8,71,99,130,310,356,426,529,537,689,19736,19792,19917,19929,20089,20094\
,20108,20110,20115,20191) AND BR.STATUS=1
CON> WHERE
CON> R.STATUS=1;

gave the following plan:

PLAN SORT (JOIN (R NATURAL,BR INDEX
(PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES,PK_BA\
SERES,PK_BASERES,PK_BASERES,PK_BASERES,
PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES,PK_BAS\
ERES,PK_BASERES,PK_BASERES,PK_BASERES,PK_BASERES)))