The optimizer sees only the filters [A, J]. the join for X is nested under J, but not seen by the optimizer. The permutations built are [A, J] and [J, A], and the Plan constructor calculates for allFilters [A, J, X] and [J, X, A] (with a visitor which add the filters in the order it sees them)
That's why [J, A, X] is not considered. I have assumed that the cost for [J, X, A] is infinite because X needs A, but I haven't digged it yet. Le jeudi 20 septembre 2018 02:19:01 UTC+2, Evgenij Ryazanov a écrit : > > Hello. > > Execution of OUTER joins cannot swap it sides in H2, H2 does not have hash > joins yet. That's why only [A, J, X] and [J, X, A] plans are considered. > For LEFT OUTER join H2 iterates over left side and lookups right side. > Index on PROP(ID, OWNER_PK) may be wanted here if there are many rows with > the same OWNER_PK but different ID value. > > I don't know, however, why H2 evaluates the cost of [J, X, A] as infinite > (with empty tables). It may be a bug in cost calculation (H2 definitely > have some). > > Also I'm not sure why [J, A, X] is not considered (with inner join > between), may be it's not really valid in H2 for the same reason, or may be > it's just a missing feature in optimizer. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
