Understanding Index Scans

Understanding Index 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.

Filter, join, and project are the fundamental operations when processing database queries. 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 subset 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.

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 explain plan for the following query. You can see the spans in the IndexScan section of the explain plan.
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id = 10;
{
    "requestID": "4f64d56a-0db6-4d1e-8868-36bfe11146cf",
    "signature": "json",
    "results": [
        {
            "plan": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "IndexScan",
                        "covers": [
                            "cover ((`travel-sample`.`id`))",
                            "cover ((meta(`travel-sample`).`id`))"
                        ],
                        "index": "idx_id",
                        "index_id": "8ad897f8afb165ef",
                        "keyspace": "travel-sample",
                        "namespace": "default",
                        "spans": [ { "Range": { "High": [ "10" ], "Inclusion": 3, "Low": [ "10" ] } } ],
                        "using": "gsi"
                    }
                ]
               ...
            }
        }
    ]
}    
Index scan requests in N1QL 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 from the range, then there is no upper bound.
  • A “Low” field in the range indicates the start value. If "Low" is missing from the range, the scan starts with a NULL. NULL is the lowest value as MISSING values are not indexed.
  • Inclusion indicates if the values of 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.
When you analyze an explain plan, correlate the predicates in the explain to the spans. To make your queries more efficient:
  • Ensure the most optimal index is selected and the spans have the expected range for all the index keys.
  • Use more keys in each span.

Examples

The following examples illustrate the spans for different predicates.

Before running the examples, ensure that you've installed the travel-sample sample bucket. And create the following indexes:

CREATE INDEX `idx_id` ON `travel-sample`(`id`);
      CREATE INDEX `idx_name` ON `travel-sample`(`name`);
      CREATE INDEX `idx_saiport_dairport_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 1: EQUALITY Predicate

SELECT meta().id FROM `travel-sample` WHERE id = 10;
This example pushes the predicate id = 10 to index scan.
Span for Low High Inclusion
id=10 10 10 3(BOTH)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id = 10;
Result snippet
      "spans": [
          {
              "Range": {
                  "High": [
                       "10"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "10"
                   ]
              }
          }
      ]    

Example 2: Inclusive One-Sided Range Predicate

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

This example pushes the predicate id >= 10 to index scan.

Span for Low High Inclusion
id >= 10 10 Unbounded 1(LOW)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id >= 10;
Result snippet


          "spans": [
            {
              "Range": {
                "Inclusion": 1,
                "Low": [
                  "10"
                ]
              }
            }
          ]

Example 3: Exclusive One-Sided Range Predicate

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

This example pushes the predicate id >= 10 to index scan.

Span for Low High Inclusion
id > 10 10 Unbounded 0 (NEITHER)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id > 10;
Result snippet


      "spans": [
          {
              "Range": {
                   "Inclusion": 0,
                   "Low": [
                       "10"
                   ]
              }
          }
      ]

Example 4: Inclusive One-Sided Range Predicate

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

This example pushes the predicate id <= 10 to index scan.

Span for Low High Inclusion
id <= 10 NULL 10 2(HIGH)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id <= 10;
Result snippet


     "spans": [
          {
              "Range": {
                  "High": [
                       "10"
                   ],
                   "Inclusion": 2,
                   "Low": [
                       "null"
                   ]
              }
          }
      ]     

Example 5: Exclusive One-Sided Range Predicate

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

This example pushes the predicate id < 10 to index scan.

Span for Low High Inclusion
id < 10 NULL 10 0(NEITHER)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id < 10;
Result snippet


      "spans": [
          {
              "Range": {
                  "High": [
                       "10"
                   ],
                   "Inclusion": 0,
                   "Low": [
                       "null"
                   ]
              }
          }
      ]       

Example 6: AND Predicate

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

This example pushes the predicate id >= 10 AND id < 25 to index scan.

Span for Low High Inclusion
id >= 10 AND id < 25 10 25 1(LOW)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id >=10 AND id < 25;
Result snippet


      "spans": [
          {
              "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;

This example pushes the predicates id >= 10 AND id < 25 AND id <= 20 to the index scan.

Span for Low High Inclusion
id >= 10 AND id < 25 AND id <= 20 10 20 3(BOTH)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id >=10 AND id < 25 AND id <= 20;
Result snippet


      "spans": [
          {
              "Range": {
                  "High": [
                       "20"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "10"
                   ]
              }
          }
      ]
      
Observe that the optimizer created the span without the id < 25 predicate because the other AND predicate id <=20 makes the former predicate redundant.
Span 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 optimizer breaks down each predicate and then combines it in a logically consistent manner.
  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 the first two steps.
  4. Repeat the 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;

This example pushes the predicate id > 10 AND id < 5 to the index scan.

Span 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, Inclusion: 0. In this case, the start value is higher than the end value and will not produce results. Hence, the span is converted to EMPTY SPAN.

Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id >10 AND id < 5;
Result snippet


    "spans": [
          {
              "Range": {
                  "High": [
                       "null"
                   ],
                   "Inclusion": 0
              }
          }
      ] 
    

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 for Low High Inclusion
id BETWEEN 10 AND 25 10 25 3(BOTH)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id BETWEEN 10 AND 25;
Result snippet


      "spans": [
          {
              "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 plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id = 10 OR id = 20;
Result snippet

      "spans": [
          {
              "Range": {
                  "High": [
                       "10"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "10"
                   ]
              }
          },
          {
              "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 the key.
Span for Low High Inclusion
id = 10 10 10 3(BOTH)
id = 20 20 20 3(BOTH)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id IN [10, 20];
Result snippet


      "spans": [
          {
              "Range": {
                  "High": [
                       "10"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "10"
                   ]
              }
          },
          {
              "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);

This example pushes the predicate (id BETWEEN 10 AND 25) OR (id > 50 AND id <= 60) to index scan.

Span for Low High Inclusion
id BETWEEN 10 AND 25 10 25 3(BOTH)
id > 50 AND id <= 60 50 60 2(HIGH)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE (id BETWEEN 10 AND 25) OR (id > 50 AND id <= 60);
Result snippet


      "spans": [
          {
              "Range": {
                  "High": [
                       "25"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "10"
                   ]
              }
          },
          {
              "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 for Low High Inclusion
id < 10 NULL 10 0(NEITHER)
id > 10 10 Unbounded 0(NEITHER)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id <> 10;
Result snippet
      "spans": [
          {
              "Range": {
                  "High": [
                       "10"
                   ],
                   "Inclusion": 0,
                   "Low": [
                       "null"
                   ]
              }
          },
          {
              "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 for Low High Inclusion
id < 10 NULL 10 0(NEITHER)
id >= 25 25 Unbounded 1(LOW)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE NOT (id >= 10 AND id < 25);
Result snippet

"spans": [
          {
              "Range": {
                  "High": [
                       "10"
                   ],
                   "Inclusion": 0,
                   "Low": [
                       "null"
                   ]
              }
          },
          {
              "Range": {
                   "Inclusion": 1,
                   "Low": [
                       "25"
                   ]
              }
          }
      ]     

Example 15: EQUALITY Predicate on String Type

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

This example pushes the predicate name >= "American Airlines" to index scan.

Span for Low High Inclusion
name = "American Airlines" "American Airlines" "American Airlines" 3(BOTH)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE name = "American Airlines";
Result snippet
        
      "spans": [
          {
              "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";

This example pushes the predicate name >= "American Airlines" AND name <= "United Airlines" to the index scan.

Span for Low High Inclusion
name >= "American Airlines" AND name <= "United Airlines" "American Airlines" "United Airlines" 3(BOTH)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` 
  WHERE name >= "American Airlines" 
    AND name <= "United Airlines";      
Result snippet
"spans": [
          {
              "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" (the next N1QL collation order of "American") and then pushed to index scan. In LIKE predicate % means match with any number of any character.

Span for Low High Inclusion
name LIKE "American%" "American" "Americao" 1(LOW)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE name LIKE "American%";      
Result snippet
      "spans": [
          {
              "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 for Low High Inclusion
name LIKE "%American%" "" "[]" 1(LOW)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE name LIKE "%American%";      
Result snippet
      "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;

This example pushes the predicate sourceairport = "SFO" AND destinationairport = "JFK" AND stops BETWEEN 0 AND 2 to index scan.

Span 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)
The index selected has keys (sourceairport, destinationairport, stops) and the spans are stitched in that order to create a composite span.
Span for Low High Inclusion
sourceairport = "SFO" AND destinationairport = "JFK" AND stops BETWEEN 0 AND 2 ["SFO","JFK",0] ["SFO","JFK",2] 3(BOTH)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` 
WHERE sourceairport = "SFO" 
  AND destinationairport = "JFK" 
  AND stops BETWEEN 0 AND 2;      
Result snippet
      "spans": [
          {
              "Range": {
                   "High": [
                      "\"SFO\"",
                      "\"JFK\"",
                      "2"
                   ],
                   "Inclusion": 3,
                   "Low": [
                      "\"SFO\"",
                      "\"JFK\"",
                      "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;

This example pushes the predicate sourceairport IN ["SFO", "SJC"] AND destinationairport = "JFK" AND stops = 0 to index scan.

Span 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)

The index selected has keys (sourceairport, destinationairport, stops) and the spans are stitched in that order to create a composite span as follows:

Span for Low High Inclusion
sourceairport IN ["SFO", "SJC"] AND destinationairport = "JFK" AND stops = 0 ["SFO","JFK",0]

["SJC","JFK", 0]

["SFO","JFK",0]

["SJC","JFK", 0]

3(BOTH)

3(BOTH)

Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` 
WHERE sourceairport IN ["SFO", "SJC"] 
  AND destinationairport = "JFK" 
  AND stops = 0;      
Result snippet
      "spans": [
          {
              "Range": {
                   "High": [
                      "\"SFO\"",
                      "\"JFK\"",
                      "0"
                   ],
                   "Inclusion": 3,
                   "Low": [
                      "\"SFO\"",
                      "\"JFK\"",
                      "0"
                   ]
              }
          },
              "Range": {
                   "High": [
                      "\"SJC\"",
                      "\"JFK\"",
                      "0"
                   ],
                   "Inclusion": 3,
                   "Low": [
                      "\"SJC\"",
                      "\"JFK\"",
                      "0"
                   ]
              }
          }
      ]      

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

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

This example pushes the predicate sourceairport = "SFO" AND destinationairport = "JFK" to index scan.

Span for Low High Inclusion
sourceairport = "SFO" "SFO" "SFO" 3(BOTH)
destinationairport = "JFK" "JFK" "JFK" 3(BOTH)
The index selected has keys (sourceairport, destinationairport, stops). As the stop key predicate is missing in the query, the one before span high value is converted to successor and removed inclusive bit. The transformed spans are as follows:
Span for Low High Inclusion
sourceairport = "SFO" "SFO" "SFO" 3(BOTH)
destinationairport = "JFK" "JFK" successor(JFK) 1(LOW)
The spans are then stitched in that order to create a composite span as follows.
Span for Low High Inclusion
sourceairport = "SFO" AND destinationairport = "JFK" ["SFO","JFK"] ["SFO", successor("JFK")] 1(LOW)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` 
WHERE sourceairport = "SFO" 
  AND destinationairport = "JFK";
Result snippet
     "spans": [
          {
              "Range": {
                   "High": [
                      "\"SFO\"",
                      "successor(\"JFK\")"
                   ],
                   "Inclusion": 1,
                   "Low": [
                      "\"SFO\"",
                      "\"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;

This example pushes the predicate sourceairport = "SFO" AND destinationairport = "JFK" AND stops >= 0 to index scan.

Span for Low High Inclusion
sourceairport = "SFO" "SFO" "SFO" 3(BOTH)
destinationairport = "JFK" "JFK" "JFK" 3(BOTH)
stops >= 0 0 Unbounded 1(LOW)

The index selected has keys (sourceairport, destinationairport, stops). As the stop key high is unbounded, the high value of the earlier span is converted to successor and the inclusive bit is removed. The transformed spans looks as follows:

Span for Low High Inclusion
sourceairport = "SFO" "SFO" "SFO" 3(BOTH)
destinationairport = "JFK" "JFK" successor("JFK") 1(LOW)
stops >= 0 0 Unbounded 1(LOW)
The spans are then stitched in that order to create a composite span as follows:
Span for Low High Inclusion
sourceairport = "SFO" AND destinationairport = "JFK" AND stops >= 0 ["SFO","JFK",0] ["SFO",successor("JFK")] 1(LOW)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` 
WHERE sourceairport = "SFO" 
  AND destinationairport = "JFK" 
  AND stops >= 0;       
Result snippet
     "spans": [
          {
              "Range": {
                   "High": [
                      "\"SFO\"",
                      "successor(\"JFK\")"
                   ],
                   "Inclusion": 1,
                   "Low": [
                      "\"SFO\"",
                      "\"JFK\"",
                      "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 for Low High Inclusion
id = $1 $1 $1 3(BOTH)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id = $1;      
Result snippet
    "spans": [
          {
              "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;

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

Span for Low High Inclusion
id >= $1 AND id < $2 $1 $2 1(LOW)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id >= $1 AND id < $2;     
Result snippet
     "spans": [
          {
              "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 for Low High Inclusion
id = $1 $1 $1 3(BOTH)
id < $2 NULL $2 0(NEITHER)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id = $1 OR id < $2;      
Result snippet
      "spans": [
          {
              "Range": {
                  "High": [
                       "$1"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "$1"
                   ]
              },
              "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] ;

This example pushes the predicate id IN [$1, 10, $2] to the index scan.

Span for Low High Inclusion
id IN [$1, 10, $2] $1

10

$2

$1

10

$2

3(BOTH)

3(BOTH)

3(BOTH)

Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id IN [$1, 10, $2];      
Result snippet
    "spans": [
          {
              "Range": {
                  "High": [
                       "$1"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "$1"
                   ]
              },
              "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;  

This example pushes the predicate v.day = 0 to ARRAY index scan.

Span for Low High Inclusion
v.day = 0 0 0 3(BOTH)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` 
WHERE type = "route" 
  AND ANY v IN schedule SATISFIES v.day = 0 
END;  
Result snippet
      "spans": [
          {
              "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;

This example pushes the predicate v.day IN [1,2,3] to ARRAY index scan.

Span for Low High Inclusion
v.day IN [1,2,3] 1

2

3

1

2

3

3(BOTH)

3(BOTH)

3(BOTH)

Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` 
WHERE type = "route" 
  AND ANY v IN schedule SATISFIES v.day IN [1,2,3] 
END;  
Result snippet
      "spans": [
          {
              "Range": {
                  "High": [
                       "1"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "1"
                   ]
              },
              "Range": {
                  "High": [
                       "2"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "2"
                   ]
              },
              "Range": {
                  "High": [
                       "3"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "3"
                   ]
              }
          }
      ]      

Example 29: EQUALITY Predicate on Expression

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

This example does not push any predicate to index scan.

Span for Low High Inclusion
id NULL Unbounded 0(NEITHER)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE abs(id) = 10;      
Result snippet
     "spans": [
          {
                   "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 query pushes the predicate abs(id) = 10 to index scan.
Span for Low High Inclusion
abs(id) = 10 10 10 3(BOTH)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE abs(id) = 10;      
Result snippet
      "spans": [
          {
              "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);

This example pushes the predicate id <= 100 OR (id BETWEEN 50 AND 150) to index scan as two ranges.

Span for Low High Inclusion
id <= 100 NULL 100 2(HIGH)
id BETWEEN 50 AND 150 50 150 3(BOTH)
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id <= 100 OR (id BETWEEN 50 AND 150);      
Result snippet
     "spans": [
          {
              "Range": {
                  "High": [
                       "100"
                   ],
                   "Inclusion": 2,
                   "Low": [
                       "null"
                   ]
              }
          },
          {
              "Range": {
                  "High": [
                       "150"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "50"
                   ]
              }
          }
      ]      
The spans indicate that two range scans are being performed, and the ranges are overlapping with the values 50-100 being scanned twice. To eliminate duplicates N1QL does distinct operations on meta().id. When possible, rewriting the query may help eliminate duplicates.
Explain plan
EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id <= 150;
Result snippet
      "spans": [
          {
              "Range": {
                  "High": [
                       "150"
                   ],
                   "Inclusion": 2,
                   "Low": [
                       "null"
                   ]
              }
          }
      ]