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

MAX queries can be further optimized

    XMLWordPrintable

Details

    • Improvement
    • Resolution: Duplicate
    • Major
    • 6.5.0
    • 5.5.0
    • secondary-index
    • None

    Description

      https://forums.couchbase.com/t/slow-max-query-with-predicate-that-might-be-true-or-false-against-bucket-with-tens-of-millions-of-documents/17874

      CREATE INDEX uuidcountermax ON production_statedata(Type, UUID DESC);
      		 
      SELECT MAX(UUID) as max 
      FROM production_statedata
      		 
      WHERE Type = $type_name;

      Above query takes time. But below query fast

      SELECT UUID as max 
      FROM production_statedata
      		 
       WHERE Type = $type_name
      		 
      ORDER BY UUID DESC LIMIT 1;

      There is no group by and MAX() argument is in DESC order and all index keys before are equality predicates when first value found it can terminate scan.

      This is similar to MB-29605

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MB-29605 Optimize MIN aggregate without group by when KeyPos > 0

          • Major - Major loss of function.
          • Closed
          No reviews matched the request. Check your Options in the drop-down menu of this sections header.

          Activity

            People

              jeelan.poola Jeelan Poola
              Sitaram.Vemulapalli Sitaram Vemulapalli
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty