On Thu, 1 Feb 2007 10:16:32 +0100, "Ivan Prenosil" <Ivan.Prenosil@...> wrote:

>> I'm facing serious speed issues with FB2.0, where the optimizer
>> follows a different path than the one used by FB1.5.
>
> but later it states that:
>
>> Both the 1.5 and the 2.0 optimizers select a NATURAL scan of the table.
>
> So, are the plans the same or different ?

They are different: I wasn't clear enough in saying that my *real* case is of course a little bit more convoluted than the one I sampled. In particular, the effective procedure loops over a SELECT which joins in two other SPs, like

SELECT a.id, b.foo, c.bar, ...
FROM table a
LEFT JOIN someproc(a.somefield, ...) b ON 1=1
LEFT JOIN otherproc(a.otherfield, ...) c ON 1=1

The generated PLAN is similar: under 2.0 I see a few more items related to the joined procedures, which 1.5 instead does not show. As said, given my WHERE clauses, both engines uses a NATURAL scan on the main table, but 2.0 executes the procedures *many* *many* more times than 1.5.

This morning I was able to repeat my tests against latest 2.0.1rc, and with pleasure I saw that it is indeed much better than 2.0, even slightly better than 1.5!

>
> Then there is written:
>
>> As soon as I heard about 2.0.1 (and in particular about
>> http://tracker.firebirdsql.org/browse/CORE-1029), I retried my
>> attempts on that version as well, but nothing changed.
>
> which points to bug "Bad plan in outer joins with IS NULL clauses",
> but in the attached script there are no outer joins, in fact
> there are no joins at all.
>
>> FOR SELECT what
>> FROM testbes tb
>> WHERE tb.id = :p_id
>> AND tb.dat = :p_dat
>> INTO what DO
>> SUSPEND;

Yes, I know there's no JOIN involved, and again I'm afraid I wasn't good at explaining the reference: this msg and the sample code I attached was to clarify the point with the NATURAL scan when there's a condition like "(:p_id IS NULL) or (table.id = :p_id)". But this is just one face of the problem. As said above, in the real scenario, the procedure does (did) something like that PLUS joining in two other SPs: 2.0.1 (maybe thanks to #1029, maybe other fixes) behaves much better on it, as it executes the procedure just a dozen times (ie, once for every selected record) and not thousands of times as 2.0 does.

Thank you (and svet) for the explanation,
bye, lele.