Hello Svein,

>I think we need more information to answer this one, i.e. sql
>statement, chosen plan and

The query:

select ...
from
TBL_PARTY p join TBL_PAYMENT pmnt on (pmnt.PARTY_ID = p.PARTY_ID)
join tbl_bill_payed bill on (pmnt.pmnt_id = bill.pmnt_id)
where
(pmnt.BILLING_DATE BETWEEN ... AND ...) and bill.service_id = :serviceId

Query plan:

PLAN JOIN (PMNT ORDER IDX_PAYMENT_DATE,P INDEX (RDB$PRIMARY26),BILL INDEX (TBL_BILL_PAYED_IDX2));

Where:
IDX_PAYMENT_DATE - index on payment date
RDB$PRIMARY26 - PK index on TBL_PARTY (party_id)
TBL_BILL_PAYED_IDX2 - The index in question! It was either (payment_id,service_id) or (service_id,payment_id)

>information about how many duplicates/matching records are expected for
>PAYMENT_ID and SERVICE_ID.

The query normally selects about 1000 records of 100000

--
Best regards,
Alexander mailto:saho@...