--- In firebird-support@yahoogroups.com, "Jerome Bouvattier" wrote:

> Hi Set !
>
> Thanks for the help.
> >
> > inner join presence pres on (L1.id+0 = pres.outletid)
> > inner join sys_idlists_idx L2 on (pres.status = L2.id+0)
>
> That was it ! I tried this, but on the first join only. With both,
> the good plan is chosen.

With just one of them, it is simply for the optimizer to change L1 for
L2, and it can still choose a 'bad' plan.

> There is still something I don't quite understand. How come the
> optimizer can hesitate that way ? I find it scary because it makes
> things unpredictable. My app works ok for hours, then crawl, then
> come back to normal, etc... This without much change to data itself.
> The pb once occured right after a restore.
>
> Any ideas ?

I'm great at making the tail of a dog wiggle, but I am a lousy vet. I
know that the optimizer thinks the bad plan is ideal, but the only
solution that I know of that actually may work in understanding why
this is so, is to shout ARNO, help, ARNO ;o}

> > to eliminate the SYS_IDLISTS_IDX_IDX3 for L1 and L2.
>
> It was useful for other purposes, but now that I know how to hint
> the optimizer, this might be an option.
> >
> > You could also change the index SYS_IDLISTS_IDX_IDX1 to (groupid,
> > id) to make it more selective.
>
> It's so already. And it's also unique. The only way I found to make
> the optimizer choose the fastest plan.
>
> SYS_IDLISTS_IDX 's DDL is as follows :
>
> CREATE TABLE SYS_IDLISTS_IDX (
> GROUPID INTEGER NOT NULL,
> ID INTEGER NOT NULL,
> ORD SMALLINT
> );
>
> CREATE UNIQUE INDEX SYS_IDLISTS_IDX_IDX1 ON SYS_IDLISTS_IDX
> (GROUPID, ID);
> CREATE UNIQUE INDEX SYS_IDLISTS_IDX_IDX3 ON SYS_IDLISTS_IDX (ID,
> GROUPID);

Ah, ha! This is probably your problem. Two identical indexes confuse
the optimiser (identical selectivity). Remove ID or GroupID from one
of them or make certain that only one of the fields can be used when
using the indexes.

> FYI, what I'm doing here is to turned the following query.
>
> SELECT distinct PRES.PRODUCTID
> FROM PRESENCE PRES
> WHERE
> PRES.OUTLETID IN (118299,118340,118398,118477)
> and PRES.VALUEDATE >= '2003-09-01'
> and PRES.VALUEDATE < '2003-11-01'
> and PRES.STATUS IN (1,2,3)
>
> .. into a parameterized one. (note the 2 IN predicates). For, that
> I use the SYS_IDLISTS_IDX table which is a persistent temporary
> table, to hold groups of IDs.
>
> This solution beats, by far, anything else I could find including
> the hardcoded IN you see above, and your beloved "EXISTS" ;-).

WHAT??? Have you defeated my EXISTS? Grrr... No, you're right, choose
the right tool for the problem. Even though you're right in that I
love EXISTS, it is not the universal solution to all database problems
and your solution sounds very good. Though I suspect it will end up
like some of the workarounds I have become accustomed to - Arno will
improve the optimiser so that it is no longer neccessary with any
workaround.

Set