Satheesh Bandaram wrote:
Army wrote:
So if we have a subquery, in which case childResult will (or least can)
be a SelectNode, the fact that SelectNode is NOT an instance of Optimizable
means that this method will return false and the optimizer won't ever
consider a hash join when the inner table is a subquery.
Do you have a sample query that you can share? Is your case subqueries
in Select list or subquery in WHERE clause?
Take as an example the following:
CREATE TABLE "APP"."T1" ("I" INTEGER, "A" INTEGER);
CREATE TABLE "APP"."T2" ("J" INTEGER, "B" INTEGER);
CREATE TABLE "APP"."T3" ("K" INTEGER, "C" INTEGER);
insert into t1 values (1, -1), (2, -2), (4, -4);
insert into t2 values (3, -3), (4, -4), (5, -5);
insert into t3 values (4, -4), (5, -5), (6, -6), (7, -7), (99, -99);
Now run:
select t1.i, x1.c from t1 inner join (select * from t2, t3) x1 on x1.j = t1.i;
I thought subqueries in Select list are converted initially to
FromSubQuery, which is optimizable?
As you say, the subquery in the outer SELECT is originally parsed as a
FromSubquery; however, in the "preprocess" method of FromSubquery.java, a call
is made to the "extractSubquery()", which turns the FromSubquery into a
ProjectRestrictNode on top of a SelectNode. The leading comments for
extractSubquery are as follow:
/**
* Extract out and return the subquery, with a PRN on top.
* (See FromSubquery.preprocess() for more details.)
...
So in the above case, by the time the optimizer is considering a Hash join, our
optimizable is a ProjectRestrictNode with a child that's a SelectNode, which
means the "isMaterializable()" method of ProjectRestrictNode will return false,
and thus the optimizer won't ever consider doing a hash join on the subquery.
That said, it seems reasonable to think that the optimizer should at least
consider doing a hash join on the subquery, in which case the join between T2
and T3 could be materialized and then a hash-join could be done using the
predicate x1.j = t1.i.
That's the theory, anyways; feel free to let me know if I'm missing
something...?
Army
