Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Understanding Index Scans in Couchbase N1QL Query

DZone's Guide to

Understanding Index Scans in Couchbase N1QL Query

Here's an in-depth look at 30 examples of N1QL predicates designed to efficiently scan your indexes.

Free Resource

Traditional relational databases weren’t designed for today’s customers. Learn about the world’s first NoSQL Engagement Database purpose-built for the new era of customer experience.

CouchbaseN1QL 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. N1QL supports enhanced SQL for JSON to make query processing easier.

Applications and database drivers submit the N1QL query to one of the available Query nodes on a cluster. The Query node analyzes the query, uses metadata on underlying objects to figure out the optimal execution plan, which it then executes. During execution, depending on the query, using applicable indexes, query node works with index and data nodes to retrieve and perform the planned operations. Because Couchbase is a modular clustered database, you scale out data, index, and query services to fit your performance and availability goals.

Query Execution: Inside View



This figure shows all the possible phases a SELECT query goes through to return the results. Not all queries need to go through every phase, some go through many of these phases multiple times. For example, sort phase can be skipped when there is no ORDER BY clause in the query; scan-fetch-join phase 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. In this article, 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 predicate indicates the subset of the data interested. 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 Indexer. Spans simply express the predicates in terms of data ranges.

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 query below:

EXPLAIN select meta().id from `travel-sample` where id = 10;


The spans can be seen as part of IndexScan section of the explain  for the query:

{
    "requestID": "4f64d56a-0db6-4d1e-8868-36bfe11146cf",
    "signature": "json",
    "results": [
        {
            "plan": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "IndexScan",
                        "covers": [
                            "cover ((`travel-sample`.`id`))",
                            "cover ((meta(`travel-sample`).`id`))"
                        ],
                        "index": "idx_id",
                        "index_id": "8ad897f8afb165ef",
                        "keyspace": "travel-sample",
                        "namespace": "default",
                        "spans": [
                            {
                                "Range": {
                                    "High": [
                                        "10"
                                    ],
                                    "Inclusion": 3,
                                    "Low": [
                                        "10"
                                    ]
                                }
                            }
                        ],
                        "using": "gsi"
                    }
                ]
                ……...
            }
        }
    ]
}


In N1QL, index scan requests are based on range. So, the index spans consists of one or more ranges. Each range will have start value, end value and whether to include the start or end value.

  • “High” field of Range indicates the end value. If High is missing, then there is no upper bound.

  • “Low” field of Range indicates start value. If Low is missing, the scan starts with null (i.e. null is lowest value, because MISSING values are not indexed).

  • Inclusion indicates values of Low or High included

Inclusion


Meaning

0    

NEITHER

Neither Low nor High included

1    

LOW

Low included

2

HIGH

High included

3

BOTH

Both Low and High included


To run the examples Install travel-sample sample bucket and create the following indexes.

CREATE INDEX `idx_id` ON `travel-sample`(`id`);
CREATE INDEX `idx_name` ON `travel-sample`(`name`);
CREATE INDEX `idx_saiport_dairport_stops`  ON `travel-sample` (`sourceairport`, `destinationairport`, `stops`);
CREATE INDEX idx_sched ON `travel-sample`(DISTINCT ARRAY v.day FOR v IN schedule END) WHERE type = "route";


Example 1: EQUALITY Predicate

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


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

Span for

Low

High

Inclusion

id = 10

10

10

3 (BOTH)


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


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


Example 2: Inclusive One-Sided Range Predicate

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


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

Span for

Low

High

Inclusion

id >= 10

10

unbounded

1 (LOW)


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


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


Example 3: Exclusive One-Sided Range Predicate

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


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

Span for

Low

High

Inclusion

id > 10

10

unbounded

0 (NEITHER)


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


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


Example 4: Inclusive One-sided Range Predicate

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


In this example, the predicate id <= 10 pushed to index scan. The query predicate doesn’t contain an explicit start value, when the field is present in predicate it is implicit non-null values. So it is implicit the start value will be non-inclusive null value.

Span for

Low

High

Inclusion

id <= 10

null

10

2 (HIGH)


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


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


Example 5: Exclusive One-Sided Range Predicate

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


In this example, the predicate id < 10 pushed to index scan. The query predicate doesn’t contain explicit start value, when the field is present in predicate it is implicit non-null values. So it is implicit the start value will be non-inclusive null value.

Span for

Low

High

Inclusion

id < 10

null

10

0 (NEITHER)


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


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


Example 6: AND Predicate

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


In this example, the predicate id >= 10 AND id < 25 pushed to index scan.

Span for

Low

High

Inclusion

id >= 10 AND id < 25

10

25

1 (LOW)


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


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


Example 7: Multiple AND Predicates

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


In this example, the predicate id >= 10 AND id < 25 AND id <= 20 pushed to index scan.

Span for

Low

High

Inclusion

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

10

20

3 (BOTH)


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


      "spans": [
          {
              "Range": {
                  "High": [
                       "20"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "10"
                   ]
              }
          }
      ]


Observe that optimizer created the span without the (id < 25) because there is an AND predicate (id <= 20) makes the other predicate redundant.

Internally, optimizer breaks down each predicate and then combines it in a logically consistent manner. FYI: If this is too detailed for now, you can skip over to Example 8.

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


  1. Combined Low becomes highest of both Low’s (null is lowest).

  2. Combined High becomes lowest of both High’s (unbounded is highest).

  3. Combined Inclusion becomes OR of corresponding inclusions of step 1 and 2.

  4. Repeat the steps 1-3 for each AND clause.

Example 8: AND Predicate Makes Empty

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


In this example, the predicate id > 10 AND id < 5 pushed to index scan.

Span for

Low

High

Inclusion

id > 10 AND id < 5

null

null

0 (NEITHER)


This is a special case. Span will be Low: 10, High:5, 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.

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


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


Example 9: BETWEEN Predicate

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


In this example, the predicate id BETWEEN 10 AND 25 (i.e. id >=10 AND id <=25) pushed to index scan.

Span for

Low

High

Inclusion

id BETWEEN 10 AND 25

10

25

3 (BOTH)


EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id BETWEEN 10 AND 25;


      "spans": [
          {
              "Range": {
                  "High": [
                       "25"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "10"
                   ]
              }
          }
      ]


Example 10: Simple OR Predicate

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

In this example, the predicate id = 10 OR id = 20 produce two independent ranges and both of them are pushed to index scan. Duplicate ranges are eliminated, but overlaps are not eliminated.

Span for

Low

High

Inclusion

id = 10

10

10

3 (BOTH)

Id = 20

20

20

3 (BOTH)


EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id = 10 OR id = 20;


      "spans": [
          {
              "Range": {
                  "High": [
                       "10"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "10"
                   ]
              }
          },
          {
              "Range": {
                  "High": [
                       "20"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "20"
                   ]
              }
          }
      ]


Example 11: Simple IN Predicate

SELECT meta().id FROM `travel-sample` WHERE id IN [10, 20];


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

In Couchbase Server 4.5 up to 8192 IN elements are pushed as a separate range to the indexer. If the number of elements more than 8192 it does full scan on that key.

Span for

Low

High

Inclusion

id = 10

10

10

3 (BOTH)

Id = 20

20

20

3 (BOTH)


EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id IN [10, 20];


      "spans": [
          {
              "Range": {
                  "High": [
                       "10"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "10"
                   ]
              }
          },
          {
              "Range": {
                  "High": [
                       "20"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "20"
                   ]
              }
          }
      ]


Example12: OR, BETWEEN, AND Predicate

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


In this example, the predicate (id BETWEEN 10 AND 25) OR (id > 50 AND id <= 60) pushed to index scan.

Span for

Low

High

Inclusion

id between 10 and 25

10

25

3 (BOTH)

Id > 50 AND id <= 60

50

60

2 (HIGH)


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


      "spans": [
          {
              "Range": {
                  "High": [
                       "25"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "10"
                   ]
              }
          },
          {
              "Range": {
                  "High": [
                       "60"
                   ],
                   "Inclusion": 2,
                   "Low": [
                       "50"
                   ]
              }
          }
      ]


Example 13: NOT Predicate

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


In this example, the predicate id <> 10 is transformed to id < 10 OR id > 10 and pushed to index scan.

Span for

Low

High

Inclusion

id < 10

null

10

0 (NEITHER)

Id > 10

10

unbounded

0 (NEITHER)


EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id <> 10;
      "spans": [
          {
              "Range": {
                  "High": [
                       "10"
                   ],
                   "Inclusion": 0,
                   "Low": [
                       "null"
                   ]
              }
          },
          {
              "Range": {
                   "Inclusion": 0,
                   "Low": [
                       "10"
                   ]
              }
          }
      ]


Example 14: NOT, AND Predicate

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


In this example, the predicate NOT (id >= 10 AND id < 25) is transformed to id < 10 OR id >=25 and pushed to index scan.

Span for

Low

High

Inclusion

id < 10

null

10

0 (NEITHER)

Id >= 25

25

unbounded

1 (LOW)


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


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


Example 15: EQUALITY Predicate on String Type

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


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

Span for

Low

High

Inclusion

name = "American Airlines"

"American Airlines"

"American Airlines"

3 (BOTH)


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


      "spans": [
          {
              "Range": {
                  "High": [
                       "\"American Airlines\""
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "\"American Airlines\""
                   ]
              }
          }
      ]

      

Example 16: Range Predicate on String Type

SELECT meta().id FROM `travel-sample` WHERE name >= "American Airlines" AND name <= "United Airlines";


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

Span for

Low

High

Inclusion

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

"American Airlines"

"United Airlines"

3 (BOTH)


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


 "spans": [
          {
              "Range": {
                  "High": [
                       "\"United Airlines\""
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "\"American Airlines\""
                   ]
              }
          }
      ]


Example 17: LIKE Predicate

SELECT meta().id FROM `travel-sample` WHERE name LIKE "American%";


In this example, the predicate name LIKE “American%” is transformed to name >= “American” AND name < “Americao” (i.e. "Americao" is next N1QL collation order of "American") and pushed to index scan. In LIKE predicate % means match with any number of any character.

Span for

Low

High

Inclusion

name LIKE "American%"

"American"

"Americao"

1 (LOW)


EXPLAIN SELECT meta().id FROM `travel-sample` WHERE name LIKE "American%";


      "spans": [
          {
              "Range": {
                  "High": [
                       "\"Americao\""
                   ],
                   "Inclusion": 1,
                   "Low": [
                       "\"American\""
                   ]
              }
          }
      ]


Example 18: LIKE Predicate

SELECT meta().id FROM `travel-sample` WHERE name LIKE "%American%";


In this example, the predicate name LIKE “%American%” is transformed and pushed to index scan. In LIKE predicate ‘%’  is leading portion of the string so we can’t push any portion of the string to indexer. “” is the lowest string. [] is empty array and is greater than every string value in N1QL collation order.

Span for

Low

High

Inclusion

name LIKE "%American%"

""

"[]"

1 (LOW)


EXPLAIN SELECT meta().id FROM `travel-sample` WHERE name LIKE "%American%";


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


Example 19: AND Predicate With Composite Index

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


In this example, the predicate sourceairport = "SFO" AND destinationairport = "JFK" and stops BETWEEN 0 AND 2 pushed to index scan.

Span for

Low

High

Inclusion

sourceairport = "SFO"

"SFO"

"SFO"

3 (BOTH)

destinationairport = "JFK"

"JFK"

"JFK"

3 (BOTH)

stops

0

2

3 (BOTH)


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

Span for

Low

High

Inclusion

sourceairport = "SFO" AND destinationairport = "JFK" and stops BETWEEN 0 AND 2

["SFO","JFK",0]

["SFO","JFK",2]

3 (BOTH)


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


      "spans": [
          {
              "Range": {
                   "High": [
                      "\"SFO\"",
                      "\"JFK\"",
                      "2"
                   ],
                   "Inclusion": 3,
                   "Low": [
                      "\"SFO\"",
                      "\"JFK\"",
                      "0"
                   ]
              }
          }
      ]


Example 20: AND Predicate With Composite Index

SELECT meta().id from `travel-sample` WHERE sourceairport IN ["SFO", "SJC"] AND destinationairport = "JFK" AND stops = 0;


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

Span for

Low

High

Inclusion

sourceairport IN ["SFO", “SJC”]

"SFO"

“SJC”

"SFO"

“SJC”

3 (BOTH)

3 (BOTH)

destinationairport = "JFK"

"JFK"

"JFK"

3 (BOTH)

stops

0

0

3 (BOTH)


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

Span for

Low

High

Inclusion

sourceairport IN [ "SFO", ”SJC”]  AND destinationairport = "JFK" and stops = 0

["SFO","JFK",0]

[“SJC”,”JFK”,0]

["SFO","JFK",2]

[“SJC”,”JFK”,0]

3 (BOTH)

3 (BOTH)


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


      "spans": [
          {
              "Range": {
                   "High": [
                      "\"SFO\"",
                      "\"JFK\"",
                      "0"
                   ],
                   "Inclusion": 3,
                   "Low": [
                      "\"SFO\"",
                      "\"JFK\"",
                      "0"
                   ]
              }
          },
              "Range": {
                   "High": [
                      "\"SJC\"",
                      "\"JFK\"",
                      "0"
                   ],
                   "Inclusion": 3,
                   "Low": [
                      "\"SJC\"",
                      "\"JFK\"",
                      "0"
                   ]
              }
          }
      ]


Example 21: Composite AND Predicate With Trailing Keys Are Missing in Predicate

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


In this example, the predicate sourceairport = "SFO" AND destinationairport = "JFK" pushed to index scan.

Span for

Low

High

Inclusion

sourceairport = "SFO"

"SFO"

"SFO"

3 (BOTH)

destinationairport = "JFK"

"JFK"

"JFK"

3 (BOTH)


The index selected has keys (sourceairport, destinationairport, stops). As the stop key predicate is missing in the query the one before span high value is converted to successor and removed inclusive bit. The transformed spans are

Span for

Low

High

Inclusion

sourceairport = "SFO"

"SFO"

"SFO"

3 (BOTH)

destinationairport = "JFK"

"JFK"

successor("JFK")

1 (LOW)


Then spans are stitched in that order create composite span as follows.

Span for

Low

High

Inclusion

sourceairport = "SFO" AND destinationairport = "JFK"

["SFO","JFK"]

["SFO",successor("JFK")]

1 (LOW)


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


     "spans": [
          {
              "Range": {
                   "High": [
                      "\"SFO\"",
                      "successor(\"JFK\")"
                   ],
                   "Inclusion": 1,
                   "Low": [
                      "\"SFO\"",
                      "\"JFK\""
                   ]
              }
          }
      ]


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

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


In this example, the predicate sourceairport = "SFO" AND destinationairport = "JFK" AND stops >= 0 pushed to index scan.

Span for

Low

High

Inclusion

sourceairport = "SFO"

"SFO"

"SFO"

3 (BOTH)

destinationairport = "JFK"

"JFK"

"JFK"

3 (BOTH)

stops >= 0

0

unbounded

1 (LOW)


The index selected has keys (sourceairport, destinationairport, stops). As the stop key high is unbounded before span high value is converted to successor and removed inclusive bit. The transformed spans are

Span for

Low

High

Inclusion

sourceairport = "SFO"

"SFO"

"SFO"

3 (BOTH)

destinationairport = "JFK"

"JFK"

successor("JFK")

1 (LOW)

stops >= 0

0

unbounded

1 (LOW)


Then spans are stitched in that order create composite span as follows.

Span for

Low

High

Inclusion

sourceairport = "SFO" AND destinationairport = "JFK" AND stops >= 0

["SFO","JFK",0]

["SFO",successor("JFK")]

1 (LOW)


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


     "spans": [
          {
              "Range": {
                   "High": [
                      "\"SFO\"",
                      "successor(\"JFK\")"
                   ],
                   "Inclusion": 1,
                   "Low": [
                      "\"SFO\"",
                      "\"JFK\"",
                      "0"
                   ]
              }
          }
      ]


Example 23: Equality Predicate With Query Parameters

SELECT meta().id FROM `travel-sample` WHERE id = $1;


In this example, the predicate id = $1 pushed to index scan.

Span for

Low

High

Inclusion

id = $1

$1

$1

3 (BOTH)


EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id = $1;


    "spans": [
          {
              "Range": {
                  "High": [
                       "$1"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "$1"
                   ]
              }
          }
      ]


Example 24: AND Predicate With Query Parameters

SELECT meta().id FROM `travel-sample` WHERE id >= $1 AND id < $2;


In this example, the predicate id >= $1 AND id < $2 pushed to index scan.

Span for

Low

High

Inclusion

id >= $1 AND id < $2

$1

$2

1 (LOW)


EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id >= $1 AND id < $2;


     "spans": [
          {
              "Range": {
                  "High": [
                       "$2"
                   ],
                   "Inclusion": 1,
                   "Low": [
                       "$1"
                   ]
              }
          }
      ]


Example 25: OR Predicate With Query Parameters

SELECT meta().id FROM `travel-sample` WHERE id = $1 OR id < $2;


In this example, the predicate id = $1 OR id < $2 pushed to index scan.

Span for

Low

High

Inclusion

id = $1

$1

$1

3 (BOTH)

Id < $2

null

$2

0 (NEITHER)


EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id = $1 OR id < $2;


      "spans": [
          {
              "Range": {
                  "High": [
                       "$1"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "$1"
                   ]
              },
              "Range": {
                  "High": [
                       "$2"
                   ],
                   "Inclusion": 0,
                   "Low": [
                       "null"
                   ]
              }
          }
      ]


Example 26: IN Predicate With Query Parameters

SELECT meta().id FROM `travel-sample` WHERE id IN [ $1, 10, $2] ;


In this example, the predicate id IN [ $1, 10, $2] pushed to index scan. This is improved plan for Couchbase Server 4.5.1

Span for

Low

High

Inclusion

id IN [$1, 10, $2]

$1

10

$2

$1

10

$2

3 (BOTH)

3 (BOTH)

3 (BOTH)


EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id IN [$1, 10, $2];


      "spans": [
          {
              "Range": {
                  "High": [
                       "$1"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "$1"
                   ]
              },
              "Range": {
                  "High": [
                       "10"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "10"
                   ]
              },
              "Range": {
                  "High": [
                       "$2"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "$2"
                   ]
              }
          }
      ]


Example 27: ANY Predicate

SELECT meta().id FROM `travel-sample` WHERE type = "route" AND ANY v IN schedule SATISFIES v.day = 0 END ;


In this example, the predicate v.day = 0 pushed to ARRAY index scan.

Span for

Low

High

Inclusion

v.day = 0

0

0

3 (BOTH)


EXPLAIN SELECT meta().id FROM `travel-sample` WHERE type = "route" AND ANY v IN schedule SATISFIES v.day = 0 END ;


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


Example 28: ANY Predicate

SELECT meta().id FROM `travel-sample` WHERE type = "route" AND ANY v IN schedule SATISFIES v.day IN [1,2,3] END ;


In this example, the predicate v.day IN [1,2,3] pushed to ARRAY index scan.

Span for

Low

High

Inclusion

v.day IN [1,2,3]

1

2

3

1

2

3

3 (BOTH)

3 (BOTH)

3 (BOTH)


EXPLAIN SELECT meta().id FROM `travel-sample` WHERE type = "route" AND ANY v IN schedule SATISFIES v.day IN [1,2,3] END ;


      "spans": [
          {
              "Range": {
                  "High": [
                       "1"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "1"
                   ]
              },
              "Range": {
                  "High": [
                       "2"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "2"
                   ]
              },
              "Range": {
                  "High": [
                       "3"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "3"
                   ]
              }
          }
      ]


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

Example 29: Equality Predicate on Expression

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


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

Span for

Low

High

Inclusion

id

null

unbounded

0 (NEITHER)


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


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


The span indicates we are doing a complete index scan, if it is not covered index we fetch the document from Data node and apply predicate. For better performance create new index as follows:

CREATE INDEX `idx_absid` ON `travel-sample`(abs(`id`));


When idx_absid is used  predicate abs(id) = 10 pushed to index scan.

Span for

Low

High

Inclusion

abs(id) = 10

10

10

3 (BOTH)


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


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


Example 30: Overlapping Predicates

SELECT meta().id FROM `travel-sample` WHERE id <= 100 OR (id BETWEEN 50 AND 150);


In this example, id <= 100 OR (id between 50 and 150) predicates are pushed to index scan as two ranges.

Span for

Low

High

Inclusion

id <= 100

null

100

2 (HIGH)

Id between 50 and 150

50

150

3 (BOTH)


EXPLAIN SELECT meta().id FROM `travel-sample` WHERE id <= 100 OR (id BETWEEN 50 AND 150);
     "spans": [
          {
              "Range": {
                  "High": [
                       "100"
                   ],
                   "Inclusion": 2,
                   "Low": [
                       "null"
                   ]
              }
          },
          {
              "Range": {
                  "High": [
                       "150"
                   ],
                   "Inclusion": 3,
                   "Low": [
                       "50"
                   ]
              }
          }
      ]


The spans indicate we are doing two range scans. When we observe closely, the ranges are overlapped. The values 50-100 are scanned twice (in future Couchbase Server releases, this might be improved). To eliminate duplicates N1QL does distinct operations on meta().id. If possible, rewriting query will eliminate duplicates.

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


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


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.

Learn how the world’s first NoSQL Engagement Database delivers unparalleled performance at any scale for customer experience innovation that never ends.

Topics:
nosql ,couchbase ,n1ql ,query ,index ,json ,peperformance ,explain ,optimization ,sql

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}