Subject Re: [firebird-support] Suboptimal plan
Author Aldo Caruso

  thanks for your clues !
  I certain circumstances the first one could change the result set, as it changes an inner join to an outer join.
  On the other hand, the second one is really good an prevents the optimizer to use the index on the long table, as you said.


El 01/08/14 a las 18:29, Alexandre Benson Smith iblist@... [firebird-support] escibiĆ³:

Hi !

There is some tricks to change the way optimizer will choose the order of tables:

   LongTable L left join
   ShortTable S on (S.FieldA = L.FieldA)

The outer join would force the table on the left to be scaned first.

Another option is
   LongTable L join
   ShortTable S on (S.FieldA = L.FieldA+0)

the +0 (or || '' if the field is a char/varchar) would prevent the optimizer to use the index on LongTable.FieldA and could lead to change the join order

see you !

Em 1/8/2014 18:09, Aldo Caruso aldo.caruso@... [firebird-support] escreveu:
Sean and Carlos,

  thanks for your answer.

  Although the statistics are up to date the optimizer still chooses the shorter stream as the controller.

  Nevertheless, I found a workaround using a subquery on the shorter table instead of a join.

  This forced the optimizer to traverse the longer table first and boosts the performance even more than using an optimized query with a fixed plan.

    Thank you very much for your help.


El 01/08/14 a las 16:01, 'Carlos H. Cantu' listas@... [firebird-support] escibiĆ³:

Are the indexes statistics up to date?

Also, post the query, query plan, and indexes stats.

Firebird Performance in Detail - -

ACacacfs> Hello,

ACacacfs> In a select statement there are two tables joined by a single
ACacacfs> matching field, with search conditions in both of them. One of them has
ACacacfs> roughly 130000 records, while the other has 600.

ACacacfs> Inspecting the plan generated by the optimizer, I realized that it
ACacacfs> uses the shorter one as the controlling stream instead of the longer one
ACacacfs> (i.e. the shorter table is at the left side).

ACacacfs> Using a PLAN expression with the corresponding indexes to bypass
ACacacfs> the plan deviced by the optimizer in order to switch the order of the
ACacacfs> streams, the performance is boosted (nearly three times faster).

ACacacfs> What can cause the optimizer to pick a shorter table as the
ACacacfs> controlling stream ?

ACacacfs> Is there a workaround to induce the optimizer to select the streams
ACacacfs> in a fixed way or should I have to resort to a manually imposed PLAN ?

ACacacfs> Thanks,
ACacacfs> Aldo