Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-32872

Query with windowing function and ORDER BY isn't using covering index

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • Major
    • 6.5.0
    • 6.5.0
    • query
    • arke cluster
    • Untriaged
    • Centos 64-bit
    • No

    Description

      Index: 

      CREATE INDEX `wf3` ON `bucket-1`((`item`.`manufacturer_id`),(`item`.`brand`),`sales_price`)

       

      Query 1:  Windowing function + ORDER BY.
      Latency is 6 sec

      {{SELECT item.brand, }}
      {{SUM(sales_price) AS item_revenue, }}
      COUNT(1) OVER () AS items_per_brand
      FROM `bucket-1`
      WHERE item.manufacturer_id = 12
      {{GROUP BY item.brand }}
      ORDER BY item_revenue;

       

      Query2: No windowing function
      Latency is 300ms

      {{SELECT item.brand, }}
      SUM(sales_price) AS item_revenue
      FROM `bucket-1`
      WHERE item.manufacturer_id = 12
      {{GROUP BY item.brand }}
      ORDER BY item_revenue;

       

      Query3: No GROUP BY
      Latency is 300ms

      SELECT item.brand,
      SUM(sales_price) AS item_revenue,
      COUNT(1) OVER () AS items_per_brand
      FROM `bucket-1`
      WHERE item.manufacturer_id = 12
      GROUP BY item.brand

       

      Query Plans attached.

       

      Logs of the full test run with Query 1:

      http://perf.jenkins.couchbase.com/job/arke/398/

      [https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-398/172.23.97.18.zip
      ] https://s3-us-west-2.amazonaws.com/perf-artifacts/jenkins-arke-398/172.23.97.19.zip

       

       

      Attachments

        1. Querry1_Plan.json
          8 kB
        2. Querry2_Plan.json
          5 kB
        3. Querry3_Plan.json
          5 kB

        Issue Links

          For Gerrit Dashboard: MB-32872
          # Subject Branch Project Status CR V

          Activity

            People

              korrigan.clark Korrigan Clark (Inactive)
              oleksandr.gyryk Alex Gyryk (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty