>Hi all,
>
>I have a problem with indexes in my firebird query.
>
>Below is my query.
>
>select a.objid, b.running_qty, b.running_qty2, b.running_totalcost, b.running_lastcost
>from mm_itrghd a, mm_itrgdt b where (a.objid=b.header_id) and
>(b.item_id=1200) and (b.wh_id=1) and
>((a.postdate<'2010-09-05 00:00:00') or ((a.postdate='2010-09-05 00:00:00') and (a.objid<50000)))
>order by a.postdate desc, a.objid desc, b.calctyp desc, b.objid desc
>
>As you see, in order by section, we use desc. I have an descending index, but my query plan does not use it.
>It only use index Table A (a.objid) and Table B (b.item_id, b.wh_id)
>Is there something i missed? What index do you think should i create?
>
>Index for Table A (mm_itrghd)
>
>(TR_CODE, DOC_ID) Ascending
>(OBJID) Ascending
>(TR_CODE) Ascending
>(POSTDATE) Ascending
>(POSTDATE, OBJID) Ascending
>(POSTDATE, OBJID) Descending
>
>Index for Table B (mm_itrgdt)
>
>(HEADER_ID) Ascending
>(ITEM_ID) Ascending
>(WH_ID) Ascending
>(LOT_NO) Ascending
>(SERIAL_NO, ITEM_ID) Ascending
>(HEADER_ID, ITEM_ID, WH_ID, SERIAL_NO, LOT_NO) Ascending
>(HEADER_ID, ITEM_ID, WH_ID) Ascending
>(CALCTYP, OBJID) Ascending
>(ITEM_ID, WH_ID) Ascending
>(CALCTYP, OBJID, ITEM_ID, WH_ID) Ascending
>(CALCTYP, OBJID) Descending
>(OBJID, ITEM_ID, WH_ID) Descending
>(OBJID) Descending

Hi Reynaldi,
indexes aren't magical solutions to performance problems, sometimes using an index speed up things, other times it slows things down. Looking at your index definition, I'd say you've created too many indexes. Myself, I generally prefer single field indexes, using multi-field indexes is a bit faster on certain queries, but it also complicates things and Firebird is pretty good at using several indexes for a table when required. Also, things vary between Firebird versions, until recently Firebird (2.1 or 2.5?) didn't know the selectivity of individual fields of an index, and quite some time ago (Firebird 1.0?) Firebird was slow to delete/update if an index contained lots of duplicates. Moreover, the optimizer used to get into problems when duplicate indexes were created. Generally, you never want to create an index that simply consists of the beginning of another index.

I'd ask myself the following before creating an index.

Is the field selective?
Is it used for equality or between operations in WHERE or JOIN clauses?

If the answer to one of these questions is NO, then I'd normally not create an index. From your description above, I'd consider dropping at least the following indexes (and if some fields aren't selective, I'd drop more indexes):

Table A (mm_itrghd)
(TR_CODE, DOC_ID) Ascending - drop it if there is an index or key for DOC_ID
(TR_CODE) Ascending - drop it if there isn't an index or key for DOC_ID
(POSTDATE, OBJID) Ascending

Table B (mm_itrgdt)
(CALCTYP, OBJID, ITEM_ID, WH_ID) Ascending
(HEADER_ID, ITEM_ID, WH_ID, SERIAL_NO, LOT_NO) Ascending
(HEADER_ID, ITEM_ID, WH_ID) Ascending
(ITEM_ID, WH_ID) Ascending

If you want to change the plan of your query, you may try changing to:

select a.objid, b.running_qty, b.running_qty2, b.running_totalcost, b.running_lastcost
from mm_itrghd a
join mm_itrgdt b on a.objid=b.header_id
where b.item_id=1200
and b.wh_id=1
and a.postdate<='2010-09-05 00:00:00'
and (a.postdate<'2010-09-05 00:00:00'
or a.objid<50000)
order by a.postdate desc, a.objid desc, b.calctyp desc, b.objid desc

However, I'd say your original plan seems good, indexes are most useful for selecting data, not for ordering a result set that presumably only contain a small portion of the tables accessed (using an additional index for the order by would probably slow down the execution of the query).

HTH,
Set