Thanks for the reply, Helen.

I thought Firebird converts correlated subquery to JOINs when executing. I
may be wrong.

What I don't understand is that why the optimiser doesn't run the filters on
the main table AllFile before it goes to the correlated subquery, as in this
case the subquery doesn't depend on those main filters. I thought Firebird
only executes from outside to inside when doing subqueries. If that's true,
then filtering before subquery makes sense.

I understand that 2.1 has improved the optimiser a lot, but in this
particular case it fails to realise that running those filters before
subquery can be faster, and it seems that it only chooses to do so when
there are indexes available on those filters.

Cheers
Huan

2009/5/12 Helen Borrie <helebor@...>

>
>
> At 02:26 PM 12/05/2009, you wrote:
> >Hi Sean
> >
> >I agree this query is not very well designed, but it's a simplified
> version
> >of a query from one of our applications. I would have put an index on
> >excludedfile.pathname to speed this example up.
> >
> >However, there are many other filters other than allfile.filename =
> '1.TXT'
> >in real life. The point is no matter these filters have index on them or
> >not, the optimiser should apply them to eliminate unwanted records before
> >running the subquery which basically is a JOIN.
>
> No, it's not a JOIN. Your search condition is a correlated subquery and,
> furthermore, it's a NOTed correlated subquery. If you want a JOIN query then
> write it as a JOIN query. Test it (both ways) with typical data to figure
> out which method gets the better performance, since the actual conditions
> will determine the plan heuristically.
>
> And, as Sean said, the heuristics "intelligence" was improved between 2.0
> and 2.1.
>
> ./heLen
>
>
>

[Non-text portions of this message have been removed]