>I forgot to put "first 1" on my first post.

OK, the difference between including your ORDER BY and not including it is that with ORDER BY you ask the question 'get me the record that meets these criteria', whereas without ORDER BY you ask the question 'get me a record that meets these criteria'. There's a vast difference between finding a person in a telephone directory with the name SMITH and finding the person named Smith that has the highest telephone number in the directory.

>I did more testing and try these queries
>
>"select col1 from table1 where col2=1 and col3=1 and col4=1 and col5=1"
>
>and
>
>"select col1 from table1 where col2>1 and col3>1 and col4=1 and col5=1"
>
>index1 (col2, col3, col4, col5)
>index2 (col4, col5)
>
>query no 1 uses index no 1
>and query no 2 uses index no 2.
>
>Can you guys tell me what happen?

Suppose you have the following records (in index1 order, only including column2 (C2) and column3 (C3)):

RecNo C2 C3
-----------
1 0 0
2 1 1
3 2 0
4 2 1

For query 1, one could just jump straight to record 2 which matches both C2 and C3.

For query 2, only the C2 part of the query could be of use. Record 3 is between two records of interest despite not fitting the C3 criteria, hence the index is useless for the C3 part of the index. That's one of the reasons that I almost exclusively use single field indexes, if you had

index3 (col2)
index4 (col3)

both indexes could (simultaneously) be used by Firebird.

Back to your original query. For finding the first match, you could try modifying your query like this (if you use a fairly recent Firebird version):

WITH TMP_A AS
(SELECT a.objid
from mm_itrghd a
where a.postdate<='2010-09-05 00:00:00'
and (a.postdate<'2010-09-05 00:00:00'
or a.objid<50000)
and exists(select *
from mm_itrgdt b
where a.objid=b.header_id
and b.item_id=1200
and b.wh_id=1)
order by a.postdate desc, a.objid desc
ROWS 1)

select a.objid, b.running_qty, b.running_qty2, b.running_totalcost, b.running_lastcost
from TMP_A a
join mm_itrgdt b on a.objid=b.header_id
where b.item_id=1200
and b.wh_id=1)
order by b.calctyp desc, b.objid desc

What I've tried to speed up here, is "mm_itrghd a", using EXISTS rather than JOIN could make the optimizer choose your index. It might speed up things if records with item_id=1200 and wh_id=1 for one or more of the latest POSTDATEd records exists. If item_id is rare and only occurs on very old POSTDATEd records, then it could slow things down. On the other hand, I might be wrong and the plan might not change much at all. Try and see whether the performance increases.

HTH,
Set