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.


--- In firebird-support@yahoogroups.com, "Chris Endacott" <chris@t...>
wrote:

> Hi,
>
> I have an optimization concern with the 1.5.2.4731 Firebird Release.

I have

> tested the same query on 1.0.3, 1.5.1.4481, and 1.5.2.4731.
>
> * Variables in the WHERE clauses are all indexed Integers.
> * I consider that each of the WHERE Clauses tested below are equivelent
> statements.
>
> Here are my results...
>
> "WHERE STATUS=1 AND (PAYTYPE=1 OR PAYTYPE>2)"
> 1.0.3 Execution Time: 175 ms Fetch Time: 187 ms
> 1.5.1 Execution Time: 109 ms Fetch Time: 144 ms
> 1.5.2 Execution Time: 39840 ms Fetch Time: 26361 ms ***
>
> "WHERE STATUS=1 AND (PAYTYPE=1 OR PAYTYPE>=3)"
> 1.0.3 Execution Time: 176 ms Fetch Time: 188 ms
> 1.5.1 Execution Time: 134 ms Fetch Time: 99 ms
> 1.5.2 Execution Time: 2712 ms Fetch Time: 1854 ms ***
>
> "WHERE STATUS=1 AND (PAYTYPE!=2)"
> 1.0.3 Execution Time: 174 ms Fetch Time: 190 ms
> 1.5.1 Execution Time: 141 ms Fetch Time: 95 ms
> 1.5.2 Execution Time: 96 ms Fetch Time: 81 ms
>
> I have attached the results from InterBase PLANalyzer 1.0.0.47
>
> The whole query is:
> 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 ... (as shown above)
>
>
> The number of reads for each join is very different for 1.5.2.4731. But
> they are the same for 1.0.3 and 1.5.1.
>
> Regards,
>
> Chris
> TransActive Systems
>
> --
> Outgoing mail has been virus checked but TransActive Systems do not

warrant

> that this email is free of viruses that may have passed through our anti
> virus software undetected.
> Checked by AVG Anti-Virus.
> Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date:

6/09/2005

>
>
>
>
> [Non-text portions of this message have been removed]