I think we need more information to answer this one, i.e. sql
statement, chosen plan and some information about how many
duplicates/matching records are expected for PAYMENT_ID and
SERVICE_ID.

A general answer would just be that if the query was something like
SELECT * FROM TBL_PAYMENT
WHERE SERVICE_ID > 0 AND PAYMENT_ID = 25545435
then the second index would most likely be inferior to the first since
PAYMENT_ID was checked for a unique value whereas SERVICE_ID had lots
of duplicates matching your requirement.

Basically, just follow common sense when creating compound indexes. If
I were trying to do something like

SELECT * FROM PERSON
WHERE FIRSTNAME STARTING 'A'
AND SURNAME = 'TABAKOV'

then the index (SURNAME, FIRSTNAME) would be far more suitable than
(FIRSTNAME, SURNAME), simply because there are fewer persons with the
surname TABAKOV than there are persons with a firstname starting with
A. Checking all records with a firstname starting A for the surname
Tabakov, naturally would take considerably more time than checking all
records with the surname Tabakov for a firstname starting A.

HTH,
Set

--- In firebird-support@yahoogroups.com, Alexander Tabakov wrote:
> Hi guys,
>
> I have a table TBL_PAYMENT. There is a compound index defined on
> (PAYMENT_ID, SERVICE_ID). When performing a query using SERVICE_ID
> in the WHERE clause execution time is about 30 ms which is perfectly
> OK.
>
> But, after changing the index to be (SERVICE_ID, PAYMENT_ID)
> execution time of the same query became: 3 minutes and 30 sec.
>
> Both times the plan was one and the same (I mean the optimizer
> choose the same index to use).
>
> I use Firebird 1.0.3 on Win2K box.
>
> Any ideas about the difference in execution speed?
> Are there any rules on field ordering in a compound key?