Subject Re: [firebird-support] Reads inconsistency between FB TraceManager and IB Planalyser
Author setysvar
>> Also, if I remove the order by, IP reports a drastic reduction is
>> execution time i.e. down to 6msec. Does the ORDER BY on the PK make
such a
>> difference?
>Unh, maybe. Between the list, my mail system and yours, I've lost
most of the formatting and can't look at the original while typing

My guess would rather be that the ORDER BY forces Fb to fetch all rows,
whereas without ORDER BY only the first few (random) rows are fetched,
and that the time for fetching all rows rather than the first row are

>You seem to use left outer joins and that can be a problem because (in
general) the order in which outer joins are declared in the query is the
>order in which they have to be accessed in the query plan.

Sure, LEFT JOIN limits the optimizer. However, the WHERE clause only
refers to the TILD (main) table, and the three LEFT JOINs seem to have a
function similar to subselects used to find lookup values (the last
three fields in the view). Hence, I'm pretty certain the optimizer would
have chosen a very similar plan if LEFT had been deleted (it could of
course have reordered the three "lookup tuples", but they're not related
anyway (they each join to the TILD table, not eachother), so the same
indexes should be used.