[teiid-issues] [JBoss JIRA] (TEIID-3842) Teiid's query's explain not consistence with other Database
Kylin Soong (JIRA)
issues at jboss.org
Mon Nov 23 22:35:00 EST 2015
[ https://issues.jboss.org/browse/TEIID-3842?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kylin Soong updated TEIID-3842:
-------------------------------
Description:
h2. The way to get execution plan is not consistence with others
For most of RDBMS(Oracle, Mysql), the execution plan get via SQL query
{code}
explain select ...
{code}
But in Teiid, we need use Teiid API, and need enable showplan in advance:
{code}
statement.execute("set showplan on");
ResultSet rs = statement.executeQuery("select ...");
TeiidStatement tstatement = statement.unwrap(TeiidStatement.class);
PlanNode queryPlan = tstatement.getPlanDescription();
System.out.println(queryPlan);
{code}
Cache Hint can be added as prefix, so I think we can enhance that show plan also as prefix, in order for consistence with others.
h2. The plan structure is too complex
[1] is the query plan of dynamicvdb-datafederation which is 2 datasources' federation, it long, complex, for the users/customers, it's not easy for them to understand. So can we enhance that simplify the output, like add a format option that can output as matrix/table.
[1] dynamicvdb-datafederation's 'select * from Stock' query plan
{code}
ProjectNode
+ Relational Node ID:0
+ Output Columns:
0: product_id (integer)
1: symbol (string)
2: price (bigdecimal)
3: company_name (string)
+ Statistics:
0: Node Output Rows: 9
1: Node Next Batch Process Time: 0
2: Node Cumulative Next Batch Process Time: 12
3: Node Cumulative Process Time: 21
4: Node Next Batch Calls: 3
5: Node Blocks: 2
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
JoinNode
+ Relational Node ID:1
+ Output Columns:
0: ID (integer)
1: symbol (string)
2: price (bigdecimal)
3: COMPANY_NAME (string)
+ Statistics:
0: Node Output Rows: 9
1: Node Next Batch Process Time: 4
2: Node Cumulative Next Batch Process Time: 12
3: Node Cumulative Process Time: 21
4: Node Next Batch Calls: 3
5: Node Blocks: 2
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
JoinNode
+ Relational Node ID:2
+ Output Columns:
0: symbol (string)
1: price (bigdecimal)
+ Statistics:
0: Node Output Rows: 10
1: Node Next Batch Process Time: 2
2: Node Cumulative Next Batch Process Time: 7
3: Node Cumulative Process Time: 7
4: Node Next Batch Calls: 2
5: Node Blocks: 1
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
ProjectNode
+ Relational Node ID:3
+ Output Columns:file (clob)
+ Statistics:
0: Node Output Rows: 1
1: Node Next Batch Process Time: 0
2: Node Cumulative Next Batch Process Time: 2
3: Node Cumulative Process Time: 2
4: Node Next Batch Calls: 1
5: Node Blocks: 0
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
AccessNode
+ Relational Node ID:4
+ Output Columns:
0: file (clob)
1: filePath (string)
+ Statistics:
0: Node Output Rows: 1
1: Node Next Batch Process Time: 2
2: Node Cumulative Next Batch Process Time: 2
3: Node Cumulative Process Time: 2
4: Node Next Batch Calls: 1
5: Node Blocks: 0
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:EXEC MarketData.getTextFiles('*.txt')
+ Model Name:MarketData
+ Select Columns:MarketData.getTextFiles.file
+ Child 1:
TextTableNode
+ Relational Node ID:5
+ Output Columns:
0: symbol (string)
1: price (bigdecimal)
+ Statistics:
0: Node Output Rows: 10
1: Node Next Batch Process Time: 3
2: Node Cumulative Next Batch Process Time: 3
3: Node Cumulative Process Time: 3
4: Node Next Batch Calls: 2
5: Node Blocks: 1
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Table Function:TEXTTABLE(f.file COLUMNS symbol string, price bigdecimal HEADER) AS SP
+ Join Strategy:NESTED TABLE JOIN
+ Join Type:CROSS JOIN
+ Join Criteria
+ Child 1:
AccessNode
+ Relational Node ID:6
+ Output Columns:
0: SYMBOL (string)
1: ID (integer)
2: COMPANY_NAME (string)
+ Statistics:
0: Node Output Rows: 25
1: Node Next Batch Process Time: 1
2: Node Cumulative Next Batch Process Time: 1
3: Node Cumulative Process Time: 17
4: Node Next Batch Calls: 5
5: Node Blocks: 4
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.SYMBOL AS c_0, g_0.ID AS c_1, g_0.COMPANY_NAME AS c_2 FROM Accounts.PRODUCT AS g_0 ORDER BY c_0
+ Model Name:Accounts
+ Join Strategy:ENHANCED SORT JOIN RAN AS SORT MERGE (SORT/ALREADY_SORTED)
+ Join Type:INNER JOIN
+ Join Criteria:SP.symbol=A.SYMBOL
+ Select Columns:
0: A.ID AS product_id
1: SP.symbol
2: SP.price
3: A.COMPANY_NAME AS company_name
+ Data Bytes Sent:0
+ Planning Time:128
{code}
was:
h2. The way to get execution plan is not consistence with others
For most of RDBMS(Oracle, Mysql), the execution plan get via SQL query
{code}
explain select ...
{code}
But in Teiid, we need use Teiid API, and need enable showplan in advance:
{code}
statement.execute("set showplan on");
ResultSet rs = statement.executeQuery("select ...");
TeiidStatement tstatement = statement.unwrap(TeiidStatement.class);
PlanNode queryPlan = tstatement.getPlanDescription();
System.out.println(queryPlan);
{code}
Cache Hint can be added as prefix, so I think we can enhance that show plan also as prefix, in order for consistence with others.
h2. The plan structure is too complex
[1] is the query plan of dynamicvdb-datafederation which is 2 datasources' federation, it long, complex, for the users/customers, it's not easy for then to understand. So can we enhance that simplify the output, like add a format option that can output as matrix/table.
[1] dynamicvdb-datafederation's 'select * from Stock' query plan
{code}
ProjectNode
+ Relational Node ID:0
+ Output Columns:
0: product_id (integer)
1: symbol (string)
2: price (bigdecimal)
3: company_name (string)
+ Statistics:
0: Node Output Rows: 9
1: Node Next Batch Process Time: 0
2: Node Cumulative Next Batch Process Time: 12
3: Node Cumulative Process Time: 21
4: Node Next Batch Calls: 3
5: Node Blocks: 2
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
JoinNode
+ Relational Node ID:1
+ Output Columns:
0: ID (integer)
1: symbol (string)
2: price (bigdecimal)
3: COMPANY_NAME (string)
+ Statistics:
0: Node Output Rows: 9
1: Node Next Batch Process Time: 4
2: Node Cumulative Next Batch Process Time: 12
3: Node Cumulative Process Time: 21
4: Node Next Batch Calls: 3
5: Node Blocks: 2
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
JoinNode
+ Relational Node ID:2
+ Output Columns:
0: symbol (string)
1: price (bigdecimal)
+ Statistics:
0: Node Output Rows: 10
1: Node Next Batch Process Time: 2
2: Node Cumulative Next Batch Process Time: 7
3: Node Cumulative Process Time: 7
4: Node Next Batch Calls: 2
5: Node Blocks: 1
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
ProjectNode
+ Relational Node ID:3
+ Output Columns:file (clob)
+ Statistics:
0: Node Output Rows: 1
1: Node Next Batch Process Time: 0
2: Node Cumulative Next Batch Process Time: 2
3: Node Cumulative Process Time: 2
4: Node Next Batch Calls: 1
5: Node Blocks: 0
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
AccessNode
+ Relational Node ID:4
+ Output Columns:
0: file (clob)
1: filePath (string)
+ Statistics:
0: Node Output Rows: 1
1: Node Next Batch Process Time: 2
2: Node Cumulative Next Batch Process Time: 2
3: Node Cumulative Process Time: 2
4: Node Next Batch Calls: 1
5: Node Blocks: 0
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:EXEC MarketData.getTextFiles('*.txt')
+ Model Name:MarketData
+ Select Columns:MarketData.getTextFiles.file
+ Child 1:
TextTableNode
+ Relational Node ID:5
+ Output Columns:
0: symbol (string)
1: price (bigdecimal)
+ Statistics:
0: Node Output Rows: 10
1: Node Next Batch Process Time: 3
2: Node Cumulative Next Batch Process Time: 3
3: Node Cumulative Process Time: 3
4: Node Next Batch Calls: 2
5: Node Blocks: 1
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Table Function:TEXTTABLE(f.file COLUMNS symbol string, price bigdecimal HEADER) AS SP
+ Join Strategy:NESTED TABLE JOIN
+ Join Type:CROSS JOIN
+ Join Criteria
+ Child 1:
AccessNode
+ Relational Node ID:6
+ Output Columns:
0: SYMBOL (string)
1: ID (integer)
2: COMPANY_NAME (string)
+ Statistics:
0: Node Output Rows: 25
1: Node Next Batch Process Time: 1
2: Node Cumulative Next Batch Process Time: 1
3: Node Cumulative Process Time: 17
4: Node Next Batch Calls: 5
5: Node Blocks: 4
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.SYMBOL AS c_0, g_0.ID AS c_1, g_0.COMPANY_NAME AS c_2 FROM Accounts.PRODUCT AS g_0 ORDER BY c_0
+ Model Name:Accounts
+ Join Strategy:ENHANCED SORT JOIN RAN AS SORT MERGE (SORT/ALREADY_SORTED)
+ Join Type:INNER JOIN
+ Join Criteria:SP.symbol=A.SYMBOL
+ Select Columns:
0: A.ID AS product_id
1: SP.symbol
2: SP.price
3: A.COMPANY_NAME AS company_name
+ Data Bytes Sent:0
+ Planning Time:128
{code}
> Teiid's query's explain not consistence with other Database
> ------------------------------------------------------------
>
> Key: TEIID-3842
> URL: https://issues.jboss.org/browse/TEIID-3842
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Affects Versions: 9.x
> Reporter: Kylin Soong
> Assignee: Kylin Soong
> Fix For: 9.x
>
>
> h2. The way to get execution plan is not consistence with others
> For most of RDBMS(Oracle, Mysql), the execution plan get via SQL query
> {code}
> explain select ...
> {code}
> But in Teiid, we need use Teiid API, and need enable showplan in advance:
> {code}
> statement.execute("set showplan on");
> ResultSet rs = statement.executeQuery("select ...");
> TeiidStatement tstatement = statement.unwrap(TeiidStatement.class);
> PlanNode queryPlan = tstatement.getPlanDescription();
> System.out.println(queryPlan);
> {code}
> Cache Hint can be added as prefix, so I think we can enhance that show plan also as prefix, in order for consistence with others.
> h2. The plan structure is too complex
> [1] is the query plan of dynamicvdb-datafederation which is 2 datasources' federation, it long, complex, for the users/customers, it's not easy for them to understand. So can we enhance that simplify the output, like add a format option that can output as matrix/table.
> [1] dynamicvdb-datafederation's 'select * from Stock' query plan
> {code}
> ProjectNode
> + Relational Node ID:0
> + Output Columns:
> 0: product_id (integer)
> 1: symbol (string)
> 2: price (bigdecimal)
> 3: company_name (string)
> + Statistics:
> 0: Node Output Rows: 9
> 1: Node Next Batch Process Time: 0
> 2: Node Cumulative Next Batch Process Time: 12
> 3: Node Cumulative Process Time: 21
> 4: Node Next Batch Calls: 3
> 5: Node Blocks: 2
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> JoinNode
> + Relational Node ID:1
> + Output Columns:
> 0: ID (integer)
> 1: symbol (string)
> 2: price (bigdecimal)
> 3: COMPANY_NAME (string)
> + Statistics:
> 0: Node Output Rows: 9
> 1: Node Next Batch Process Time: 4
> 2: Node Cumulative Next Batch Process Time: 12
> 3: Node Cumulative Process Time: 21
> 4: Node Next Batch Calls: 3
> 5: Node Blocks: 2
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> JoinNode
> + Relational Node ID:2
> + Output Columns:
> 0: symbol (string)
> 1: price (bigdecimal)
> + Statistics:
> 0: Node Output Rows: 10
> 1: Node Next Batch Process Time: 2
> 2: Node Cumulative Next Batch Process Time: 7
> 3: Node Cumulative Process Time: 7
> 4: Node Next Batch Calls: 2
> 5: Node Blocks: 1
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> ProjectNode
> + Relational Node ID:3
> + Output Columns:file (clob)
> + Statistics:
> 0: Node Output Rows: 1
> 1: Node Next Batch Process Time: 0
> 2: Node Cumulative Next Batch Process Time: 2
> 3: Node Cumulative Process Time: 2
> 4: Node Next Batch Calls: 1
> 5: Node Blocks: 0
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Child 0:
> AccessNode
> + Relational Node ID:4
> + Output Columns:
> 0: file (clob)
> 1: filePath (string)
> + Statistics:
> 0: Node Output Rows: 1
> 1: Node Next Batch Process Time: 2
> 2: Node Cumulative Next Batch Process Time: 2
> 3: Node Cumulative Process Time: 2
> 4: Node Next Batch Calls: 1
> 5: Node Blocks: 0
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Query:EXEC MarketData.getTextFiles('*.txt')
> + Model Name:MarketData
> + Select Columns:MarketData.getTextFiles.file
> + Child 1:
> TextTableNode
> + Relational Node ID:5
> + Output Columns:
> 0: symbol (string)
> 1: price (bigdecimal)
> + Statistics:
> 0: Node Output Rows: 10
> 1: Node Next Batch Process Time: 3
> 2: Node Cumulative Next Batch Process Time: 3
> 3: Node Cumulative Process Time: 3
> 4: Node Next Batch Calls: 2
> 5: Node Blocks: 1
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Table Function:TEXTTABLE(f.file COLUMNS symbol string, price bigdecimal HEADER) AS SP
> + Join Strategy:NESTED TABLE JOIN
> + Join Type:CROSS JOIN
> + Join Criteria
> + Child 1:
> AccessNode
> + Relational Node ID:6
> + Output Columns:
> 0: SYMBOL (string)
> 1: ID (integer)
> 2: COMPANY_NAME (string)
> + Statistics:
> 0: Node Output Rows: 25
> 1: Node Next Batch Process Time: 1
> 2: Node Cumulative Next Batch Process Time: 1
> 3: Node Cumulative Process Time: 17
> 4: Node Next Batch Calls: 5
> 5: Node Blocks: 4
> + Cost Estimates:Estimated Node Cardinality: -1.0
> + Query:SELECT g_0.SYMBOL AS c_0, g_0.ID AS c_1, g_0.COMPANY_NAME AS c_2 FROM Accounts.PRODUCT AS g_0 ORDER BY c_0
> + Model Name:Accounts
> + Join Strategy:ENHANCED SORT JOIN RAN AS SORT MERGE (SORT/ALREADY_SORTED)
> + Join Type:INNER JOIN
> + Join Criteria:SP.symbol=A.SYMBOL
> + Select Columns:
> 0: A.ID AS product_id
> 1: SP.symbol
> 2: SP.price
> 3: A.COMPANY_NAME AS company_name
> + Data Bytes Sent:0
> + Planning Time:128
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
More information about the teiid-issues
mailing list