I have trouble with indexes during 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 can see, so as by section, we use desc. I've an climbing down index, but my query plan doesn't utilize it. It just use index Table A (a.objid) and Table B (b.item_id, b.wh_id) Can there be something i skipped? What index do you consider must i create?

Index for Table A (mm_itrghd)

(TR_CODE, DOC_ID) Climbing (OBJID) Climbing (TR_CODE) Climbing (POSTDATE) Climbing (POSTDATE, OBJID) Climbing (POSTDATE, OBJID) Climbing down

Index for Table B (mm_itrgdt)

(HEADER_ID) Climbing (ITEM_ID) Climbing (WH_ID) Climbing (LOT_NO) Climbing (SERIAL_NO, ITEM_ID) Climbing (HEADER_ID, ITEM_ID, WH_ID, SERIAL_NO, LOT_NO) Climbing (HEADER_ID, ITEM_ID, WH_ID) Climbing (CALCTYP, OBJID) Climbing (ITEM_ID, WH_ID) Climbing (CALCTYP, OBJID, ITEM_ID, WH_ID) Climbing (CALCTYP, OBJID) Climbing down (OBJID, ITEM_ID, WH_ID) Climbing down (OBJID) Climbing down

Thanks ahead of time

Regards, Reynaldi

To begin with update indices statistics because Firebird relay onto it when selecting what index to make use of and more. Either do backup-restore cycle for database or execute following code:

EXECUTE BLOCK
AS
  DECLARE VARIABLE IDX VARCHAR(31);
BEGIN
  FOR
    SELECT rdb$index_name FROM rdb$indices
    WHERE NOT rdb$index_name LIKE 'RDB$%'
    INTO :idx
  DO BEGIN
    EXECUTE STATEMENT 'update statistics ' || :idx
    WITH AUTONOMOUS TRANSACTION; 
  END
END

Look into the query plan next. If index sits dormant for the reason that Firebird thinks that it is usage is going to do more damage then help. You may either specify arrange for query by hand or attempt to rewrite it.

Inside your situation you are able to eliminate OR condition using UNION operator:

select 
  a.postdate, 
  a.objid, 
  b.calctyp, 
  b.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') 

union all

select 
  a.postdate, 
  a.objid, 
  b.calctyp, 
  b.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.objid<50000)

order by 
  1 desc, 2 desc, 3 desc, 4 desc

It is simply a stomach feeling, but attempt to also choose b.objid