At 14:50 02.07.2003 +0200, you wrote:

>Created the indices and ran the query again. Here's the plan the optimizer
>uses :
>
>PLAN SORT (SORT (MERGE (SORT (S NATURAL),SORT (JOIN (C NATURAL,L INDEX
>(IDX_POSTYPE))))))

Hm, that is not the plan it should create. The two indexes I suggested
ought to have changed this to something like (ignoring all sorts and merges):

L INDEX (IDX_POSTYPE), S INDEX(IDX_CLNT_SUB_CODE), C INDEX(IDX_CLIENT_CODE)

which should be about as fast as you can get it.

But wait, I think I can guess why it uses the indexes it does. InterBase
was known for messing up plans if you had duplicate indexes, and you have
several (I don't know how the Firebird optimizer compares in this regard,
but this is my prime suspicion)!

E.g.

CREATE INDEX "IDX_POSTYPE" ON "LOG"("POS_ID", "TRAN_TYPE"); includes the index
CREATE INDEX "IDX_POSID" ON "LOG"("POS_ID");

Please DROP INDEX IDX_POSID;

Also DROP INDEX IDX_TRANTYPE;

and consider reordering one of the indexes of both pairs

IDX_DRYSTOCKSALES/IDX_TRANDATA
IDX_POSTYPE/IDX_TAX

The thing to learn from this is to try to make it easy for your optimizer
to make its optimization, something your CREATE INDEX statements indicate
that you have tried to avoid (and avoid helping the optimizer is asking for
trouble).

Set