Well, excepting that I always prefer to use aliases rather than table names, I wouldn't say there's anything directly wrong with your SQL, Michael. There is, however, one minor error in your email - you don't say which of the queries are quick and which is slow.

I'd say the optimizer is left with a tough choice. Either it has to choose decent selectivity for TRANSAKTIONER at the cost of choosing NATURAL for VARER, or it chooses the TRANS_DATO index for TRANSAKTIONER and the optimal index for VARER. Which is better depends on the value of DATO, and the optimizer has no idea whether that part of the query is selective or not (histograms doesn't exist yet in Firebird) - so it guesses.

If you prefer PLAN SORT (JOIN (VARER NATURAL, TRANSAKTIONER INDEX (TRANS_VAREFRVSTRNR))), then I suspect that

INNER JOIN VARER ON TRANSAKTIONER.VAREFRVSTRNR = VARER.PLU_NR+0

will help the optimizer choose the plan you want. If you prefer PLAN SORT (JOIN (TRANSAKTIONER INDEX (TRANS_DATO), VARER INDEX (RDB$PRIMARY26))), then I'd try

INNER JOIN VARER ON TRANSAKTIONER.VAREFRVSTRNR+0 = VARER.PLU_NR

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of mivi71dk
Sent: 22. november 2010 12:48
To: firebird-support@yahoogroups.com
Subject: [firebird-support] SQL and PLAN

Hi

I have a DB server running Firebird 2.1.1.
On this server there is 2 Databases.

Preparing this SQL:

Select
transaktioner.Afdeling_ID as AfdNr,
sum (transaktioner.salgstk) as Antal,
sum (transaktioner.Salgkr) as Spr,
sum (transaktioner.KostPr) as Kpr
from transaktioner
INNER JOIN VARER ON TRANSAKTIONER.VAREFRVSTRNR = VARER.PLU_NR AND VARER.SERVICEYDELSE=0
where
transaktioner.art in (14,70,71) and
transaktioner.Dato>='2010-11-01 23:59:59' and
transaktioner.Pakkelinje IN (1,5)
group by
transaktioner.Afdeling_ID

in both of them returns different plans.

In 1 I get

PLAN SORT (JOIN (VARER NATURAL, TRANSAKTIONER INDEX (TRANS_VAREFRVSTRNR)))
(Selectivity: 0.000050904)

In the other I get

PLAN SORT (JOIN (TRANSAKTIONER INDEX (TRANS_DATO), VARER INDEX (RDB$PRIMARY26)))
(Selectivity: 0.000236239)


The difference is the usage of RDB$PRIMARY26 on table VARER.
And it makes a hell of a lot of a difference using it or not!

The index is a VARCHAR(30) field called PLU_NR in table VARER.
The selectivity of this field is listed above.

I have just executed SET STATISTICS last night on all index.
An today several times on this index, and I can see the selectivity changes when I do.

If I recall correctly the lower the number in selectivity means the better the index is.
But above the index is choosen with a higher number and omitted with a lower number.

Is this a badly written SQL which I could change in some way to make Firebird use this index every time?

Regards
Michael