Kjell Rilbe wrote:

> Aage Johansen wrote:
>>I would start by "removing" the index on either F.FTGSTAT or F.USTAT, _and_
>>either A.AESTAT or A.AETYP. An index on A.ORGNR might be a good thing, and
>>both A.AESTAT or A.AETYP could be "removed" (depends on selectivity for
>>A.ORGNR).
>
>
> Thanks, it worked. Exec time dropped from 21 hours to 34 seconds. (I
> already had ORGNR indices if you look carefully.)

I see, A_FK_ORGNR.

> I must say that one of the most frustrating aspects of Firebird is its
> unability to generate an appropriate plan for this kind of queries. It
> goes against all sanity that (read) query performance would *increase*
> by *removing* indices. (Assuming you're a "regular" user who doesn't
> know the inner workings of the DB engine.)

Sometimes the Fb way works fine, at other times less so.
Other databases allow/need "hints" on what to do (or not). Fb sometimes
needs help as well.

> As I understand it, the problem is that it will repeat the subquery for
> each record in the outer query and that the low index selectivity of the
> subquery indices causes these subqueries to execute rather sluggishly.

Maybe not quite. I think Fb is waisting time on indexes which add less
than nothing to performance.

> But I don't really understand what the problem is. The engine knows how
> the outer query and the subquery are correlated. So it should be able to
> run the subquery once ignoring the correlating conditions, order the
> result set on the correlated columns, and do some kind of single-pass
> loop. What am I missing?

A "not exists (select 1 ...)" is usually fast enough if you don't tempt Fb
into suboptimality...


--
Aage J.