Hi

We have been using Firebird 1.5.1 in our test environment and have had
an issue with an sql that used to take 20 seconds now taking 1.5
hours. The same plan is being reported as in 1.5.0.

Some information about the tables.
UnmatchedTransactions has 200,000 records and no indexes.
InvProd has 6,000 records and InvProdRef is it's primarykey
InvTran has about 4,000,000 records. The Index it is using is called
INTERBATCH and is a composite index of ITBatchRef, INVHEADRef,
INVPRODREf and TranType. In this case only the ITBatchRef field would
be used in the join.

SELECT
IMS.InvTranRef, IMS.TranAmount,
IMS.TranType, IMS.TranDate, IMS.InvProdRef ,
IMS.TranSign,
SUM(InvTran.Units * InvTran.TranSign *
(CASE WHEN IMS.TranType = 'PURC' THEN -1 ELSE 1 END)) as
TA_AdjAmount,
(COALESCE(SUM(InvTran.Units * InvTran.TranSign *
(CASE WHEN IMS.TranType = 'PURC' THEN -1 else 1 END)), 0) +
IMS.TranAmount) *
IMS.TranSign as TA_TotalAmount
FROM UnmatchedTransactions IMS

INNER JOIN InvProd on IMS.InvProdRef = InvProd.InvProdRef AND
InvProd.ProdType in ('DSHR', 'INVT', 'DFIX', 'GILT', 'RGHT',
'OPTN', 'BANK')

LEFT OUTER JOIN InvTran ON InvTran.ITBatchRef = IMS.ITBatchRef AND
InvTran.InvProdRef = 'IP00000002' AND
InvTran.TranType in ('BROK', 'STMP', 'PFIT', 'SFIT', 'PTML',
'BRPC')

WHERE
IMS.TranType IN ('SALE', 'PURC', 'RTSL')

GROUP BY IMS.InvTranRef, IMS.TranAmount,
IMS.TranType, IMS.TranDate, IMS.InvProdRef, IMS.TranSign

In 1.5.0 the plan is
PLAN SORT (JOIN (JOIN (IMS NATURAL,INVPROD INDEX (PK_INVPROD)),INVTRAN
INDEX (INTERBATCH)))
The plan analyzer says the following number of records are returned.
UnmatchedTransactions 200,000 (Non indexed)
InvProd 30,000 (Indexed)
InvTran 12,000 (Indexed)

In 1.5.1 the plan is the same
After waiting 1.5 hours the Plan analyzer returns the following number
of records.
UnmatchedTransactions 200,000 (Non Indexed)
InvProd 200,000 (Indexed)
InvTran 1,200,000,000 (i.e. 1.2 billion records) (Indexed)

If in 1.5.1 we change the sql to
SELECT
IMS.InvTranRef, IMS.TranAmount,
IMS.TranType, IMS.TranDate, IMS.InvProdRef ,
IMS.TranSign,
SUM(InvTran.Units * InvTran.TranSign *
(CASE WHEN IMS.TranType = 'PURC' THEN -1 ELSE 1 END)) as
TA_AdjAmount,
(COALESCE(SUM(InvTran.Units * InvTran.TranSign *
(CASE WHEN IMS.TranType = 'PURC' THEN -1 else 1 END)), 0) +
IMS.TranAmount) *
IMS.TranSign as TA_TotalAmount
FROM UnmatchedTransactions IMS

INNER JOIN InvProd on IMS.InvProdRef = InvProd.InvProdRef AND
InvProd.ProdType in ('DSHR', 'INVT', 'DFIX', 'GILT', 'RGHT',
'OPTN', 'BANK')
and IMS.TranType IN ('SALE', 'PURC', 'RTSL')

LEFT OUTER JOIN InvTran ON InvTran.ITBatchRef = IMS.ITBatchRef AND
InvTran.InvProdRef = 'IP00000002' AND
InvTran.TranType in ('BROK', 'STMP', 'PFIT', 'SFIT', 'PTML',
'BRPC')

GROUP BY IMS.InvTranRef, IMS.TranAmount,
IMS.TranType, IMS.TranDate, IMS.InvProdRef, IMS.TranSign

This case still has the same plan but takes 20 seconds and returns the
following hits. I have moved the where clause into the first join.
UnmatchedTransactions 200,000 (non indexed)
InvProd 200,000 (Indexed)
InvTran 12,000 (Indexed)

This is not as good as the 1.5.0 version which has less hits on the
InvProd table.

Any help or explaination of why/how things have changed would be very
useful.

Thanks

Bernard