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

Ability push non-leading non-continuous predicate to indexer

    XMLWordPrintable

Details

    • Improvement
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 6.5.0
    • 6.5.0
    • query
    • None

    Description

      For Index selection query must have predicate on leading index key.
      Once index selection is made spans/ranges are generated based on predicates.
      At present we only pass leading n continuos index keys predicates only to indexer.

      CREATE INDEX ix1 ON default(c0,c1,c2,c3,c4,c5);
      EXPLAIN SELECT * FROM default WHERE c0 BETWEEN 1 AND 100 AND c2 = "XYZ" AND c4 = 200;
                             "spans": [
                                  {
                                      "exact": true,
                                      "range": [
                                          {
                                              "high": "100",
                                              "inclusion": 3,
                                              "low": "1"
                                          }
                                      ]
                                  }
                              ]
      

         c0 BETWEEN 1 AND 100                                                         qualifies 10K documents
         c0 BETWEEN 1 AND 100 AND c2 = "XYZ"                              qualifies  100 documents
         c0 BETWEEN 1 AND 100 AND c2 = "XYZ" AND c4 = 200       qualifies 10 documents
      

      Above query has predicates on c0, c2 and c4 but there is no predicate on c1 query only pass the range of c0 to indexer (not c2, c4). This result in 10K documents fetch and applied c2 and c4 predicates post fetch and produces 10 documents.
      There are lot of unnecessary processing.

      As GSI index maintains MISSING values on NON-LEADING key, during span generation we can generate FULL scan on skipped ranges and push other predicates to indexer.

       
                              "spans": [
                                  {
                                      "exact": true,
                                      "range": [
                                          {
                                              "high": "100",
                                              "inclusion": 3,
                                              "low": "1"
                                          },
                                          {
                                              "inclusion": 0
                                          },
                                          {
                                              "high": "\"XYZ\"",
                                              "inclusion": 3,
                                              "low": "\"XYZ\""
                                          },
                                          {
                                              "inclusion": 0
                                          },
                                          {
                                              "high": "200",
                                              "inclusion": 3,
                                              "low": "200"
                                          }
                                      ]
                                  }
                              ]
         
      

      In this case indexer will produce only 10 documents and result in only required document fetch.

      • Result in more predicates pushed to indexer
      • Exact predicate pushdown and less false positives
      • More queries qualify ORDER,LIMIT,OFFSET, GROUP, Aggregation pushdown
      • Reduce Unnecessary Fetch
      • Can result in less number of indexes.
      • Can contribute performance and scaling

      __CF_USER_TEXT_ENCODING=0x1F7:0x0:0x0
      INSERT INTO orders (KEY,VALUE)
      VALUES("is0001", {"type":"indexscan","test_id":"indexscan","c0":1, "c1":10,"c2":100,"c3":1000,"c4":10000, "a1":[11,11,12,12,13,14], "a2":[[11,12],[11,12],[13,14]], "a3":[{"id":11, "type": "n"},{"id":11,"type": "n"},{"id":12,"type": "n"},{"id":13,"type": "n"},{"id":14,"type": "n"},{"id":15,"type": "n"}],"a4":[{"aa":[{"id":11},{"id":11},{"id":12}]},{"aa":[{"id":13},{"id":14},{"id":15}]}]}),
      VALUES("is0002", {"type":"indexscan","test_id":"indexscan","c0":2, "c1":20,"c2":200,"c3":2000,"c4":20000, "a1":[11,11,12,12,13,14], "a2":[[11,12],[11,12],[13,14]], "a3":[{"id":11, "type": "n"},{"id":11,"type": "n"},{"id":12,"type": "n"},{"id":13,"type": "n"},{"id":14,"type": "n"},{"id":15,"type": "n"}],"a4":[{"aa":[{"id":11},{"id":11},{"id":12}]},{"aa":[{"id":13},{"id":14},{"id":15}]}]}),
      VALUES("is0003", {"type":"indexscan","test_id":"indexscan","c0":3, "c1":30,"c2":300,"c3":3000,"c4":30000, "a1":[11,11,12,12,13,14], "a2":[[11,12],[11,12],[13,14]], "a3":[{"id":11, "type": "n"},{"id":11,"type": "n"},{"id":12,"type": "n"},{"id":13,"type": "n"},{"id":14,"type": "n"},{"id":15,"type": "n"}],"a4":[{"aa":[{"id":11},{"id":11},{"id":12}]},{"aa":[{"id":13},{"id":14},{"id":15}]}]}),
      VALUES("is0004", {"type":"indexscan","test_id":"indexscan","c0":4, "c1":40,"c2":400,"c3":4000,"c4":40000, "a1":[11,11,12,12,13,14], "a2":[[11,12],[11,12],[13,14]], "a3":[{"id":11, "type": "n"},{"id":11,"type": "n"},{"id":12,"type": "n"},{"id":13,"type": "n"},{"id":14,"type": "n"},{"id":15,"type": "n"}],"a4":[{"aa":[{"id":11},{"id":11},{"id":12}]},{"aa":[{"id":13},{"id":14},{"id":15}]}]}),
      VALUES("is0005", {"type":"indexscan","test_id":"indexscan","c0":5, "c1":50,"c2":500,"c3":5000,"c4":50000, "a1":[11,11,12,12,13,14], "a2":[[11,12],[11,12],[13,14]], "a3":[{"id":11, "type": "n"},{"id":11,"type": "n"},{"id":12,"type": "n"},{"id":13,"type": "n"},{"id":14,"type": "n"},{"id":15,"type": "n"}],"a4":[{"aa":[{"id":11},{"id":11},{"id":12}]},{"aa":[{"id":13},{"id":14},{"id":15}]}]}),
      VALUES("is0006", {"type":"indexscan","test_id":"indexscan","c0":6, "c2":600,"c4":60000, "a1":[11,11,12,12,13,14], "a2":[[11,12],[11,12],[13,14]], "a3":[{"id":11, "type": "n"},{"id":11,"type": "n"},{"id":12,"type": "n"},{"id":13,"type": "n"},{"id":14,"type": "n"},{"id":15,"type": "n"}],"a4":[{"aa":[{"id":11},{"id":11},{"id":12}]},{"aa":[{"id":13},{"id":14},{"id":15}]}]});
       
      CREATE INDEX is01 ON orders (c0,c1,c2,DISTINCT a1,c3,c4) WHERE type="indexscan" AND test_id = "indexscan";
      CREATE INDEX is02 ON orders (ALL ARRAY v.id FOR v IN a3 END,c0,c1,c2,c3,c4) WHERE type="indexscan" AND test_id = "indexscan";
      SELECT META(o).id FROM orders AS o WHERE o.type="indexscan" AND o.test_id = "indexscan" AND o.c0 > 0 AND o.c1 >= 10 AND o.c3 = 1000;
      SELECT META(o).id FROM orders AS o WHERE o.type="indexscan" AND o.test_id = "indexscan" AND o.c0 > 0 AND o.c1 >= 10 AND o.c3 IN [1000, 2000];
      SELECT META(o).id FROM orders AS o WHERE o.type="indexscan" AND o.test_id = "indexscan" AND o.c0 > 0 AND ANY v IN o.a1 SATISFIES v IN [11,12] END AND o.c3 = 1000;
      SELECT META(o).id FROM orders AS o WHERE o.type="indexscan" AND o.test_id = "indexscan" AND o.c0 > 0 AND o.c1 IS MISSING AND o.c2 = 600 AND o.c3 IS MISSING ;
      SELECT v.id FROM orders AS o UNNEST o.a3 AS v WHERE o.type="indexscan" AND o.test_id = "indexscan" AND v.id > 11 AND o.c0 = 1 AND o.c3 = 1000;
      
      

      INSERT INTO default (KEY,VALUE) VALUES("is0000", {"c0":0, "c1":10,"c2":10,"c3":1000,"c4":10000}),
                                     VALUES("is0001", {"c0":1, "c1":10,"c2":20,"c3":1000,"c4":10000}),
                                     VALUES("is0002", {"c0":2, "c1":10,"c2":20,"c3":1000,"c4":10000}),
                                     VALUES("is0003", {"c0":3, "c1":10,"c2":40,"c3":1000,"c4":10000}),
                                     VALUES("is0004", {"c0":4, "c2":100,"c4":10000}),
                                     VALUES("is0005", {"c0":5, "c1":null,"c2":100,"c4":20000}),
                                     VALUES("is0006", {"c0":6, "c1":10,"c2":100,"c3":2000,"c4":10000}),
                                     VALUES("is0007", {"c0":7, "c1":10,"c2":110,"c3":1000,"c4":10000}),
                                     VALUES("is0008", {"c0":8, "c1":10,"c2":120,"c3":1000,"c4":10000}),
                                     VALUES("is0009", {"c0":9, "c1":10,"c2":130,"c3":1000,"c4":10000});
       
      CREATE INDEX ix1 ON default(c0,c1,c2,c3,c4);
       
      SELECT c0,c1,c2,c3,c4, META(d).id
      FROM default AS d
      WHERE c0 BETWEEN 1 AND 8 AND c2 = 100;
       
      SELECT c0,c1,c2,c3,c4, META(d).id
      FROM default AS d
      WHERE c0 BETWEEN 1 AND 8 AND c2 = 100 AND c4 = 20000;
       
      SELECT c0,c1,c2,c3,c4, META(d).id
      FROM default AS d
      WHERE c0 BETWEEN 1 AND 8 AND c2 = 100 AND c1 IS MISSING;
       
      SELECT c0,c1,c2,c3,c4, META(d).id
      FROM default AS d
      WHERE c0 BETWEEN 1 AND 8 AND c1 IS NULL AND c3 IS MISSING;
      

      Attachments

        Issue Links

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

          Activity

            Build couchbase-server-6.5.0-1076 contains query commit 722c3ab with commit message:
            MB-30422. Ability push non-leading non-continuous predicate to indexer

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.5.0-1076 contains query commit 722c3ab with commit message: MB-30422 . Ability push non-leading non-continuous predicate to indexer

            Build couchbase-server-6.5.0-1112 contains query commit c373910 with commit message:
            MB-30422. Avoid Additional Span generations

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.5.0-1112 contains query commit c373910 with commit message: MB-30422 . Avoid Additional Span generations

            Closing this improvement ticket as it is tested in Mad-hatter

            mihir.kamdar Mihir Kamdar (Inactive) added a comment - Closing this improvement ticket as it is tested in Mad-hatter

            People

              korrigan.clark Korrigan Clark (Inactive)
              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