You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Nir Pedhazur <ni...@amdocs.com> on 2016/10/20 04:29:49 UTC
Infinite loop during query planning
Hi,
We have been encountering issues resulting with endless query planning phases in VolcanoPlanner when running queries involving (7+) large number of joins. Below is a sample dump of a relevant logical plan -
40:LogicalProject(ref_id=[$11], parent_id=[$10], title=[$12])
39:LogicalFilter(condition=[AND(=(CAST($6):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), =(CAST($6):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($15):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), =(CAST($10):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($16):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), =(CAST($10):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($20):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), =(CAST($23):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($19):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), =(CAST($23):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($32):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), =(CAST($10):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($33):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), <>(CAST($23):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($6):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), <>(CAST($27):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($10):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), =(CAST($7):BIGINT, 0), =(CAST($3):VARCHAR(13) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", 'BLG_CUST_TYPE'))])
38:LogicalJoin(condition=[true], joinType=[inner])
36:LogicalJoin(condition=[true], joinType=[inner])
34:LogicalJoin(condition=[true], joinType=[inner])
32:LogicalJoin(condition=[true], joinType=[inner])
30:LogicalJoin(condition=[true], joinType=[inner])
28:LogicalJoin(condition=[true], joinType=[inner])
26:LogicalJoin(condition=[true], joinType=[inner])
24:LogicalTableScan(table=[[crm, table_hgbst_lst]])
25:LogicalTableScan(table=[[crm, table_hgbst_show]])
27:LogicalTableScan(table=[[crm, table_hgbst_elm]])
29:LogicalTableScan(table=[[crm, mtm_hgbst_elm0_hgbst_show1]])
31:LogicalTableScan(table=[[crm, mtm_hgbst_elm0_hgbst_show1]])
33:LogicalTableScan(table=[[crm, table_hgbst_show]])
35:LogicalTableScan(table=[[crm, table_hgbst_elm]])
37:LogicalTableScan(table=[[crm, mtm_hgbst_elm0_hgbst_show1]])
Seeing https://issues.apache.org/jira/browse/CALCITE-349, https://issues.apache.org/jira/browse/CALCITE-302 we have been trying to activate the heuristic join optimizer to avoid these issues but have not been successful so far (either through Hook.PROGRAM.add (resulting in NullPointerExceptions) or through FrameworkConfig which didn't have effect on prepare phase which unless a hook is used uses that the standard sequence of programs).
Is it possible to setup Calcite to reliably handle these types of queries ? Any assistance will be welcome.
Thanks,
Nir
This message and the information contained herein is proprietary and confidential and subject to the Amdocs policy statement,
you may review at http://www.amdocs.com/email_disclaimer.asp
Re: Infinite loop during query planning
Posted by Julian Hyde <jh...@apache.org>.
The short answer is “yes”. It is possible to set up heuristic join ordering (that’s what Hive uses Calcite, for instance). And yes, that is the right approach to take; the exhaustive approach requires an exponential number of rule firings, so is not practical for “big” joins.
I’m not sure of the exact details for how to invoke heuristic join ordering. Did you try running the relevant tests?
Julian
> On Oct 19, 2016, at 9:29 PM, Nir Pedhazur <ni...@amdocs.com> wrote:
>
> Hi,
>
> We have been encountering issues resulting with endless query planning phases in VolcanoPlanner when running queries involving (7+) large number of joins. Below is a sample dump of a relevant logical plan -
>
> 40:LogicalProject(ref_id=[$11], parent_id=[$10], title=[$12])
> 39:LogicalFilter(condition=[AND(=(CAST($6):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), =(CAST($6):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($15):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), =(CAST($10):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($16):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), =(CAST($10):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($20):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), =(CAST($23):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($19):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), =(CAST($23):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($32):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), =(CAST($10):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($33):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), <>(CAST($23):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($6):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), <>(CAST($27):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", CAST($10):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), =(CAST($7):BIGINT, 0), =(CAST($3):VARCHAR(13) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary", 'BLG_CUST_TYPE'))])
> 38:LogicalJoin(condition=[true], joinType=[inner])
> 36:LogicalJoin(condition=[true], joinType=[inner])
> 34:LogicalJoin(condition=[true], joinType=[inner])
> 32:LogicalJoin(condition=[true], joinType=[inner])
> 30:LogicalJoin(condition=[true], joinType=[inner])
> 28:LogicalJoin(condition=[true], joinType=[inner])
> 26:LogicalJoin(condition=[true], joinType=[inner])
> 24:LogicalTableScan(table=[[crm, table_hgbst_lst]])
> 25:LogicalTableScan(table=[[crm, table_hgbst_show]])
> 27:LogicalTableScan(table=[[crm, table_hgbst_elm]])
> 29:LogicalTableScan(table=[[crm, mtm_hgbst_elm0_hgbst_show1]])
> 31:LogicalTableScan(table=[[crm, mtm_hgbst_elm0_hgbst_show1]])
> 33:LogicalTableScan(table=[[crm, table_hgbst_show]])
> 35:LogicalTableScan(table=[[crm, table_hgbst_elm]])
> 37:LogicalTableScan(table=[[crm, mtm_hgbst_elm0_hgbst_show1]])
>
> Seeing https://issues.apache.org/jira/browse/CALCITE-349, https://issues.apache.org/jira/browse/CALCITE-302 we have been trying to activate the heuristic join optimizer to avoid these issues but have not been successful so far (either through Hook.PROGRAM.add (resulting in NullPointerExceptions) or through FrameworkConfig which didn't have effect on prepare phase which unless a hook is used uses that the standard sequence of programs).
>
> Is it possible to setup Calcite to reliably handle these types of queries ? Any assistance will be welcome.
>
> Thanks,
> Nir
>
>
>
> This message and the information contained herein is proprietary and confidential and subject to the Amdocs policy statement,
> you may review at http://www.amdocs.com/email_disclaimer.asp