A newer version of this documentation is available.

View Latest

Scans

      +
      During query execution, when the index path is chosen, the query engine requests the scan by providing a range of values to return. This range is represented as a span in the query plan. Index scans play a major role in optimizing the query plan generation and execution. This section discusses how index spans are generated from query predicates and provides a number of examples.

      Couchbase N1QL is a modern query processing engine designed to provide SQL for JSON on distributed data with a flexible data model. Modern databases are deployed on massive clusters. Using JSON provides a flexible data mode, and N1QL supports enhanced SQL for JSON, to make query processing easier.

      Applications and database drivers submit the N1QL query to one of the available Query nodes on a cluster. The Query node analyzes the query, uses metadata on underlying objects to figure out the optimal execution plan, which it then executes. During execution, depending on the query, using applicable indexes, the Query node works with the Index and Data nodes to retrieve and perform the planned operations. Because Couchbase is a modular clustered database, you scale out data, index, and query services to fit your performance and availability goals.

      Query Execution: Details

      1. Client submits the query over the REST API; 2. Query Service parses, analyzes, and creates plan; 3. Query Service sends request and index filters to Index Service; 4. Index Service returns qualified doc keys to Query Service; 5. Query Service sends fetch request with doc keys to Data Service; 6. Data Service returns documents to the Query Service; 7. Query Service evaluates the documents; 8. Query Service returns result to client

      The following figure shows all the possible phases a SELECT query goes through to return the results. Not all queries need to go through every phase, some go through many of these phases multiple times. For example, the Sort phase can be skipped when there is no ORDER BY clause in the query; and the Scan-Fetch-Join phases will execute multiple times for correlated subqueries.

      Inside a Query Node

      query service

      This brief introduction to query planning has details of query planner. When the Index path is chosen, query engine requests the scan by providing the range of values to return. This range is represented as a SPAN in the query plan. The index spans will play major roles in optimal plan generation and execution. Here, we discuss how the Index spans are generated from the query predicates (filters).

      Spans Overview

      FILTER, JOIN, and PROJECT are fundamental operations of database query processing. The filtering process takes the initial keyspace and produces an optimal subset of the documents the query is interested in. To produce the smallest possible subset, indexes are used to apply as many predicates as possible.

      Query predicates indicate the subsets of data that we are interested in. During the query planning phase, we select the indexes to be used. Then, for each index, we decide the predicates to be applied by each index. The query predicates are translated into spans in the query plan and passed to the Indexer. Spans simply express the predicates in terms of data ranges.

      Examples on This Page

      The examples on this page illustrate the spans for different predicates and require the following indexes.

      To run the examples, install the travel-sample sample bucket and create these indexes:

      CREATE INDEX idx_airline_id ON `travel-sample`.inventory.airline(`id`);
      
      CREATE INDEX idx_airline_name ON `travel-sample`.inventory.airline(`name`);
      
      CREATE INDEX idx_route_src_dst_stops
      ON `travel-sample`.inventory.route(sourceairport, destinationairport, stops);
      
      CREATE INDEX idx_route_sched
      ON `travel-sample`.inventory.route(DISTINCT ARRAY v.day FOR v IN schedule END);

      Example Translations

      The following table shows some example translations:

      Predicate Span Low Span High Span Inclusion

      id = 10

      10

      10

      3 (BOTH)

      id > 10

      10

      No upper bound

      0 (NEITHER)

      id <= 10

      NULL

      10

      2 (HIGH)

      Consider the plan for the following query:

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id = 10;

      You can see the spans in the IndexScan3 section of the Explain for the query:

      [
        {
          "plan": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "IndexScan3",
                "bucket": "travel-sample",
                "covers": [
                  "cover ((`airline`.`id`))",
                  "cover ((meta(`airline`).`id`))"
                ],
                "filter": "(cover ((`airline`.`id`)) = 10)",
                "index": "idx_airline_id",
                "index_id": "39cf9192429a6581",
                "keyspace": "airline",
                "namespace": "default",
                "scope": "inventory",
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "10",
                        "inclusion": 3,
                        "low": "10"
                      }
                    ]
                  }
                ],
                "using": "gsi"
              },
              {
                "#operator": "Parallel",
                "~child": {
                  "#operator": "Sequence",
                  "~children": [
                    {
                      "#operator": "InitialProject",
                      "result_terms": [
                        {
                          "expr": "cover ((meta(`airline`).`id`))"
                        }
                      ]
                    }
                  ]
                }
              }
            ]
          },
          "text": "SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id = 10;"
        }
      ]

      Note that the above codeblock shows the entire EXPLAIN plan, but the examples below show only the "spans" section.

      In N1QL, Index Scan requests are based on a range where each range has a start value, an end value, and specifies whether to include the start or the end value.

      • A "High" field in the range indicates the end value. If "High" is missing, then there is no upper bound.

      • A "Low" field in the range indicates the start value. If "Low" is missing, the scan starts with MISSING.

      • Inclusion indicates if the values of the High and Low fields are included.

        Inclusion Number Meaning Description

        0

        NEITHER

        Neither High nor Low fields are included.

        1

        LOW

        Only Low fields are included.

        2

        HIGH

        Only High fields are included.

        3

        BOTH

        Both High and Low fields are included.

      Example 1: EQUALITY Predicate

      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id = 10;

      In this example, the predicate id = 10 is pushed to index scan.

      Span Range for Low High Inclusion

      id = 10

      10

      10

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id = 10;
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "10",
                        "inclusion": 3,
                        "low": "10"
                      }
                    ]
                  }
                ],
      // ...

      Example 2: Inclusive One-Sided Range Predicate

      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id >= 10;

      In this example, the predicate id >= 10 is pushed to index scan.

      Span Range for Low High Inclusion

      id >= 10

      10

      Unbounded

      1 (LOW)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id >= 10;
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "inclusion": 1,
                        "low": "10"
                      }
                    ]
                  }
                ],
      // ...

      Example 3: Exclusive One-Sided Range Predicate

      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id > 10;

      In this example, the predicate id > 10 is pushed to index scan.

      Span Range for Low High Inclusion

      id > 10

      10

      Unbounded

      0 (NEITHER)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id > 10;
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "inclusion": 0,
                        "low": "10"
                      }
                    ]
                  }
                ],
      // ...

      Example 4: Inclusive One-Sided Range Predicate

      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id <= 10;

      In this example, the predicate id <= 10 is pushed to index scan. This query predicate doesn’t contain an explicit start value, so the start value will implicitly be the non-inclusive null value.

      Span Range for Low High Inclusion

      id <= 10

      NULL

      10

      2 (HIGH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id <= 10;
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "10",
                        "inclusion": 2,
                        "low": "null"
                      }
                    ]
                  }
                ],
      // ...

      Example 5: Exclusive One-Sided Range Predicate

      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id < 10;

      In this example, the predicate id < 10 is pushed to index scan. The query predicate doesn’t contain an explicit start value, so the start value will implicitly be the non-inclusive null value.

      Span Range for Low High Inclusion

      id < 10

      NULL

      10

      0 (NEITHER)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id < 10;
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "10",
                        "inclusion": 0,
                        "low": "null"
                      }
                    ]
                  }
                ],
      // ...

      Example 6: AND Predicate

      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id >= 10 AND id < 25;

      In this example, the predicate id >= 10 AND id < 25 is pushed to index scan.

      Span Range for Low High Inclusion

      id >= 10 AND id < 25

      10

      25

      1 (LOW)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id >=10 AND id < 25;
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "25",
                        "inclusion": 1,
                        "low": "10"
                      }
                    ]
                  }
                ],
      // ...

      Example 7: Multiple AND Predicates

      SELECT meta().id FROM `travel-sample`.inventory.airline
      WHERE id >= 10 AND id < 25 AND id <= 20;

      In this example, the predicate id >= 10 AND id < 25 AND id <= 20 is pushed to the index scan.

      Span Range for Low High Inclusion

      id >= 10 AND id < 25 AND id <= 20

      10

      20

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline
      WHERE id >=10 AND id < 25 AND id <= 20;
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "20",
                        "inclusion": 3,
                        "low": "10"
                      }
                    ]
                  }
                ],
      // ...

      Observe that the optimizer created the span without the id < 25 predicate because the AND predicate id <=20 makes the former predicate redundant. Internally, the optimizer breaks down each predicate and then combines it in a logically consistent manner. If this is too detailed for now, you can skip over to Example 8.

      Span Range for Low High Inclusion

      id >= 10

      10

      Unbounded

      1 (LOW)

      id < 25

      NULL

      25

      0 (NEITHER)

      id >= 10 AND id < 25

      10

      25

      1 (LOW)

      id <= 20

      NULL

      20

      2 (HIGH)

      id >= 10 AND id < 25 AND id <= 20

      10

      20

      3 (BOTH)

      Internally, the following steps occur:

      1. Combined Low becomes highest of both Low values (NULL is the lowest.)

      2. Combined High becomes lowest of both High values (Unbounded is the highest.)

      3. Combined Inclusion becomes OR of corresponding inclusions of Step 1 and Step 2.

      4. Repeat Steps 1 to 3 for each AND clause.

      Example 8: AND Predicate Makes Empty

      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id > 10 AND id < 5;

      In this example, the predicate id > 10 AND id < 5 is pushed to index scan.

      Span Range for Low High Inclusion

      id > 10 AND id < 5

      NULL

      NULL

      0 (NEITHER)

      This is a special case where the span is Low: 10, High: 5, and Inclusion: 0. In this case, the start value is higher than the end value and will not produce results; so, the span is converted to EMPTY SPAN, which will not do any IndexScan.

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id > 10 AND id < 5;
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "null",
                        "inclusion": 0,
                        "low": "null"
                      }
                    ]
                  }
                ],
      // ...

      Example 9: BETWEEN Predicate

      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id BETWEEN 10 AND 25;

      In this example, the predicate id BETWEEN 10 AND 25 (that is, id >= 10 AND id <= 25) is pushed to index scan.

      Span Range for Low High Inclusion

      id BETWEEN 10 AND 25

      10

      25

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id BETWEEN 10 AND 25;
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "25",
                        "inclusion": 3,
                        "low": "10"
                      }
                    ]
                  }
                ],
      // ...

      Example 10: Simple OR Predicate

      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id = 10 OR id = 20;

      In this example, the predicate id = 10 OR id = 20 produces two independent ranges and both of them are pushed to index scan. Duplicate ranges are eliminated, but overlaps are not eliminated.

      Span for Low High Inclusion

      id = 10

      10

      10

      3 (BOTH)

      id = 20

      20

      20

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id = 10 OR id = 20;
      // ...
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "10",
                          "inclusion": 3,
                          "low": "10"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "20",
                          "inclusion": 3,
                          "low": "20"
                        }
                      ]
                    }
                  ],
      // ...

      Example 11: Simple IN Predicate

      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id IN [10, 20];

      In this example, the predicate is id IN [10,20] (that is, id = 10 OR id = 20). After eliminating the duplicates, each element is pushed as a separate range to index scan.

      In version 4.5, up to 8192 IN elements are pushed as separate ranges to the index service. If the number of elements exceed 8192, then the index service performs a full scan on that key.
      Span Range for Low High Inclusion

      id = 10

      10

      10

      3 (BOTH)

      id = 20

      20

      20

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id IN [10, 20];
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "10",
                        "inclusion": 3,
                        "low": "10"
                      }
                    ]
                  },
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "20",
                        "inclusion": 3,
                        "low": "20"
                      }
                    ]
                  }
                ],
      // ...

      Example 12: OR, BETWEEN, AND Predicates

      SELECT meta().id FROM `travel-sample`.inventory.airline
      WHERE (id BETWEEN 10 AND 25)
         OR (id > 50 AND id <= 60);

      In this example, the predicate (id BETWEEN 10 AND 25) OR (id > 50 AND id <= 60) is pushed to index scan.

      Span Range for Low High Inclusion

      id BETWEEN 10 AND 25

      10

      25

      3 (BOTH)

      id > 50 AND id <= 60

      50

      60

      2 (HIGH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline
      WHERE (id BETWEEN 10 AND 25)
         OR (id > 50 AND id <= 60);
      // ...
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "25",
                          "inclusion": 3,
                          "low": "10"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "60",
                          "inclusion": 2,
                          "low": "50"
                        }
                      ]
                    }
                  ],
      // ...

      Example 13: NOT Predicate

      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id <> 10;

      In this example, the predicate id <> 10 is transformed to id < 10 OR id > 10 and then pushed to index scan.

      Span Range for Low High Inclusion

      id < 10

      NULL

      10

      0 (NEITHER)

      id > 10

      10

      Unbounded

      0 (NEITHER)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id <> 10;
      // ...
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "10",
                          "inclusion": 0,
                          "low": "null"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "inclusion": 0,
                          "low": "10"
                        }
                      ]
                    }
                  ],
      // ...

      Example 14: NOT, AND Predicates

      SELECT meta().id FROM `travel-sample`.inventory.airline
      WHERE NOT (id >= 10 AND id < 25);

      In this example, the predicate id >= 10 AND id < 25 is transformed to id <10 OR id >=25 and pushed to index scan.

      Span Range for Low High Inclusion

      id < 10

      NULL

      10

      0 (NEITHER)

      id >= 25

      25

      Unbounded

      1 (LOW)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline
      WHERE NOT (id >= 10 AND id < 25);
      // ...
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "10",
                          "inclusion": 0,
                          "low": "null"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "inclusion": 1,
                          "low": "25"
                        }
                      ]
                    }
                  ],
      // ...

      Example 15: EQUALITY Predicate on String Type

      SELECT meta().id FROM `travel-sample`.inventory.airline
      WHERE name = "American Airlines";

      In this example, the predicate name >= "American Airlines" is pushed to index scan.

      Span Range for Low High Inclusion

      name = "American Airlines"

      "American Airlines"

      "American Airlines"

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline
      WHERE name = "American Airlines";
      // ...
               "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"American Airlines\"",
                        "inclusion": 3,
                        "low": "\"American Airlines\""
                      }
                    ]
                  }
                ],
      // ...

      Example 16: Range Predicate on String Type

      SELECT meta().id FROM `travel-sample`.inventory.airline
      WHERE name >= "American Airlines"
        AND name <= "United Airlines";

      In this example, the predicate name >= "American Airlines" AND name <= "United Airlines" is pushed to index scan.

      Span Range for Low High Inclusion

      name >= "American Airlines" AND name <= "United Airlines"

      "American Airlines"

      "United Airlines"

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline
        WHERE name >= "American Airlines"
          AND name <= "United Airlines";
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"United Airlines\"",
                        "inclusion": 3,
                        "low": "\"American Airlines\""
                      }
                    ]
                  }
                ],
      // ...

      Example 17: LIKE Predicate

      SELECT meta().id FROM `travel-sample`.inventory.airline
      WHERE name LIKE "American%";

      In this example, the predicate name LIKE "American%" is transformed to name >= "American" AND name < "Americao" (where "Americao" is the next string in N1QL collation order after "American") and then pushed to index scan. In the LIKE predicate, the % means match with any number of any characters.

      Span Range for Low High Inclusion

      name LIKE "American%"

      "American"

      "Americao"

      1 (LOW)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline
      WHERE name LIKE "American%";
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"Americao\"",
                        "inclusion": 1,
                        "low": "\"American\""
                      }
                    ]
                  }
                ],
      // ...

      Example 18: LIKE Predicate

      SELECT meta().id FROM `travel-sample`.inventory.airline
      WHERE name LIKE "%American%";

      In this example, the predicate name LIKE "%American%" is transformed and pushed to index scan. In this LIKE predicate '%' is the leading portion of the string, so we can’t push any portion of the string to the index service. "" is the lowest string. [] is an empty array and is greater than every string value in the N1QL collation order.

      Span Range for Low High Inclusion

      name LIKE "%American%"

      ""

      "[]"

      1 (LOW)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline
      WHERE name LIKE "%American%";
      // ...
                "spans": [
                  {
                    "range": [
                      {
                        "high": "[]",
                        "inclusion": 1,
                        "low": "\"\""
                      }
                    ]
                  }
                ],
      // ...

      Example 19: AND Predicate with Composite Index

      SELECT meta().id FROM `travel-sample`.inventory.route
      WHERE sourceairport = "SFO"
        AND destinationairport = "JFK"
        AND stops BETWEEN 0 AND 2;

      In this example, the predicate sourceairport = "SFO" AND destinationairport = "JFK" AND stops BETWEEN 0 AND 2 is pushed to index scan.

      Span Range for Low High Inclusion

      sourceairport = "SFO"

      "SFO"

      "SFO"

      3 (BOTH)

      destinationairport = "JFK"

      "JFK"

      "JFK"

      3 (BOTH)

      stops BETWEEN 0 AND 2

      0

      2

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.route
      WHERE sourceairport = "SFO"
        AND destinationairport = "JFK"
        AND stops BETWEEN 0 AND 2;
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"SFO\"",
                        "inclusion": 3,
                        "low": "\"SFO\""
                      },
                      {
                        "high": "\"JFK\"",
                        "inclusion": 3,
                        "low": "\"JFK\""
                      },
                      {
                        "high": "2",
                        "inclusion": 3,
                        "low": "0"
                      }
                    ]
                  }
                ],
      // ...

      Example 20: AND Predicate with Composite Index

      SELECT meta().id FROM `travel-sample`.inventory.route
      WHERE sourceairport IN ["SFO", "SJC"]
        AND destinationairport = "JFK"
        AND stops = 0;

      In this example, the predicate sourceairport IN ["SFO", "SJC"] AND destinationairport = "JFK" AND stops = 0 is pushed to index scan.

      Span Range for Low High Inclusion

      sourceairport IN ["SFO", "SJC"]

      "SFO"

      "SJC"

      "SFO"

      "SJC"

      3 (BOTH)

      3 (BOTH)

      destinationairport = "JFK"

      "JFK"

      "JFK"

      3 (BOTH)

      stops

      0

      0

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.route
      WHERE sourceairport IN ["SFO", "SJC"]
        AND destinationairport = "JFK"
        AND stops = 0;
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"SFO\"",
                        "inclusion": 3,
                        "low": "\"SFO\""
                      },
                      {
                        "high": "\"JFK\"",
                        "inclusion": 3,
                        "low": "\"JFK\""
                      },
                      {
                        "high": "0",
                        "inclusion": 3,
                        "low": "0"
                      }
                    ]
                  },
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"SJC\"",
                        "inclusion": 3,
                        "low": "\"SJC\""
                      },
                      {
                        "high": "\"JFK\"",
                        "inclusion": 3,
                        "low": "\"JFK\""
                      },
                      {
                        "high": "0",
                        "inclusion": 3,
                        "low": "0"
                      }
                    ]
                  }
                ],
      // ...

      Example 21: Composite AND Predicate with Trailing Keys Missing in Predicate

      SELECT meta().id FROM `travel-sample`.inventory.route
      WHERE sourceairport = "SFO"
        AND destinationairport = "JFK";

      In this example, the predicate sourceairport = "SFO" AND destinationairport = "JFK" is pushed to index scan.

      Span Range for Low High Inclusion

      sourceairport = "SFO"

      "SFO"

      "SFO"

      3 (BOTH)

      destinationairport = "JFK"

      "JFK"

      "JFK"

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.route
      WHERE sourceairport = "SFO"
        AND destinationairport = "JFK";
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"SFO\"",
                        "inclusion": 3,
                        "low": "\"SFO\""
                      },
                      {
                        "high": "\"JFK\"",
                        "inclusion": 3,
                        "low": "\"JFK\""
                      }
                    ]
                  }
                ],
      // ...

      Example 22: Composite AND Predicate with Unbounded High of Trailing Key

      SELECT meta().id FROM `travel-sample`.inventory.route
      WHERE sourceairport = "SFO"
        AND destinationairport = "JFK"
        AND stops >= 0;

      In this example, the predicate sourceairport = "SFO" AND destinationairport = "JFK" AND stops >= 0 is pushed to index scan.

      Span Range for Low High Inclusion

      sourceairport = "SFO"

      "SFO"

      "SFO"

      3 (BOTH)

      destinationairport = "JFK"

      "JFK"

      "JFK"

      3 (BOTH)

      stops >= 0

      0

      Unbounded

      1 (LOW)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.route
      WHERE sourceairport = "SFO"
        AND destinationairport = "JFK"
        AND stops >= 0;
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"SFO\"",
                        "inclusion": 3,
                        "low": "\"SFO\""
                      },
                      {
                        "high": "\"JFK\"",
                        "inclusion": 3,
                        "low": "\"JFK\""
                      },
                      {
                        "inclusion": 1,
                        "low": "0"
                      }
                    ]
                  }
                ],
      // ...

      Example 23: EQUALITY Predicate with Query Parameters

      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id = $1;

      This example pushes the predicate id = $1 to index scan.

      Span Range for Low High Inclusion

      id = $1

      $1

      $1

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id = $1;
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "$1",
                        "inclusion": 3,
                        "low": "$1"
                      }
                    ]
                  }
                ],
      // ...

      Example 24: AND Predicate with Query Parameters

      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id >= $1 AND id < $2;

      In this example, the predicate id >= $1 AND id < $2 is pushed to the index scan.

      Span Range for Low High Inclusion

      id >= $1 AND id < $2

      $1

      $2

      1 (LOW)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id >= $1 AND id < $2;
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "$2",
                        "inclusion": 1,
                        "low": "$1"
                      }
                    ]
                  }
                ],
      // ...

      Example 25: OR Predicate with Query Parameters

      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id = $1 OR id < $2;

      This example pushes the predicate id = $1 OR id < $2 to the index scan.

      Span Range for Low High Inclusion

      id = $1

      $1

      $1

      3 (BOTH)

      id < $2

      NULL

      $2

      0 (NEITHER)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id = $1 OR id < $2;
      // ...
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "$1",
                          "inclusion": 3,
                          "low": "$1"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "$2",
                          "inclusion": 0,
                          "low": "null"
                        }
                      ]
                    }
                  ],
      // ...

      Example 26: IN Predicate with Query Parameters

      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id IN [ $1, 10, $2] ;

      In this example, the predicate id IN [$1, 10, $2] is pushed to index scan.

      Span Range for Low High Inclusion

      id IN [$1, 10, $2]

      $1

      10

      $2

      $1

      10

      $2

      3 (BOTH)

      3 (BOTH)

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE id IN [$1, 10, $2];
      // ...
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "$1",
                          "inclusion": 3,
                          "low": "$1"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "10",
                          "inclusion": 3,
                          "low": "10"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "$2",
                          "inclusion": 3,
                          "low": "$2"
                        }
                      ]
                    }
                  ],
      // ...

      Example 27: ANY Predicate

      SELECT meta().id FROM `travel-sample`.inventory.route
      WHERE ANY v IN schedule SATISFIES v.day = 0
      END;

      In this example, the predicate v.day = 0 is pushed to ARRAY index scan.

      Span Range for Low High Inclusion

      v.day = 0

      0

      0

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.route
      WHERE ANY v IN schedule SATISFIES v.day = 0
      END;
      // ...
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "0",
                          "inclusion": 3,
                          "low": "0"
                        }
                      ]
                    }
                  ],
      // ...

      Example 28: ANY Predicate

      SELECT meta().id FROM `travel-sample`.inventory.route
      WHERE ANY v IN schedule SATISFIES v.day IN [1,2,3]
      END;

      In this example, the predicate v.day IN [1,2,3] is pushed to ARRAY index scan.

      Span Range for Low High Inclusion

      v.day IN [1,2,3]

      1

      2

      3

      1

      2

      3

      3 (BOTH)

      3 (BOTH)

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.route
      WHERE ANY v IN schedule SATISFIES v.day IN [1,2,3]
      END;
      // ...
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "1",
                          "inclusion": 3,
                          "low": "1"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "2",
                          "inclusion": 3,
                          "low": "2"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "3",
                          "inclusion": 3,
                          "low": "3"
                        }
                      ]
                    }
                  ],
      // ...

      Example 29: EQUALITY Predicate on Expression

      The following examples don’t have the right indexes, or the queries need to be modified to produce an optimal plan.
      SELECT meta().id FROM `travel-sample`.inventory.airline WHERE abs(id) = 10;

      In this example, no predicate is pushed to index scan.

      Span Range for Low High Inclusion

      id

      NULL

      Unbounded

      0 (NEITHER)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline WHERE abs(id) = 10;
      // ...
                "spans": [
                  {
                    "range": [
                      {
                        "inclusion": 0,
                        "low": "null"
                      }
                    ]
                  }
                ],
      // ...

      The span indicates that the index service is performing a complete index scan. If the index does not cover the query, the query service fetches the document from the data node and then applies the predicate. For better performance, create a new index as follows:

      CREATE INDEX `idx_airline_absid` ON `travel-sample`.inventory.airline(abs(`id`));

      When index idx_airline_absid is used, the predicate abs(id) = 10 is pushed to index scan.

      Span Range for Low High Inclusion

      abs(id) = 10

      10

      10

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline
      USE INDEX (idx_airline_absid)
      WHERE abs(id) = 10;
      // ...
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "10",
                        "inclusion": 3,
                        "low": "10"
                      }
                    ]
                  }
                ],
      // ...

      Example 30: Overlapping Predicates

      SELECT meta().id FROM `travel-sample`.inventory.airline
      WHERE id <= 100
        OR (id BETWEEN 50 AND 150);

      In this example, the predicates id <= 100 OR (id BETWEEN 50 AND 150) are pushed to index scan as two ranges.

      Span Range for Low High Inclusion

      id <= 100

      NULL

      100

      2 (HIGH)

      id BETWEEN 50 AND 150

      50

      150

      3 (BOTH)

      EXPLAIN SELECT meta().id FROM `travel-sample`.inventory.airline
      WHERE id <= 100
        OR (id BETWEEN 50 AND 150);
      // ...
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "100",
                          "inclusion": 2,
                          "low": "null"
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "150",
                          "inclusion": 3,
                          "low": "50"
                        }
                      ]
                    }
                  ],
      // ...

      Summary

      When you analyze the explain plan, correlate the predicates in the explain to the spans. Ensure the most optimal index is selected and the spans have the expected range for all the index keys. More keys in each span will make the query more efficient.