---
title: Scans
description: This section discusses how index spans are generated from query
  predicates and provides a number of examples.
pubDate: 2026-08-17T09:53:44.266Z
antora:
  editUrl: https://github.com/couchbaselabs/docs-devex/edit/release/7.2/modules/learn/pages/services-and-indexes/indexes/index-scans.adoc
  xref: xref:7.2@server:learn:services-and-indexes/indexes/index-scans.adoc[]
---

[Consult the llms.txt file for a full list of contents](/llms.txt)
[View original HTML](/server/7.2/learn/services-and-indexes/indexes/index-scans.html)

# 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 SQL++ 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 SQL++ supports enhanced SQL for JSON, to make query processing easier.

## [](#query-execution-details)Query Execution: Details

Applications and database drivers submit the SQL++ 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 Overview](../../_images/query-execution-753745aceedd624171a073ca90962b01d3d35924.svg) 

Figure 1\. Query Execution Overview

### [](#inside%5Fa%5Fquery%5Fnode)Inside a Query Node

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 Query Service skips the Sort phase when it does not include an ORDER BY clause; and the Scan-Fetch-Join phases execute multiple times for correlated subqueries.

![Query Execution Phases](../../_images/query-service-8eb5c0385acd8f41381d65fb79a6c914f2aee639.svg) 

Figure 2\. Query Execution Phases

During query execution, the query planner selects the most appropriate index path based on the query predicates (filters). Once the index path is chosen, the query engine requests the Index Service to perform a scan. This scan is defined by a specific range of values that the query engine needs to retrieve. These ranges are represented as spans in the query plan. A span defines the start and end values for the scan, along with whether these values are inclusive or exclusive. It plays a critical role in optimizing both the query plan generation and its execution. For more information about index spans, see [Spans Overview](#spans-overview).

## [](#spans-overview)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)Examples

The examples in this section use the travel-sample dataset which is shipped with Couchbase Server. For instructions on how to install the sample bucket, see [Sample Buckets](../../../manage/manage-settings/install-sample-buckets.md).

To use the examples on this page, you must set the query context to the `inventory` scope in the travel sample dataset. For more information, see [Query Context](../../../n1ql/n1ql-intro/queriesandresults.md#query-context).

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

```sqlpp
CREATE INDEX idx_airline_id ON airline(`id`);

CREATE INDEX idx_airline_name ON airline(`name`);

CREATE INDEX idx_route_src_dst_stops
ON route(sourceairport, destinationairport, stops);

CREATE INDEX idx_route_sched
ON route(DISTINCT ARRAY v.day FOR v IN schedule END);
```

### [](#example-translations)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:

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id = 10;
```

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

```json
[
  {
    "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 airline WHERE id = 10;"
  }
]
```

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

In SQL++, 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.    |

### [](#ex1-equality)Example 1: EQUALITY Predicate

```sqlpp
SELECT meta().id FROM 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)  |

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id = 10;
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "10",
                  "inclusion": 3,
                  "low": "10"
                }
              ]
            }
          ],
// ...
```

### [](#ex2-one-sided-range)Example 2: Inclusive One-Sided Range Predicate

```sqlpp
SELECT meta().id FROM 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)   |

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id >= 10;
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "inclusion": 1,
                  "low": "10"
                }
              ]
            }
          ],
// ...
```

### [](#ex3-exclusive-one-sided-range)Example 3: Exclusive One-Sided Range Predicate

```sqlpp
SELECT meta().id FROM 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) |

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id > 10;
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "inclusion": 0,
                  "low": "10"
                }
              ]
            }
          ],
// ...
```

### [](#ex4-inclusive-one-sided-range)Example 4: Inclusive One-Sided Range Predicate

```sqlpp
SELECT meta().id FROM 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)  |

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id <= 10;
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "10",
                  "inclusion": 2,
                  "low": "null"
                }
              ]
            }
          ],
// ...
```

### [](#ex5-exclusive-one-sided-range)Example 5: Exclusive One-Sided Range Predicate

```sqlpp
SELECT meta().id FROM 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) |

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id < 10;
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "10",
                  "inclusion": 0,
                  "low": "null"
                }
              ]
            }
          ],
// ...
```

### [](#ex6-and)Example 6: AND Predicate

```sqlpp
SELECT meta().id FROM 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)   |

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id >=10 AND id < 25;
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "25",
                  "inclusion": 1,
                  "low": "10"
                }
              ]
            }
          ],
// ...
```

### [](#ex7-multiple-and)Example 7: Multiple AND Predicates

```sqlpp
SELECT meta().id FROM 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)  |

```sqlpp
EXPLAIN SELECT meta().id FROM airline
WHERE id >=10 AND id < 25 AND id <= 20;
```

```json
// ...
          "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](#ex8-and-makes-empty).

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

### [](#ex8-and-makes-empty)Example 8: AND Predicate Makes Empty

```sqlpp
SELECT meta().id FROM 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.

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id > 10 AND id < 5;
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "null",
                  "inclusion": 0,
                  "low": "null"
                }
              ]
            }
          ],
// ...
```

### [](#ex9-between)Example 9: BETWEEN Predicate

```sqlpp
SELECT meta().id FROM 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)  |

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id BETWEEN 10 AND 25;
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "25",
                  "inclusion": 3,
                  "low": "10"
                }
              ]
            }
          ],
// ...
```

### [](#ex10-simple-or)Example 10: Simple OR Predicate

```sqlpp
SELECT meta().id FROM 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)  |

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id = 10 OR id = 20;
```

```json
// ...
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "10",
                    "inclusion": 3,
                    "low": "10"
                  }
                ]
              },
              {
                "exact": true,
                "range": [
                  {
                    "high": "20",
                    "inclusion": 3,
                    "low": "20"
                  }
                ]
              }
            ],
// ...
```

### [](#ex11-simple-in)Example 11: Simple IN Predicate

```sqlpp
SELECT meta().id FROM 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.

> [!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)  |

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id IN [10, 20];
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "10",
                  "inclusion": 3,
                  "low": "10"
                }
              ]
            },
            {
              "exact": true,
              "range": [
                {
                  "high": "20",
                  "inclusion": 3,
                  "low": "20"
                }
              ]
            }
          ],
// ...
```

### [](#ex12-or-between-and)Example 12: OR, BETWEEN, AND Predicates

```sqlpp
SELECT meta().id FROM 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)  |

```sqlpp
EXPLAIN SELECT meta().id FROM airline
WHERE (id BETWEEN 10 AND 25)
   OR (id > 50 AND id <= 60);
```

```json
// ...
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "25",
                    "inclusion": 3,
                    "low": "10"
                  }
                ]
              },
              {
                "exact": true,
                "range": [
                  {
                    "high": "60",
                    "inclusion": 2,
                    "low": "50"
                  }
                ]
              }
            ],
// ...
```

### [](#ex13-not)Example 13: NOT Predicate

```sqlpp
SELECT meta().id FROM 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) |

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id <> 10;
```

```json
// ...
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "10",
                    "inclusion": 0,
                    "low": "null"
                  }
                ]
              },
              {
                "exact": true,
                "range": [
                  {
                    "inclusion": 0,
                    "low": "10"
                  }
                ]
              }
            ],
// ...
```

### [](#ex14-not-and)Example 14: NOT, AND Predicates

```sqlpp
SELECT meta().id FROM 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)     |

```sqlpp
EXPLAIN SELECT meta().id FROM airline
WHERE NOT (id >= 10 AND id < 25);
```

```json
// ...
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "10",
                    "inclusion": 0,
                    "low": "null"
                  }
                ]
              },
              {
                "exact": true,
                "range": [
                  {
                    "inclusion": 1,
                    "low": "25"
                  }
                ]
              }
            ],
// ...
```

### [](#ex15-equality-string)Example 15: EQUALITY Predicate on String Type

```sqlpp
SELECT meta().id FROM 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)  |

```sqlpp
EXPLAIN SELECT meta().id FROM airline
WHERE name = "American Airlines";
```

```json
// ...
         "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "\"American Airlines\"",
                  "inclusion": 3,
                  "low": "\"American Airlines\""
                }
              ]
            }
          ],
// ...
```

### [](#ex16-range-string)Example 16: Range Predicate on String Type

```sqlpp
SELECT meta().id FROM 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)  |

```sqlpp
EXPLAIN SELECT meta().id FROM airline
  WHERE name >= "American Airlines"
    AND name <= "United Airlines";
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "\"United Airlines\"",
                  "inclusion": 3,
                  "low": "\"American Airlines\""
                }
              ]
            }
          ],
// ...
```

### [](#ex17-like-1)Example 17: LIKE Predicate

```sqlpp
SELECT meta().id FROM 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 SQL++ 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)   |

```sqlpp
EXPLAIN SELECT meta().id FROM airline
WHERE name LIKE "American%";
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "\"Americao\"",
                  "inclusion": 1,
                  "low": "\"American\""
                }
              ]
            }
          ],
// ...
```

### [](#ex18-like-2)Example 18: LIKE Predicate

```sqlpp
SELECT meta().id FROM 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 SQL++ collation order.

| Span Range for         | Low | High   | Inclusion |
| ---------------------- | --- | ------ | --------- |
| name LIKE "%American%" | ""  | "\[\]" | 1 (LOW)   |

```sqlpp
EXPLAIN SELECT meta().id FROM airline
WHERE name LIKE "%American%";
```

```json
// ...
          "spans": [
            {
              "range": [
                {
                  "high": "[]",
                  "inclusion": 1,
                  "low": "\"\""
                }
              ]
            }
          ],
// ...
```

### [](#ex19-and-composite-index-1)Example 19: AND Predicate with Composite Index

```sqlpp
SELECT meta().id FROM 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)  |

```sqlpp
EXPLAIN SELECT meta().id FROM route
WHERE sourceairport = "SFO"
  AND destinationairport = "JFK"
  AND stops BETWEEN 0 AND 2;
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "\"SFO\"",
                  "inclusion": 3,
                  "low": "\"SFO\""
                },
                {
                  "high": "\"JFK\"",
                  "inclusion": 3,
                  "low": "\"JFK\""
                },
                {
                  "high": "2",
                  "inclusion": 3,
                  "low": "0"
                }
              ]
            }
          ],
// ...
```

### [](#ex20-and-composite-index-2)Example 20: AND Predicate with Composite Index

```sqlpp
SELECT meta().id FROM 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)          |

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

```json
// ...
          "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"
                }
              ]
            }
          ],
// ...
```

### [](#ex21-composite-and-trailing-keys-missing)Example 21: Composite AND Predicate with Trailing Keys Missing in Predicate

```sqlpp
SELECT meta().id FROM 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)  |

```sqlpp
EXPLAIN SELECT meta().id FROM route
WHERE sourceairport = "SFO"
  AND destinationairport = "JFK";
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "\"SFO\"",
                  "inclusion": 3,
                  "low": "\"SFO\""
                },
                {
                  "high": "\"JFK\"",
                  "inclusion": 3,
                  "low": "\"JFK\""
                }
              ]
            }
          ],
// ...
```

### [](#ex22-composite-and-unbounded-high-trailing)Example 22: Composite AND Predicate with Unbounded High of Trailing Key

```sqlpp
SELECT meta().id FROM 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)   |

```sqlpp
EXPLAIN SELECT meta().id FROM route
WHERE sourceairport = "SFO"
  AND destinationairport = "JFK"
  AND stops >= 0;
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "\"SFO\"",
                  "inclusion": 3,
                  "low": "\"SFO\""
                },
                {
                  "high": "\"JFK\"",
                  "inclusion": 3,
                  "low": "\"JFK\""
                },
                {
                  "inclusion": 1,
                  "low": "0"
                }
              ]
            }
          ],
// ...
```

### [](#ex23-equality-query-params)Example 23: EQUALITY Predicate with Query Parameters

```sqlpp
SELECT meta().id FROM 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)  |

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id = $1;
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "$1",
                  "inclusion": 3,
                  "low": "$1"
                }
              ]
            }
          ],
// ...
```

### [](#ex24-and-query-params)Example 24: AND Predicate with Query Parameters

```sqlpp
SELECT meta().id FROM 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)   |

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id >= $1 AND id < $2;
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "$2",
                  "inclusion": 1,
                  "low": "$1"
                }
              ]
            }
          ],
// ...
```

### [](#ex25-or-query-params)Example 25: OR Predicate with Query Parameters

```sqlpp
SELECT meta().id FROM 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) |

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id = $1 OR id < $2;
```

```json
// ...
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "$1",
                    "inclusion": 3,
                    "low": "$1"
                  }
                ]
              },
              {
                "exact": true,
                "range": [
                  {
                    "high": "$2",
                    "inclusion": 0,
                    "low": "null"
                  }
                ]
              }
            ],
// ...
```

### [](#ex26-in-query-params)Example 26: IN Predicate with Query Parameters

```sqlpp
SELECT meta().id FROM 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) |

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE id IN [$1, 10, $2];
```

```json
// ...
            "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"
                  }
                ]
              }
            ],
// ...
```

### [](#ex27-any-1)Example 27: ANY Predicate

```sqlpp
SELECT meta().id FROM 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)  |

```sqlpp
EXPLAIN SELECT meta().id FROM route
WHERE ANY v IN schedule SATISFIES v.day = 0
END;
```

```json
// ...
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "0",
                    "inclusion": 3,
                    "low": "0"
                  }
                ]
              }
            ],
// ...
```

### [](#ex28-any-2)Example 28: ANY Predicate

```sqlpp
SELECT meta().id FROM 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) |

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

```json
// ...
            "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"
                  }
                ]
              }
            ],
// ...
```

### [](#ex29-equality-on-expr)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.

```sqlpp
SELECT meta().id FROM 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) |

```sqlpp
EXPLAIN SELECT meta().id FROM airline WHERE abs(id) = 10;
```

```json
// ...
          "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:

```sqlpp
CREATE INDEX `idx_airline_absid` ON 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)  |

```sqlpp
EXPLAIN SELECT meta().id FROM airline
USE INDEX (idx_airline_absid)
WHERE abs(id) = 10;
```

```json
// ...
          "spans": [
            {
              "exact": true,
              "range": [
                {
                  "high": "10",
                  "inclusion": 3,
                  "low": "10"
                }
              ]
            }
          ],
// ...
```

### [](#ex30-overlap)Example 30: Overlapping Predicates

```sqlpp
SELECT meta().id FROM 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)  |

```sqlpp
EXPLAIN SELECT meta().id FROM airline
WHERE id <= 100
  OR (id BETWEEN 50 AND 150);
```

```json
// ...
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "100",
                    "inclusion": 2,
                    "low": "null"
                  }
                ]
              },
              {
                "exact": true,
                "range": [
                  {
                    "high": "150",
                    "inclusion": 3,
                    "low": "50"
                  }
                ]
              }
            ],
// ...
```

## [](#summary)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.