[teiid-issues] [JBoss JIRA] Commented: (TEIID-1566) More complex join: ORDER BY and LIMIT not pushed down
Mark Addleman (JIRA)
jira-events at lists.jboss.org
Mon Apr 25 14:44:18 EDT 2011
[ https://issues.jboss.org/browse/TEIID-1566?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12597817#comment-12597817 ]
Mark Addleman commented on TEIID-1566:
--------------------------------------
Sorry, I missed that field: Version 7.3 plus patches 1492, 1529, 1545, 1554 and 1445. The query plan is below:
0 [main] DEBUG com.ca.chorus.db.DbExecutor - Executing SQL SELECT B."SYSID", B."USERID", J0.APPNAME as "SYSOFUSER at APPNAME", B."NAME", B."PROC", B."SUSPEND", B."UID", (select count(*) from (select * from notes.RETRIEVE_NOTES where OBJECT_PKEY = xmlserialize(xmlelement("SECURITY.BASEUSER", XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM "SECURITY.BASEUSER" as B left join security.sysinfo J0 on J0.sysid=B.sysid ORDER BY J0.APPNAME ASC LIMIT 9990,10; args: []
265720 [main] DEBUG com.ca.chorus.db.DbExecutor - Plan for sql SELECT B."SYSID", B."USERID", J0.APPNAME as "SYSOFUSER at APPNAME", B."NAME", B."PROC", B."SUSPEND", B."UID", (select count(*) from (select * from notes.RETRIEVE_NOTES where OBJECT_PKEY = xmlserialize(xmlelement("SECURITY.BASEUSER", XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM "SECURITY.BASEUSER" as B left join security.sysinfo J0 on J0.sysid=B.sysid ORDER BY J0.APPNAME ASC LIMIT 9990,10:
LimitNode
+ Output Columns:
0: SYSID (string)
1: USERID (string)
2: SYSOFUSER at APPNAME (string)
3: NAME (string)
4: PROC (string)
5: SUSPEND (string)
6: UID (integer)
7: C_notesForObject (integer)
8: __objecttype__ (string)
+ Statistics:
0: Node Output Rows: 10
1: Node Process Time: 265374
2: Node Cumulative Process Time: 265374
3: Node Cumulative Next Batch Process Time: 72870
4: Node Next Batch Calls: 23627
5: Node Blocks: 23626
+ Cost Estimates:Estimated Node Cardinality: 10.0
+ Child 0:
SortNode
+ Output Columns:
0: SYSID (string)
1: USERID (string)
2: SYSOFUSER at APPNAME (string)
3: NAME (string)
4: PROC (string)
5: SUSPEND (string)
6: UID (integer)
7: C_notesForObject (integer)
8: __objecttype__ (string)
+ Statistics:
0: Node Output Rows: 10240
1: Node Process Time: 0
2: Node Cumulative Process Time: 0
3: Node Cumulative Next Batch Process Time: 72785
4: Node Next Batch Calls: 23646
5: Node Blocks: 23626
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
ProjectNode
+ Output Columns:
0: SYSID (string)
1: USERID (string)
2: SYSOFUSER at APPNAME (string)
3: NAME (string)
4: PROC (string)
5: SUSPEND (string)
6: UID (integer)
7: C_notesForObject (integer)
8: __objecttype__ (string)
+ Statistics:
0: Node Output Rows: 23746
1: Node Process Time: 651
2: Node Cumulative Process Time: 265334
3: Node Cumulative Next Batch Process Time: 72548
4: Node Next Batch Calls: 23719
5: Node Blocks: 23672
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
AccessNode
+ Output Columns:
0: SYSID (string)
1: USERID (string)
2: APPNAME (string)
3: NAME (string)
4: PROC (string)
5: SUSPEND (string)
6: UID (integer)
+ Statistics:
0: Node Output Rows: 23746
1: Node Process Time: 264683
2: Node Cumulative Process Time: 264683
3: Node Cumulative Next Batch Process Time: 5
4: Node Next Batch Calls: 48
5: Node Blocks: 1
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:SELECT g_0.SYSID, g_0.USERID, g_1.APPNAME, g_0.NAME, g_0.PROC, g_0.SUSPEND, g_0.UID FROM SECURITY.BASEUSER AS g_0 LEFT OUTER JOIN security.sysinfo AS g_1 ON g_1.sysid = g_0.sysid
+ Model Name:security
+ Select Columns Subplan 0:
ProjectNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
GroupingNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
DependentProcedureAccessNode
+ Output Columns:
0: ID (long)
1: NOTE (string)
2: USER_ID (string)
3: NOTES_TYPE (string)
4: ACCESS_MODE (string)
5: CREATED_ON (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY)
+ Model Name:notes
+ Select Columns:ID
+ Select Columns:X.ID
+ Sort Mode:false
+ Select Columns:COUNT(*)
+ Select Columns Subplan 1:
ProjectNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
GroupingNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
DependentProcedureAccessNode
+ Output Columns:
0: ID (long)
1: NOTE (string)
2: USER_ID (string)
3: NOTES_TYPE (string)
4: ACCESS_MODE (string)
5: CREATED_ON (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY)
+ Model Name:notes
+ Select Columns:ID
+ Select Columns:X.ID
+ Sort Mode:false
+ Select Columns:COUNT(*)
+ Select Columns Subplan 2:
ProjectNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
GroupingNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
DependentProcedureAccessNode
+ Output Columns:
0: ID (long)
1: NOTE (string)
2: USER_ID (string)
3: NOTES_TYPE (string)
4: ACCESS_MODE (string)
5: CREATED_ON (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY)
+ Model Name:notes
+ Select Columns:ID
+ Select Columns:X.ID
+ Sort Mode:false
+ Select Columns:COUNT(*)
+ Select Columns Subplan 3:
ProjectNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
GroupingNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
DependentProcedureAccessNode
+ Output Columns:
0: ID (long)
1: NOTE (string)
2: USER_ID (string)
3: NOTES_TYPE (string)
4: ACCESS_MODE (string)
5: CREATED_ON (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY)
+ Model Name:notes
+ Select Columns:ID
+ Select Columns:X.ID
+ Sort Mode:false
+ Select Columns:COUNT(*)
+ Select Columns Subplan 4:
ProjectNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
GroupingNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
DependentProcedureAccessNode
+ Output Columns:
0: ID (long)
1: NOTE (string)
2: USER_ID (string)
3: NOTES_TYPE (string)
4: ACCESS_MODE (string)
5: CREATED_ON (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY)
+ Model Name:notes
+ Select Columns:ID
+ Select Columns:X.ID
+ Sort Mode:false
+ Select Columns:COUNT(*)
+ Select Columns Subplan 5:
ProjectNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
GroupingNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
DependentProcedureAccessNode
+ Output Columns:
0: ID (long)
1: NOTE (string)
2: USER_ID (string)
3: NOTES_TYPE (string)
4: ACCESS_MODE (string)
5: CREATED_ON (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY)
+ Model Name:notes
+ Select Columns:ID
+ Select Columns:X.ID
+ Sort Mode:false
+ Select Columns:COUNT(*)
+ Select Columns Subplan 6:
ProjectNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
GroupingNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
DependentProcedureAccessNode
+ Output Columns:
0: ID (long)
1: NOTE (string)
2: USER_ID (string)
3: NOTES_TYPE (string)
4: ACCESS_MODE (string)
5: CREATED_ON (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY)
+ Model Name:notes
+ Select Columns:ID
+ Select Columns:X.ID
+ Sort Mode:false
+ Select Columns:COUNT(*)
+ Select Columns Subplan 7:
ProjectNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
GroupingNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
DependentProcedureAccessNode
+ Output Columns:
0: ID (long)
1: NOTE (string)
2: USER_ID (string)
3: NOTES_TYPE (string)
4: ACCESS_MODE (string)
5: CREATED_ON (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY)
+ Model Name:notes
+ Select Columns:ID
+ Select Columns:X.ID
+ Sort Mode:false
+ Select Columns:COUNT(*)
+ Select Columns Subplan 8:
ProjectNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
GroupingNode
+ Output Columns:count (integer)
+ Cost Estimates:Estimated Node Cardinality: 1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
ProjectNode
+ Output Columns:ID (long)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Child 0:
DependentProcedureAccessNode
+ Output Columns:
0: ID (long)
1: NOTE (string)
2: USER_ID (string)
3: NOTES_TYPE (string)
4: ACCESS_MODE (string)
5: CREATED_ON (string)
+ Cost Estimates:Estimated Node Cardinality: -1.0
+ Query:EXEC notes.RETRIEVE_NOTES(notes.RETRIEVE_NOTES.OBJECT_PKEY)
+ Model Name:notes
+ Select Columns:ID
+ Select Columns:X.ID
+ Sort Mode:false
+ Select Columns:COUNT(*)
+ Select Columns:
0: B.SYSID
1: B.USERID
2: J0.APPNAME AS "SYSOFUSER at APPNAME"
3: B.NAME
4: B.PROC
5: B.SUSPEND
6: B.UID
7: (SELECT COUNT(*) FROM (SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY = XMLSERIALIZE(XMLELEMENT(NAME SECURITY.BASEUSER, XMLATTRIBUTES(B.SYSID AS SYSID, B.USERID AS USERID)) AS String)) AS foo) AS C_notesForObject
8: 'SECURITY.BASEUSER' AS "__objecttype__"
+ Sort Columns:["SYSOFUSER at APPNAME"]
+ Sort Mode:SORT
+ Row Offset:9990
+ Row Limit:10
====================================
> More complex join: ORDER BY and LIMIT not pushed down
> ------------------------------------------------------
>
> Key: TEIID-1566
> URL: https://issues.jboss.org/browse/TEIID-1566
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Reporter: Mark Addleman
> Assignee: Steven Hawkins
>
> Query: SELECT B."SYSID", B."USERID", J0.APPNAME as "SYSOFUSER at APPNAME", B."NAME", B."PROC", B."SUSPEND", B."UID", (select count(*) from (select * from notes.RETRIEVE_NOTES where OBJECT_PKEY = xmlserialize(xmlelement("SECURITY.BASEUSER", XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM "SECURITY.BASEUSER" as B left join security.sysinfo J0 on J0.sysid=B.sysid ORDER BY J0.APPNAME ASC LIMIT 9990,10; args: []
> I think there are two problems with the plan for this query: First, neither the ORDER BY nor the LIMIT clause are pushed down to DB2. However, if it is was pushed down, the plan should apply the ORDER & LIMIT before it tries to join to the notes table.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the teiid-issues
mailing list