Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-11110

Reorder applyPreJoinOrderingTransforms, add NotNULL/FilterMerge rules, improve Filter selectivity estimation

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.0.0
    • Component/s: CBO
    • Labels:
      None

      Description

      Query

      select  count(*)
       from store_sales
           ,store_returns
           ,date_dim d1
           ,date_dim d2
       where d1.d_quarter_name = '2000Q1'
         and d1.d_date_sk = ss_sold_date_sk
         and ss_customer_sk = sr_customer_sk
         and ss_item_sk = sr_item_sk
         and ss_ticket_number = sr_ticket_number
         and sr_returned_date_sk = d2.d_date_sk
         and d2.d_quarter_name in ('2000Q1','2000Q2','2000Q3’);
      

      The store_sales table is partitioned on ss_sold_date_sk, which is also used in a join clause. The join clause should add a filter “filterExpr: ss_sold_date_sk is not null”, which should get pushed the MetaStore when fetching the stats. Currently this is not done in CBO planning, which results in the stats from _HIVE_DEFAULT_PARTITION_ to be fetched and considered in the optimization phase. In particular, this increases the NDV for the join columns and may result in wrong planning.

      Including HiveJoinAddNotNullRule in the optimization phase solves this issue.

        Attachments

        1. HIVE-11110.1.patch
          32 kB
          Laljo John Pullokkaran
        2. HIVE-11110.13.patch
          0.9 kB
          Laljo John Pullokkaran
        3. HIVE-11110.14.patch
          179 kB
          Laljo John Pullokkaran
        4. HIVE-11110.15.patch
          161 kB
          Laljo John Pullokkaran
        5. HIVE-11110.16.patch
          167 kB
          Laljo John Pullokkaran
        6. HIVE-11110.17.patch
          168 kB
          Laljo John Pullokkaran
        7. HIVE-11110.18.patch
          163 kB
          Laljo John Pullokkaran
        8. HIVE-11110.19.patch
          169 kB
          Laljo John Pullokkaran
        9. HIVE-11110.2.patch
          6 kB
          Ashutosh Chauhan
        10. HIVE-11110.20.patch
          2.82 MB
          Laljo John Pullokkaran
        11. HIVE-11110.21.patch
          6.76 MB
          Laljo John Pullokkaran
        12. HIVE-11110.22.patch
          6.87 MB
          Laljo John Pullokkaran
        13. HIVE-11110.23.patch
          6.95 MB
          Laljo John Pullokkaran
        14. HIVE-11110.24.patch
          6.96 MB
          Laljo John Pullokkaran
        15. HIVE-11110.25.patch
          7.26 MB
          Laljo John Pullokkaran
        16. HIVE-11110.26.patch
          7.14 MB
          Laljo John Pullokkaran
        17. HIVE-11110.27
          7.72 MB
          Laljo John Pullokkaran
        18. HIVE-11110.27.patch
          7.72 MB
          Laljo John Pullokkaran
        19. HIVE-11110.28.patch
          7.73 MB
          Laljo John Pullokkaran
        20. HIVE-11110.29.patch
          7.04 MB
          Laljo John Pullokkaran
        21. HIVE-11110.30.patch
          7.04 MB
          Laljo John Pullokkaran
        22. HIVE-11110.31.patch
          7.77 MB
          Laljo John Pullokkaran
        23. HIVE-11110.32.patch
          7.77 MB
          Laljo John Pullokkaran
        24. HIVE-11110.33.patch
          7.78 MB
          Laljo John Pullokkaran
        25. HIVE-11110.34.patch
          7.78 MB
          Laljo John Pullokkaran
        26. HIVE-11110.35.patch
          7.78 MB
          Laljo John Pullokkaran
        27. HIVE-11110.4.patch
          29 kB
          Ashutosh Chauhan
        28. HIVE-11110.5.patch
          29 kB
          Ashutosh Chauhan
        29. HIVE-11110.6.patch
          29 kB
          Ashutosh Chauhan
        30. HIVE-11110.7.patch
          13 kB
          Laljo John Pullokkaran
        31. HIVE-11110.8.patch
          13 kB
          Hari Sankar Sivarama Subramaniyan
        32. HIVE-11110.9.patch
          132 kB
          Hari Sankar Sivarama Subramaniyan
        33. HIVE-11110.91.patch
          172 kB
          Hari Sankar Sivarama Subramaniyan
        34. HIVE-11110.92.patch
          164 kB
          Hari Sankar Sivarama Subramaniyan
        35. HIVE-11110.patch
          6 kB
          Jesus Camacho Rodriguez
        36. HIVE-11110-10.patch
          179 kB
          Laljo John Pullokkaran
        37. HIVE-11110-11.patch
          179 kB
          Laljo John Pullokkaran
        38. HIVE-11110-12.patch
          179 kB
          Laljo John Pullokkaran
        39. HIVE-11110-branch-1.2.patch
          9 kB
          Ashutosh Chauhan

          Issue Links

            Activity

              People

              • Assignee:
                jpullokkaran Laljo John Pullokkaran
                Reporter:
                jcamachorodriguez Jesus Camacho Rodriguez
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: