Understanding Index Scans in Couchbase N1QL Query

Understanding Index Scans in Couchbase N1QL Query

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: Inside View

This 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

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.

Note:

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_id ON `travel-sample`(`id`);

CREATE INDEX idx_name ON `travel-sample`(`name`);

CREATE INDEX idx_s_d_airport_stops ON `travel-sample`(`sourceairport`, `destinationairport`, `stops`);

CREATE INDEX idx_sched ON `travel-sample`(DISTINCT ARRAY v.day FOR v IN schedule END) WHERE type = "route";

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` where id = 10;
You can see the spans in the IndexScan2 section of the Explain for the query:
[                                                    
  {                                                  
    "plan": {                                        
      "#operator": "Sequence",                       
      "~children": [                                 
        {                                            
 "#operator": "IndexScan2",                 
          "covers": [                                
            "cover ((`travel-sample`.`id`))",        
            "cover ((meta(`travel-sample`).`id`))"   
          ],                                         
          "index": "idx_id",                         
          "index_id": "82d78c149171c640",            
          "keyspace": "travel-sample",               
          "namespace": "default",    
                
 "spans": [ { "exact": true, "range": [ { "high": "10", "inclusion": 3, "low": "10" } ] } ],

           "using": "gsi"
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Filter",
                "condition": "(cover ((`travel-sample`.`id`)) = 10)"
              },
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "expr": "cover ((meta(`travel-sample`).`id`))"
                  }
                ]
              },
              {
                "#operator": "FinalProject"
              }
            ]
          }
        }
      ]
    },
    "text": "select meta().id from `travel-sample` where id = 10;"
  }
]
Note: The above codeblock shows the entire EXPLAIN plan, but the below examples will 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` 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` 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` 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` WHERE id >= 10;
...                                                
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "inclusion": 1,
                  "low": "10"
                }
              ]
            }
          ],
...

Example 3: Exclusive One-Sided Range Predicate

SELECT meta().id FROM `travel-sample` 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` WHERE id > 10;
...                                                
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "inclusion": 0,
                  "low": "10"
                }
              ]
            }
          ],
...

Example 4: Inclusive One-Sided Range Predicate

SELECT meta().id FROM `travel-sample` 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` 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` 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` WHERE id < 10;
...                                                
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "10",
                  "inclusion": 0,
                  "low": "null"
                }
              ]
            }
          ],
...

Example 6: AND Predicate

SELECT meta().id FROM `travel-sample` 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` 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` 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` 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. FYI: 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` 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` 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` 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` 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` 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` 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` 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.
Note: 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` 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` 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` 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` 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` 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` 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` 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` 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` 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` 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` 
  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` WHERE name LIKE "American%";

In this example, the predicate name LIKE "American%" is transformed to name >= "American" AND name < "Americao" (i.e., "Americo" is the next N1QL collation order of "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` WHERE name LIKE "American%";      
...                                                
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "\"Americao\"",
                  "inclusion": 1,
                  "low": "\"American\""
                }
              ]
            }
          ],
... 

Example 18: LIKE Predicate

SELECT meta().id FROM `travel-sample` 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` WHERE name LIKE "%American%";      
...                                                
          "spans": [
            {
              "range": [
                {
                  "high": "[]",
                  "inclusion": 1,
                  "low": "\"\""
                }
              ]
            }
          ],
...

Example 19: AND Predicate with Composite Index

SELECT meta().id FROM `travel-sample` 
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` 
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` 
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` 
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` 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` 
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` 
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` 
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` 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` 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` 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` 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` 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` 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` 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` WHERE id IN [$1, 10, $2];      
...                                                
            "spans": [
              {
                "range": [
                  {
                    "high": "$1",
                    "inclusion": 3,
                    "low": "$1"
                  }
                ]
              },
              {
                "exact": true,
                "range": [
                  {
                    "high": "10",
                    "inclusion": 3,
                    "low": "10"
                  }
                ]
              },
              {
                "range": [
                  {
                    "high": "$2",
                    "inclusion": 3,
                    "low": "$2"
                  }
                ]
              }
            ],
...

Example 27: ANY Predicate

SELECT meta().id FROM `travel-sample` 
WHERE type = "route" 
  AND 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` 
WHERE type = "route" 
  AND 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` 
WHERE type = "route" 
  AND 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` 
WHERE type = "route" 
  AND 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

Note: 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` 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` 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_absid` ON `travel-sample`(abs(`id`));
When index idx_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` USE INDEX (idx_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` 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` 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.