Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-5260

Have query optimizer make joined tables distinct to improve performance

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: Impala 2.6.0, Impala 2.7.0, Impala 2.8.0
    • Fix Version/s: None
    • Component/s: Frontend
    • Epic Color:
      ghx-label-3

      Description

      Consider the following select statement:

      select tB.bField, count(tA.aField) ct
      from tableA tA
      join tableB tB using (id)
      where (...)
      group by tB.bField
      order by ct
      

      if tableB has a large number of rows (but still less than tableA), performance can be orders of magnitude slower than the equivalent query:

      select tB.bField, count(tA.aField) ct
      from tableA tA
      join (select distinct bField, id[, ...] from tableB) tB using (id)
      where (...)
      group by tB.bField
      order by ct
      

      It appears to me that the slower query gets bogged down with shuttling unnecessary data between nodes.

      Is it possible, and beneficial, to make such a query improvement implicit in Impala's query optimizer?

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              ski309 Michael Sokalski
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: