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

          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: