|Subject||RE: [firebird-support] Suboptimal plan|
> In a select statement there are two tables joined by a single matchingOld/invalid index statistics.
> field, with search conditions in both of them. One of them has roughly
> 130000 records, while the other has 600.
> Inspecting the plan generated by the optimizer, I realized that it uses the
> shorter one as the controlling stream instead of the longer one (i.e. the
> shorter table is at the left side).
> Using a PLAN expression with the corresponding indexes to bypass the
> plan deviced by the optimizer in order to switch the order of the streams, the
> performance is boosted (nearly three times faster).
> What can cause the optimizer to pick a shorter table as the controlling
> stream ?
> Is there a workaround to induce the optimizer to select the streams in aIf you can provide the PLAN and SELECT (old and optimized) we can offer some suggestions.
> fixed way or should I have to resort to a manually imposed PLAN ?