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

[BP to 7.1.2] - Improve pagination queries with fetch

    XMLWordPrintable

Details

    • 1

    Description

      CREATE INDEX ix1 ON default (c1,c2);
      		 
      SELECT * FROM
      		 
      default AS d
      		 
      WHERE d.c1 != 10 
      ORDER BY d.c2
      		 
      LIMIT 100
      		 
      OFFSET 10000;

      Above query not covered, not using index order so pagination not passed to indexer.
      So end up fetching all qualified documents (instead of LIMIT only documents), then sort and apply pagination.

      Here Indexer has all the information in predicate and order by (projection info is not there)

      Alternative is as follows but very inconvenient.

       
      		 
      SELECT d1.* FROM
      		 
      default AS d1 USE KEYS (SELECT RAW META(d).id
      		 
                              WHERE d.c1 != 10 
                              ORDER BY d.c2
      		 
                              LIMIT 100
      		 
                              OFFSET 10000);

      Attachments

        Issue Links

          depends on

          Bug - A problem which impairs or prevents the functions of the product. MB-52255 [BP to 7.1.1] - [ADVISOR] stop session hangs

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. MB-52253 [BP to 7.1.2] - Push filters to index scan to potentially reduce fetch size

          • Major - Major loss of function.
          • Closed
          relates to

          Improvement - An improvement or enhancement to an existing feature or task. MB-51490 Flex Index ORDER BY pushdown for pagination

          • Major - Major loss of function.
          • Closed
          mentioned in

          Page Loading...

          For Gerrit Dashboard: MB-52254
          # Subject Branch Project Status CR V
          175306,3 MB-52254 Perform SORT early when possible to save on FETCH neo query Status: MERGED +2 +1
          175307,2 MB-52254 Perform SORT early when possible to save on FETCH neo query-ee Status: MERGED +2 +1
          175403,2 MB-52254 Perform OFFSET early when possible neo query Status: MERGED +2 +1
          175684,3 MB-52254 Remove cached value attachements for early order neo query Status: MERGED +2 +1

          Activity

            People

              ajay.bhullar Ajay Bhullar
              bingjie.miao Bingjie Miao
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty