git.net

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

VolcanoPlanner changes Joins structure only once


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 the
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 the table "A" as a the "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 only one step towards optimal
solution applying JoinPushThroughJoinRule.RIGHT, but can't reach the
optimal solution when it is required to apply JoinPushThroughJoinRule.RIGHT
twice.

Are there any known VolcanoPlanner limitations that could be related to
this behavior?
-- 
Best regards,
Anton.