At 04:36 PM 12/05/2009, you wrote:

>Thanks for the reply, Helen.
>
>I thought Firebird converts correlated subquery to JOINs when executing. I
>may be wrong.

You are wrong. Although you can usually write a JOIN query that produces a similar output set to a logically equivalent correlated subquery search, the logic path is quite different.

>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.

You're assuming that the output from the subquery is a multi-record set. It's not. The correlated subquery is run for each different match-value in the outer query, and your match criteria are complex, viz. not (matching (=a or (=b (or something else))))

>I thought Firebird
>only executes from outside to inside when doing subqueries. If that's true,
>then filtering before subquery makes sense.

It's true for non-correlated subqueries. Correlated subqueries can be great but they need to be used with caution.

>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.

The misunderstanding here seems to be that your misconceptions about the logic of correlated subqueries and NOTed searches. In this case, my guess is that you can improve this query logic by using outer joins and using the WHERE clause to include only members that have null outer values - with the proviso that you provide the missing indexes, of course.

./hb