Hmm, this means that you've found one area where Firebird 1.5.2 is
worse than 1.5.1 (I've never understood why Firebird wants to use
additional indexes when it already uses a unique or primary key).

First, I would like you to remove one LEFT, there's no point in using
a LEFT JOIN and then refer to the right table within the WHERE clause
(excepting checking a field for NULL or wanting to confuse/restrict
the optimizer):

SELECT .....
FROM MBPAYMENT D
JOIN MBPAYMENTDATA P ON D.PAYMENTKEY=P.PAYMENTKEY //here
LEFT JOIN MBUSERPAYMENT U ON U.PAYMENTKEY=P.PAYMENTKEY
LEFT JOIN MBPROVIDERITEM L ON L.PROVIDERITEMKEY=P.PROVIDERITEMKEY
WHERE STATUS=1 AND (PAYTYPE=1 OR PAYTYPE>2)

This might be enough to change your plans, and for 1.5.2 to do a
better choice. If not, do one further change:
WHERE STATUS=1 AND (PAYTYPE+0=1 OR PAYTYPE>2)

HTH,
Set

--- In firebird-support@yahoogroups.com, "transactive_chris" wrote:
> Query:
>
> SELECT .....
> FROM MBPAYMENT D
> LEFT JOIN MBPAYMENTDATA P ON D.PAYMENTKEY=P.PAYMENTKEY
> LEFT JOIN MBUSERPAYMENT U ON U.PAYMENTKEY=P.PAYMENTKEY
> LEFT JOIN MBPROVIDERITEM L ON L.PROVIDERITEMKEY=P.PROVIDERITEMKEY
> WHERE STATUS=1 AND (PAYTYPE=1 OR PAYTYPE>2)
>
> Plans:
>
> 1.5.1
> PLAN JOIN (JOIN (JOIN (D INDEX (IDX_PAYMENT_1),P INDEX
> (RDB$PRIMARY102)),U INDEX (IDX_USERPAYMENT_1)),L INDEX
> (RDB$PRIMARY130))
>
> 1.5.2
> PLAN JOIN (JOIN (JOIN (D INDEX (IDX_PAYMENT_1),P INDEX
> (RDB$PRIMARY102,IDX_PAYMENTDATA_4,IDX_PAYMENTDATA_4)),U INDEX
> (IDX_USERPAYMENT_1)),L INDEX (RDB$PRIMARY130))
>
>
> It appears 1.5.2 adds "IDX_PAYMENTDATA_4,IDX_PAYMENTDATA_4" within
> the P INDEX. IDX_PAYMENTDATA_4 isn't even needed in 1.5.1 and it is
> used twice in the P Index in 1.5.2.
>
> IDX_PAYMENTDATA_4 is an Index on PAYTYPE.