Hi Set !

Thanks for the help.


From: "Svein Erling Tysvær" <svein.erling.tysvaer@...>

>
> 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.

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 ?

>
> 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);


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" ;-).

Regards.

--
Jerome

>
> 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
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>