Hi Jerome, and thanks for a good problem description!

There are several things you could try to hint the optimizer. In your
query, it would be something like

and pres.valuedate+0 < '2004-06-01'

to get rid of any possibility for using the PRESENCE_IDX1 index
(assuming it is ascending), and then

inner join presence pres on (L1.id+0 = pres.outletid)
inner join sys_idlists_idx L2 on (pres.status = L2.id+0)

to eliminate the SYS_IDLISTS_IDX_IDX3 for L1 and L2.

You could also change the index SYS_IDLISTS_IDX_IDX1 to (groupid, id)
to make it more selective.

HTH,
Set

--- In firebird-support@yahoogroups.com, "Jerome Bouvattier" wrote:
> Hi,
>
> I finally found the reason of my random slowdowns. It appears the
> optimizer do not always choose the same plan for the very same
> query. I have no clue as to what triggers those "changes of mind" in
> either way (for the faster or the slower).
> I'd really appreciate your help.
>
> The query :
>
> select distinct pres.productid
> from sys_idlists_idx L1
> inner join presence pres on (L1.id = pres.outletid)
> inner join sys_idlists_idx L2 on (pres.status = L2.id)
> where
> L1.groupid = 180089 and L2.groupid = 180091 and
> pres.valuedate >= '2004-03-01'
> and pres.valuedate < '2004-06-01'
> and pres.saleprice + 0 > 0
>
> The fast PLAN :
>
> PLAN SORT (JOIN (L1 INDEX (SYS_IDLISTS_IDX_IDX1),L2 INDEX
> (SYS_IDLISTS_IDX_IDX1),PRES INDEX (PRESENCE_IDX3)))
>
> The slow PLAN :
>
> PLAN SORT (JOIN (PRES INDEX (PRESENCE_IDX1),L2 INDEX
> (SYS_IDLISTS_IDX_IDX3),L1 INDEX (SYS_IDLISTS_IDX_IDX3)))
>
> Here PRESENCE_IDX1 indices VALUE_DATE and is very poorly selective.
> So, I removed it to see the results. And again the optimizer
> hesitates between the good plan which starts by evaluating the
> sys_idlists_idx table first (holding a few records) and the
> bad one, which starts by the *huge* "presence" table.
>
> Without the poor index the slow PLAN becomes :
> PLAN SORT (JOIN (PRES NATURAL,L2 INDEX (SYS_IDLISTS_IDX_IDX3),
> L1 INDEX (SYS_IDLISTS_IDX_IDX3)))
> (which is even more slow)
>
> The question is how can I hint the optimizer so that it starts to
> evaluate (inner) JOINs from the right end (the one I choose) every
> time. Note that a LEFT JOIN won't give me the same results here.
>
> Many many thanks in advance.
>
> --
> Jerome