Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-6075

Different plans for different joins orders

    XMLWordPrintable

    Details

    • Type: Enhancement
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: Backlog
    • Component/s: Query Engine
    • Labels:
      None
    • [QE] How to address?:
      ---
    • [QE] Why QE missed?:
      ---

      Description

      We have simple salesforce translator usage in teiid-spring-boot project
      We have two hierarchys of objects

      opportunitylink__c->
      	[Opportunity__r->Linked_Reseller__r,
      	contract__r->Account]
      	 AND
      	replacementcontract__c->Contract
      

      For similar SQL with different joins orders we have different execution plans
      first SQL with very slow performance plan

      select c.contractnumber
      from salesforce.opportunity o
          left outer join salesforce.opportunitylink__c ol on o.id = ol.opportunity__c
          left outer join salesforce.contract c on ol.contract__c = c.id
          left outer join salesforce.account a on c.accountid = a.id
          left outer join salesforce.replacementcontract__c rc on c.id = rc.replacedbycontract__c
          left outer join salesforce.account lra on o.linked_reseller__c = lra.id
      where o.type = 'Maintenance'
          and o.id IS NOT NULL
          and ol.opportunity__c IS NOT NULL
          and ol.contract__c IS NOT NULL
          and c.id IS NOT NULL
          and c.accountid IS NOT NULL
          and a.id IS NOT NULL
          and a.exclude_from_corporate_stats__c = false
          and lra.exclude_from_corporate_stats__c = false
      limit 100;
      

      Plan

      OPTIMIZATION COMPLETE:
      PROCESSOR PLAN:
      ProjectNode(0) output=[c.ContractNumber] [c.ContractNumber]
        LimitNode(1) output=[c.ContractNumber] limit 100
          JoinNode(2) [ENHANCED SORT JOIN (SORT/SORT)] [INNER JOIN] criteria=[o.Linked_Reseller__c=lra.Id] output=[c.ContractNumber]
            JoinNode(3) [ENHANCED SORT JOIN (SORT/SORT)] [LEFT OUTER JOIN] criteria=[c.Id=rc.ReplacedByContract__c] output=[o.Linked_Reseller__c, c.ContractNumber]
              AccessNode(4) output=[c.Id, o.Linked_Reseller__c, c.ContractNumber] SELECT g_2.Id, g_0.Linked_Reseller__c, g_2.ContractNumber FROM ((salesforce.Opportunity AS g_0 INNER JOIN salesforce.OpportunityLink__c AS g_1 ON g_0.Id = g_1.Opportunity__c) INNER JOIN salesforce.Contract AS g_2 ON g_1.Contract__c = g_2.Id) INNER JOIN salesforce.Account AS g_3 ON g_2.AccountId = g_3.Id WHERE (g_0.Type = 'Maintenance') AND (g_0.Id IS NOT NULL) AND (g_1.Opportunity__c IS NOT NULL) AND (g_1.Contract__c IS NOT NULL) AND (g_2.Id IS NOT NULL) AND (g_2.AccountId IS NOT NULL) AND (g_3.Id IS NOT NULL) AND (g_3.Exclude_from_Corporate_Stats__c = FALSE)
              AccessNode(5) output=[rc.ReplacedByContract__c] SELECT g_0.ReplacedByContract__c FROM salesforce.ReplacementContract__c AS g_0
            AccessNode(6) output=[lra.Id] SELECT g_0.Id FROM salesforce.Account AS g_0 WHERE g_0.Exclude_from_Corporate_Stats__c = FALSE
      

      NOTE look at: SELECT g_0.ReplacedByContract_c FROM salesforce.ReplacementContract_c AS g_0

      Second SQL with more efficient performance plan

      select c.contractnumber
      from salesforce.opportunity o
          left outer join salesforce.opportunitylink__c ol on o.id = ol.opportunity__c
          left outer join salesforce.contract c on ol.contract__c = c.id
          left outer join salesforce.account a on c.accountid = a.id
          left outer join salesforce.account lra on o.linked_reseller__c = lra.id
          left outer join salesforce.replacementcontract__c rc on c.id = rc.replacedbycontract__c
      where o.type = 'Maintenance'
          and o.id IS NOT NULL
          and ol.opportunity__c IS NOT NULL
          and ol.contract__c IS NOT NULL
          and c.id IS NOT NULL
          and c.accountid IS NOT NULL
          and a.id IS NOT NULL
          and a.exclude_from_corporate_stats__c = false
          and lra.exclude_from_corporate_stats__c = false
      limit 100;
      

      with plan

      OPTIMIZATION COMPLETE:
      PROCESSOR PLAN:
      ProjectNode(0) output=[c.ContractNumber] [c.ContractNumber]
        LimitNode(1) output=[c.ContractNumber] limit 100
          JoinNode(2) [Dependent] [ENHANCED SORT JOIN (SORT/SORT)] [LEFT OUTER JOIN] criteria=[c.Id=rc.ReplacedByContract__c] output=[c.ContractNumber]
            AccessNode(3) output=[c.Id, c.ContractNumber] SELECT g_2.Id, g_2.ContractNumber FROM (((salesforce.Opportunity AS g_0 INNER JOIN salesforce.OpportunityLink__c AS g_1 ON g_0.Id = g_1.Opportunity__c) INNER JOIN salesforce.Contract AS g_2 ON g_1.Contract__c = g_2.Id) INNER JOIN salesforce.Account AS g_3 ON g_2.AccountId = g_3.Id) INNER JOIN salesforce.Account AS g_4 ON g_0.Linked_Reseller__c = g_4.Id WHERE (g_0.Type = 'Maintenance') AND (g_0.Id IS NOT NULL) AND (g_1.Opportunity__c IS NOT NULL) AND (g_1.Contract__c IS NOT NULL) AND (g_2.Id IS NOT NULL) AND (g_2.AccountId IS NOT NULL) AND (g_3.Id IS NOT NULL) AND (g_3.Exclude_from_Corporate_Stats__c = FALSE) AND (g_4.Exclude_from_Corporate_Stats__c = FALSE) LIMIT 100
            DependentAccessNode(4) output=[rc.ReplacedByContract__c] SELECT g_0.ReplacedByContract__c FROM salesforce.ReplacementContract__c AS g_0 WHERE g_0.ReplacedByContract__c IN (<dependent values>)
      

      NOTE looka at: SELECT g_0.ReplacedByContract_c FROM salesforce.ReplacementContractc AS g_0 WHERE g_0.ReplacedByContract_c IN (<dependent values>)

      So. If we joined

      left outer join salesforce.account lra on o.linked_reseller__c = lra.id
          left outer join salesforce.replacementcontract__c rc on c.id = rc.replacedbycontract__c
      

      all work fine
      But if we joined

       left outer join salesforce.replacementcontract__c rc on c.id = rc.replacedbycontract__c
       left outer join salesforce.account lra on o.linked_reseller__c = lra.id
      

      We have not optimally plan

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            i3draven Renat Eskenin (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: