Index Scans

concept

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

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.

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

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

Spans Overview

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

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

Examples

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.

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.

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

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

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)

Predicate

Span Low

Span High

Span Inclusion

id = 10

3 (BOTH)

id > 10

No upper bound

0 (NEITHER)

id <= 10

NULL

2 (HIGH)

Consider the plan for the following query:

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

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

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

Inclusion Number

Meaning

Description

NEITHER

Neither High nor Low fields are included.

LOW

Only Low fields are included.

HIGH

Only High fields are included.

BOTH

Both High and Low fields are included.

Example 1: EQUALITY Predicate

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

Span Range for	Low	High	Inclusion
`id = 10`	`10`	`10`	`3 (BOTH)`

id = 10

10

3 (BOTH)

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

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

Example 2: Inclusive One-Sided Range Predicate

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

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

Span Range for Low High Inclusion

Span Range for	Low	High	Inclusion
`id >= 10`	`10`	`Unbounded`	`1 (LOW)`

id >= 10

10

Unbounded

1 (LOW)

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

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

Example 3: Exclusive One-Sided Range Predicate

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

Span Range for	Low	High	Inclusion
`id > 10`	`10`	`Unbounded`	`0 (NEITHER)`

id > 10

10

Unbounded

0 (NEITHER)

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

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

Example 4: Inclusive One-Sided Range Predicate

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

Span Range for	Low	High	Inclusion
`id <= 10`	`NULL`	`10`	`2 (HIGH)`

id <= 10

NULL

10

2 (HIGH)

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

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

Example 5: Exclusive One-Sided Range Predicate

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

Span Range for	Low	High	Inclusion
`id < 10`	`NULL`	`10`	`0 (NEITHER)`

id < 10

NULL

10

0 (NEITHER)

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

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

Example 6: AND Predicate

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

Span Range for	Low	High	Inclusion
`id >= 10 AND id < 25`	`10`	`25`	`1 (LOW)`

id >= 10 AND id < 25

10

25

1 (LOW)

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

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

Example 7: Multiple AND Predicates

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

Span Range for

Low

High

Inclusion

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

3 (BOTH)

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

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

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

Span Range for Low High Inclusion

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

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:

Combined Low becomes highest of both Low values (NULL is the lowest.)
Combined High becomes lowest of both High values (Unbounded is the highest.)
Combined Inclusion becomes OR of corresponding inclusions of Step 1 and Step 2.
Repeat Steps 1 to 3 for each AND clause.

Example 8: AND Predicate Makes Empty

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

Span Range for	Low	High	Inclusion
`id > 10 AND id < 5`	`NULL`	`NULL`	`0 (NEITHER)`

id > 10 AND id < 5

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 airline WHERE id > 10 AND id < 5;

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

Example 9: BETWEEN Predicate

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

Span Range for	Low	High	Inclusion
`id BETWEEN 10 AND 25`	`10`	`25`	`3 (BOTH)`

id BETWEEN 10 AND 25

10

25

3 (BOTH)

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

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

Example 10: Simple OR Predicate

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

Span for	Low	High	Inclusion
`id = 10`	`10`	`10`	`3 (BOTH)`
`id = 20`	`20`	`20`	`3 (BOTH)`

id = 10

10

3 (BOTH)

id = 20

20

3 (BOTH)

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

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

Example 11: Simple IN Predicate

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

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

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

Span Range for Low High Inclusion

Span Range for	Low	High	Inclusion
`id = 10`	`10`	`10`	`3 (BOTH)`
`id = 20`	`20`	`20`	`3 (BOTH)`

id = 10

10

3 (BOTH)

id = 20

20

3 (BOTH)

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

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

Example 12: OR, BETWEEN, AND Predicates

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

Span Range for	Low	High	Inclusion
`id BETWEEN 10 AND 25`	`10`	`25`	`3 (BOTH)`
`id > 50 AND id <= 60`	`50`	`60`	`2 (HIGH)`

id BETWEEN 10 AND 25

10

25

3 (BOTH)

id > 50 AND id <= 60

50

60

2 (HIGH)

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

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

Example 13: NOT Predicate

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

Span Range for	Low	High	Inclusion
`id < 10`	`NULL`	`10`	`0 (NEITHER)`
`id > 10`	`10`	`Unbounded`	`0 (NEITHER)`

id < 10

NULL

10

0 (NEITHER)

id > 10

10

Unbounded

0 (NEITHER)

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

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

Example 14: NOT, AND Predicates

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

Span Range for	Low	High	Inclusion
`id < 10`	`NULL`	`10`	`0 (NEITHER)`
`id >= 25`	`25`	`Unbounded`	`1 (LOW)`

id < 10

NULL

10

0 (NEITHER)

id >= 25

25

Unbounded

1 (LOW)

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

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

Example 15: EQUALITY Predicate on String Type

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

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

Span Range for Low High Inclusion

Span Range for	Low	High	Inclusion
`name = "American Airlines"`	`"American Airlines"`	`"American Airlines"`	`3 (BOTH)`

name = "American Airlines"

"American Airlines"

3 (BOTH)

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

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

Example 16: Range Predicate on String Type

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

Span Range for	Low	High	Inclusion
`name >= "American Airlines" AND name <= "United Airlines"`	`"American Airlines"`	`"United Airlines"`	`3 (BOTH)`

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

"American Airlines"

"United Airlines"

3 (BOTH)

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

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

Example 17: LIKE Predicate

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

Span Range for	Low	High	Inclusion
`name LIKE "American%"`	`"American"`	`"Americao"`	`1 (LOW)`

name LIKE "American%"

"American"

"Americao"

1 (LOW)

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

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

Example 18: LIKE Predicate

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

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

name LIKE "%American%"

""

"[]"

1 (LOW)

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

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

Example 19: AND Predicate with Composite Index

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

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

sourceairport = "SFO"

"SFO"

3 (BOTH)

destinationairport = "JFK"

"JFK"

3 (BOTH)

stops BETWEEN 0 AND 2

0

2

3 (BOTH)

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

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

Example 20: AND Predicate with Composite Index

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

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

sourceairport IN ["SFO", "SJC"]

"SFO"

"SJC"

"SFO"

"SJC"

3 (BOTH)

destinationairport = "JFK"

"JFK"

3 (BOTH)

stops

0

3 (BOTH)

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

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

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

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

Span Range for	Low	High	Inclusion
`sourceairport = "SFO"`	`"SFO"`	`"SFO"`	`3 (BOTH)`
`destinationairport = "JFK"`	`"JFK"`	`"JFK"`	`3 (BOTH)`

sourceairport = "SFO"

"SFO"

3 (BOTH)

destinationairport = "JFK"

"JFK"

3 (BOTH)

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

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

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

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

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

sourceairport = "SFO"

"SFO"

3 (BOTH)

destinationairport = "JFK"

"JFK"

3 (BOTH)

stops >= 0

0

Unbounded

1 (LOW)

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

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

Example 23: EQUALITY Predicate with Query Parameters

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

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

Span Range for Low High Inclusion

Span Range for	Low	High	Inclusion
`id = $1`	`$1`	`$1`	`3 (BOTH)`

id = $1

$1

3 (BOTH)

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

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

Example 24: AND Predicate with Query Parameters

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

Span Range for	Low	High	Inclusion
`id >= $1 AND id < $2`	`$1`	`$2`	`1 (LOW)`

id >= $1 AND id < $2

$1

$2

1 (LOW)

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

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

Example 25: OR Predicate with Query Parameters

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

Span Range for	Low	High	Inclusion
`id = $1`	`$1`	`$1`	`3 (BOTH)`
`id < $2`	`NULL`	`$2`	`0 (NEITHER)`

id = $1

$1

3 (BOTH)

id < $2

NULL

$2

0 (NEITHER)

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

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

Example 26: IN Predicate with Query Parameters

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

Span Range for	Low	High	Inclusion
`id IN [$1, 10, $2]`	`$1` `10` `$2`	`$1` `10` `$2`	`3 (BOTH)` `3 (BOTH)` `3 (BOTH)`

id IN [$1, 10, $2]

$1

10

$2

$1

10

$2

3 (BOTH)

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

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

Example 27: ANY Predicate

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

Span Range for	Low	High	Inclusion
`v.day = 0`	`0`	`0`	`3 (BOTH)`

v.day = 0

0

3 (BOTH)

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

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

Example 28: ANY Predicate

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

Span Range for	Low	High	Inclusion
`v.day IN [1,2,3]`	`1` `2` `3`	`1` `2` `3`	`3 (BOTH)` `3 (BOTH)` `3 (BOTH)`

v.day IN [1,2,3]

1

2

3

1

2

3

3 (BOTH)

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

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

Example 29: EQUALITY Predicate on Expression

The following examples don’t have the right indexes, or the queries need to be modified to produce an optimal plan.

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

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

Span Range for Low High Inclusion

Span Range for	Low	High	Inclusion
`id`	`NULL`	`Unbounded`	`0 (NEITHER)`

id

NULL

Unbounded

0 (NEITHER)

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

// ...
          "spans": [
            {
              "range": [
                {
                  "inclusion": 0,
                  "low": "null"
                }
              ]
            }
          ],
// ...

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

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

Span Range for	Low	High	Inclusion
`abs(id) = 10`	`10`	`10`	`3 (BOTH)`

abs(id) = 10

10

3 (BOTH)

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

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

Example 30: Overlapping Predicates

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

Span Range for	Low	High	Inclusion
`id <= 100`	`NULL`	`100`	`2 (HIGH)`
`id BETWEEN 50 AND 150`	`50`	`150`	`3 (BOTH)`

id <= 100

NULL

100

2 (HIGH)

id BETWEEN 50 AND 150

50

150

3 (BOTH)

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

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

Summary

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