Assuming all the joins to be pretty selective and bill.service_id the
same, I would have attempted to get a plan like

PLAN JOIN (BILL INDEX (SERVICE_ID_INDEX), PMNT INDEX (PMNT_ID_INDEX),
P INDEX (PARTY_ID_INDEX))

This can be done by preventing the indexes from being used, e.g. using
where (pmnt.BILLING_DATE +0 BETWEEN ... AND ...)

Why having your index with SERVICE_ID OR PAYMENT_ID first makes that
much of a difference, I still do not think we have enough information
to answer. It may be because one of them is quite selective whereas
the other one isn't, or because you have a separate index on either of
the fields confusing the optimizer. The only general answer I can
give, is that since the index is the last index in your plan it may
greatly affect the execution speed (which you have already discovered)
. In many cases I think it is sensible to not use composite indexes,
or at least limit their use to indexing one field + the PK to avoid
duplicate entries. Though I do not have enough experience (I generally
work against only a couple of databases, and my experience may not be
appropriate for others with a different design) to know the pros and
cons with composite indexes.

Set

--- In firebird-support@yahoogroups.com, Alexander Tabakov wrote:
> 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