Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-2280

Query optimizations based on uniqueness don't work on salted tables

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.0-incubating
    • Fix Version/s: 2.1-incubating
    • Component/s: sql-cmp
    • Labels:
      None
    • Environment:
      Any

      Description

      Rohit Jain noticed this problem. When we have an IN subquery that produces a table with unique values, then we should use an inner join in the query plan. However, we see a semi-join when the subquery references a salted table. The problem is that the uniqueness constraint generated for salted tables is not optimal. Another example is a groupby on a unique column - the optimizer should eliminate the groupby, but this does not happen for salted tables.

      Here are some example queries to demonstrate the problem:

      create table tsalt(a integer not null primary key, b integer) salt using 4 partitions;
      create table tnosalt(a integer not null primary key, b integer);
      
      insert into tsalt values (1,1), (2,2), (3,3);
      insert into tnosalt select * from tsalt;
      
      prepare s1 from
      -- display
      select * from tnosalt where b in (select a from tsalt);
      explain options 'f' s1;
      -- uses a semi-join, but a join would be sufficient
      
      prepare s2 from
      -- display
      select * from tnosalt where b in (select a from tnosalt);
      explain options 'f' s2;
      -- uses a regular inner join
      
      explain options 'f'
      select distinct a from tsalt;
      -- the plan has an unnecessary group by
      
      explain options 'f'
      select distinct a from tnosalt;
      -- the groupby is eliminated
      

        Attachments

          Activity

            People

            • Assignee:
              hzeller Hans Zeller
              Reporter:
              hzeller Hans Zeller
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: