I would guess that CA_OTHER_PAYMENT_SUB INDEX (FK_CA_OTHER_PAYMENT_SUB_1,
CA_OTHER_PAYMENT_SUB_IDX1) is the problem with query 2. Which of these are the least selective? Try adding '+0' to the least selective of CA_OTHER_PAYMENT_SUB.COMP_ID AND CA_OTHER_PAYMENT_SUB.OTHERPAY_CODE and hopefully your query will speed up.

Another thing is that if COMP_ID is the primary key of CA_OTHER_PAYMENT, then you would not want to use any other index on that table. However, I think Fb 2.0 is intelligent enough to figure this out by itself, so I guess that you have a composite primary key or something?

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of rungsant
Sent: 25. oktober 2007 09:37
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Why Adapted Plan "NATURAL" used time less than "INDEX" ?

Execute time of Query 1 < Query 2.

Database is Firebird 2.0.1


Query 1
-------

SELECT
CA_OTHER_PAYMENT_SUB.ITEM_CODE AS ACCOUNT_CODE
,M_ACC_CHART.ACCOUNT_NAME
,CA_OTHER_PAYMENT.OTHERPAY_DATE AS DOC_DATE
,SUM(CA_OTHER_PAYMENT_SUB.ITEM_AMOUNT) AS
ACCOUNT_AMOUNT
FROM CA_OTHER_PAYMENT
INNER JOIN CA_OTHER_PAYMENT_SUB ON
CA_OTHER_PAYMENT.COMP_ID||CA_OTHER_PAYMENT.OTHERPAY_CODE
=CA_OTHER_PAYMENT_SUB.COMP_ID||CA_OTHER_PAYMENT_SUB.OTHERPAY_CODE
INNER JOIN M_ACC_CHART ON
CA_OTHER_PAYMENT_SUB.COMP_ID||CA_OTHER_PAYMENT_SUB.ITEM_CODE
=M_ACC_CHART.COMP_ID||M_ACC_CHART.ACCOUNT_CODE
WHERE
CA_OTHER_PAYMENT.COMP_ID = '1'
AND CA_OTHER_PAYMENT.OTHERPAY_DATE BETWEEN '01-SEP-2007'
AND '30-SEP-2007'

GROUP BY 1,2,3

Plan
----
PLAN SORT (MERGE (SORT (CA_OTHER_PAYMENT INDEX
(CA_OTHER_PAYMENT_IDX1, PK_CA_OTHER_PAYMENT))
, SORT (MERGE (SORT (M_ACC_CHART NATURAL), SORT
(CA_OTHER_PAYMENT_SUB NATURAL)))))

Adapted Plan
------------
PLAN SORT (MERGE (SORT (CA_OTHER_PAYMENT INDEX
(CA_OTHER_PAYMENT_IDX1, PK_CA_OTHER_PAYMENT))
, SORT (MERGE (SORT (M_ACC_CHART NATURAL), SORT
(CA_OTHER_PAYMENT_SUB NATURAL)))))



Query 2
-------

SELECT
CA_OTHER_PAYMENT_SUB.ITEM_CODE AS ACCOUNT_CODE
,M_ACC_CHART.ACCOUNT_NAME
,CA_OTHER_PAYMENT.OTHERPAY_DATE AS DOC_DATE
,SUM(CA_OTHER_PAYMENT_SUB.ITEM_AMOUNT) AS
ACCOUNT_AMOUNT
FROM CA_OTHER_PAYMENT
INNER JOIN CA_OTHER_PAYMENT_SUB ON
CA_OTHER_PAYMENT.COMP_ID=CA_OTHER_PAYMENT_SUB.COMP_ID
AND
CA_OTHER_PAYMENT.OTHERPAY_CODE=CA_OTHER_PAYMENT_SUB.OTHERPAY_CODE
INNER JOIN M_ACC_CHART ON
CA_OTHER_PAYMENT.COMP_ID=M_ACC_CHART.COMP_ID
AND
CA_OTHER_PAYMENT_SUB.ITEM_CODE=M_ACC_CHART.ACCOUNT_CODE
WHERE
CA_OTHER_PAYMENT.COMP_ID = '1'
AND CA_OTHER_PAYMENT.OTHERPAY_DATE BETWEEN '01-SEP-2007'
AND '30-SEP-2007'
GROUP BY 1,2,3

Plan
----
PLAN SORT (JOIN (CA_OTHER_PAYMENT INDEX
(CA_OTHER_PAYMENT_IDX1, PK_CA_OTHER_PAYMENT)
, M_ACC_CHART INDEX (PK_M_ACC_CHART),
CA_OTHER_PAYMENT_SUB INDEX (FK_CA_OTHER_PAYMENT_SUB_1,
CA_OTHER_PAYMENT_SUB_IDX1)))

Adapted Plan
------------
PLAN SORT (JOIN (CA_OTHER_PAYMENT INDEX
(CA_OTHER_PAYMENT_IDX1, PK_CA_OTHER_PAYMENT)
, M_ACC_CHART INDEX (PK_M_ACC_CHART),
CA_OTHER_PAYMENT_SUB INDEX (FK_CA_OTHER_PAYMENT_SUB_1,
CA_OTHER_PAYMENT_SUB_IDX1)))