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

MAX queries can be further optimized

    XMLWordPrintable

Details

    • Improvement
    • Status: Closed
    • Major
    • Resolution: Duplicate
    • 5.5.0
    • 6.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

          No reviews matched the request. Check your Options in the drop-down menu of this sections header.

          Activity

            When we fix MB-29605, MAX will also be taken care of. Hence closing it as duplicate.

            jeelan.poola Jeelan Poola added a comment - When we fix MB-29605 , MAX will also be taken care of. Hence closing it as duplicate.

            Bulk closing all invalid, duplicate and won't fix bugs. Please feel free to reopen them

            raju Raju Suravarjjala added a comment - Bulk closing all invalid, duplicate and won't fix bugs. Please feel free to reopen them

            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