Removing the Left was enough to change the plan.

From Execution Time: 39840 ms Fetch Time: 26361 ms
To Execution Time: 110 ms Fetch Time: 807 ms

New Plan:
PLAN JOIN (JOIN (JOIN (P INDEX (IDX_PAYMENTDATA_4,IDX_PAYMENTDATA_4),D
INDEX (RDB$PRIMARY101)),U INDEX (IDX_USERPAYMENT_1)),L INDEX
(RDB$PRIMARY130))

Whats interesting to me is it still chose to index on
(IDX_PAYMENTDATA_4,IDX_PAYMENTDATA_4) and removed the Primary index.

Making P Natural by using PAYTYPE+0=1 also worked.

Thanks for your help Svein.


--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

> 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.