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