git.net

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Can't apply JoinPushThroughJoinRule.RIGHT twice


Hello!
I have the following problem. Sample SQL input is the following:
SELECT *
FROM X
INNER JOIN A
ON X.id = A.id
INNER JOIN X2
ON X.id = X2.id
INNER JOIN X3
ON X.id = X3.id

According to a custom cost model used, it would beneficial to move select
from the table "A" as high as possible in LogicalJoins tree. So the optimal
solution is to transform the query into the following form (move the table
"A" into the last join making it a right node of a top LogicalJoin):
SELECT *
FROM X
INNER JOIN X2
ON X.id = X2.id
INNER JOIN X3
ON X.id = X3.id
INNER JOIN A
ON X.id = A.id
In order to achieve this, JoinPushThroughJoinRule.RIGHT rule should be
applied twice with table "A" as a "B" node (in terms of rule's source code
comments). However, when trying to optimize the SQL input listed using
VolcanoPlanner, the result is the following:
SELECT *
FROM X
INNER JOIN X2
ON X.id = X2.id
INNER JOIN A
ON X.id = A.id
INNER JOIN X3
ON X.id = X3.id

So while the cost was improved, VolcanoPlanner did only the first step
towards the optimal solution. According to my debugging, there were no
attempts to apply JoinPushThroughJoinRule.RIGHT one more time to this
modified structure with the first step done.
But if using this modified structure as a new input SQL, the optimal result
is achieved.
So looks like VolcanoPlanner is able to make one step towards optimal
solution applying JoinPushThroughJoinRule.RIGHT, but can't reach the
optimal solution when it is required to apply
JoinPushThroughJoinRule.RIGHT  twice in order to achieve it.

Are there any hints regarding this problem?
-- 
Best regards,
Anton.