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

>
>
> 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.
> Ok, I'm wrong for the expression. Thanks for clearing this up.
>


>
> >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 understand that the criteria inside the subquery is not simple. But why
> does this lead the optimiser to think that going to a subquery that involves
> another table before going throughth outer table and eliminating as many
> records as possible based on the exising filters is a better option?
>
> In the original query I had, there are five ANDed simple filters on the
> outer table, one for one column, none of them has index though. Only less
> than 0.1% records actually satisfy these conditions. I agree that this is
> not a good idea and if it was me I would have put some indices on and
> therefore I won't have the problem I'm having now.
>
> It's easy to *fix* the query, but my main focus here is to work out how the
> new optimiser thinks in a different way. How as you said its "intelligence"
> is improved? After all, there are existing production queries like the one I
> have that may not be very well designed in the first place but have been
> working properly for quite some time. Then all the sudden it slows down
> dramatically just because I've updated to a smarter optimiser, this just
> discomforts me.
>
> I found that if I remove the (or excludedfile.filename = '*') bit, then 2.1
> runs like 2.0. So my guess here is that because excludedfile.filename has an
> index, so the optimiser thinks by applying this bit first, it may eliminate
> more records than applying the simple outer filters.
>
> But I would argue that since it does a natual scan to the outer table
> anyway, why can't it just apply the filters while it's already there before
> going to a subquery. If it passes the outer simple criteria, then goes to
> subquery, there will be no overhead. If it doesn't, then we save the hussle
> to go to the subquery and another table.
>
> Also, the inner table is very small compare to the outer one. So even if
> the inner indexed filter can do something, it's not comparative to the outer
> filters.
>
>

















> >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.
>
> Does this mean it can go from the inner table to outer table in correlated
> qubqueries?
>
>
>
>
> ./hb
>
>
>

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