FTS and N1QL: Better MongoDB in Operator Performance Querying Multiple Arrays

Introduction

Couchbase Full Text Search (FTS) is a great fit for indexing multiple arrays and executing queries with multiple filter predicates in arrays.  In this article, I’ll demonstrate the advantages of using FTS over GSI (Global Secondary Index) for array indexing while working through an example use case that requires querying multiple arrays.  We’ll be creating an FTS multi-array index and querying the index with N1QL using the new SEARCH() function introduced in Couchbase Server 6.5.  

Travel Sample Bucket

In this article, we’ll be referencing the Travel Sample dataset available to install in any Couchbase Server instance. The travel-sample bucket has several distinct document types: airline, route, airport, landmark, and hotel.  The document model for each kind of document contains:

• A key that acts as a primary key
• An id field that identifies the document
• A type field that identifies the kind of document

The examples in this article will be using the hotel documents. The sample document below gives you an idea of the structure of a hotel document: 

Figure 1 – Sample Hotel Document

The Problem

Our example is a use case where a user can search for hotels which have been reviewed or liked by a person with a particular name. This requires querying hotel documents on both public likes and reviews, which are arrays within the hotel document model: 

Figure 2 – The “public_likes” and “reviews” arrays in the sample hotel document

First let’s look at implementing this use case with N1QL and GSI (Global Secondary Index). To find hotels that anyone named Ozella has either liked or reviewed, the query could look like this: 

We need to create an appropriate index for this query.  Maybe something like this that indexes both arrays of interest for hotel documents: 

This doesn’t work, and we get the error shown in Figure 3:

Figure 3 – Error creating index with multiple arrays

As Keshav Murthy wrote in his blog post Search and Rescue: 7 Reasons for N1QL (SQL) developers to use Search (problem #6), with N1QL in Couchbase, “to get the best performance while searching inside arrays, you need to create indexes with array keys. The array index comes with a limitation: each array index can only have one array key per index. So, when you have a customer object with multiple array fields, you can’t search all of them using a single index…causing expensive queries.” As Keshav notes in that article, this is a limitation with b-tree indexes in databases generally. 

So now let’s try two separate array indexes.  The indexes to support this query could look like these, which were created using the Couchbase N1QL Index Advisor, a new (DP) feature in Couchbase 6.5:

With those 2 indexes in place, our query runs successfully with 5 results (hotel_26020, hotel_10025, hotel_5081, hotel_20425, hotel_25327) and the following execution plan: 

Figure 4 – Execution plan using multiple indexes (GSI)

Same plan in JSON:

In the single node cluster being used for these examples, the query elapsed time is around 190-200 milliseconds to return the 5 resulting documents.  As you can see in the plan, there are two IndexScan3 operators which use each of the two array indexes we created, followed by a DistinctScan for the results of each index scan, and then a UnionScan.  The UnionScan shows an #itemsIn value of 1646 documents and an #itemsOut value of 904 documents, the Fetch operator also gets 904 documents, and finally with the Filter operator we get an #ItemsOut value of 5.  The fetch of 904 documents is a waste considering that we ended up with 5 documents returned by the query, and in fact about 170 milliseconds of the overall elapsed time is spent fetching the 905 documents when only 5 are needed.  

The Solution

By contrast, an FTS inverted index can easily be created for multiple arrays and is well-suited for cases where you need to search for fields in multiple arrays. We’ll create a FTS index on hotel documents for both the public_likes array and the author field within the reviews array. 

Index creation steps: 

1. On the Full Text Search UI, click “Add Index”.
2. Specify an index name, e.g. “hotel_mult_arrays”, and select the travel-sample bucket. 
3. Since each document in the travel-sample bucket has a “type” field indicating the type of document, leave “JSON type field” set to “type”.
4. Under type mappings:  
   1. Click “+ Add Type Mapping”, and specify “hotel” as the type name, since the requirement is to search all hotel documents.  
   2. A list of available analyzers can be accessed by means of the pull-down menu to the right of the type name field.  For this use case, leave “inherit” selected so that the type mapping inherits the default analyzer from the index.
   3. Since the requirement is to search the hotel public likes and review author fields, check “only index specified fields”.  With this checked, only user-specified fields from the document are included in the index for the hotel type mapping (the mapping will not be dynamic, meaning that all fields are considered available for indexing). 
   4. Click OK.  
   5. Mouse over the row with the hotel type mapping, click the + button, and then click “insert child field”.  This will allow the public_likes array to be individually included in the index.  Specify the following: 
      1. field: Enter the name of the field to be indexed, “public_likes”.
      2. type: Leave this set to text for the public_likes array.
      3. searchable as: Leave this the same as the field name for the current use case.  It can be used to indicate an alternate field name. 
      4. analyzer: As was done for the type mapping, for this use case, leave “inherit” selected so that the type mapping inherits the default analyzer.
      5. index checkbox: Leave this checked, so that the field is included in the index.  Unchecking the box would explicitly remove the field from the index.
      6. store checkbox: Check this setting to include the field content in the search results which permits highlighting of matched expressions in the results.  This is useful for testing the index, but not recommended in Prod if highlighting isn’t required since it increases index size.
      7. “include in _all field” checkbox: Leave this checked since the use case requirement is to search multiple fields. 
      8. “include term vectors” checkbox: Leave this checked, too, during development and testing of our index to allow highlighting of results. 
      9. docvalues checkbox: Uncheck this setting.  This setting stores the field values in the index which provides support for Search Facets, and for the sorting of search results based on field values, neither of which we need in this use case. 
      10. Click OK.
   6. Mouse over the row with the hotel type mapping, click the + button, and then click “insert child mapping”.  This will allow the array of review sub-documents to be included in the index.  Enter the property name “reviews”, leave “inherit” selected in the analyzer drop-down, check “only index specified fields”, and click OK. 
   7. Mouse over the row with the reviews child mapping, click the + button, and then click “insert child field”.  This will allow the author field from the array of review sub-documents to be included in the index.  Specify the following: 
      1. field: Enter the name of the field to be indexed, “author”.
      2. type: Leave this set to text for the author field.
      3. searchable as: Leave this the same as the field name for the current use case.  It can be used to indicate an alternate field name. 
      4. analyzer: As was done for the type mapping, for this use case, leave “inherit” selected so that the type mapping inherits the default analyzer.
      5. index checkbox: Leave this checked, so that the field is included in the index.  Unchecking the box would explicitly remove the field from the index.
      6. store checkbox: Check this setting to include the field content in the search results which permits highlighting of matched expressions in the results.  This is useful for testing the index, but not recommended in Prod if highlighting isn’t required since it increases index size.
      7. “include in _all field” checkbox: Leave this checked since the use case requirement is to search multiple fields. 
      8. “include term vectors” checkbox: Leave this checked, too, during development and testing of our index to allow highlighting of results.  
      9. docvalues checkbox: Uncheck this setting.  This setting stores the field values in the index which provides support for Search Facets, and for the sorting of search results based on field values, neither of which we need in this use case. 
      10. Click OK.
   8. Finally, uncheck the checkbox next to the “default” type mapping.  If the default mapping is left enabled, all documents in the bucket are included in the index, regardless of whether the user actively specifies type mappings. Only the hotel documents are required, and they are included by the hotel type mapping added previously. 
5. The default values suffice for the remaining collapsed panels (Analyzers, Custom Filters, Date/Time Parsers, and Advanced). 
6. Index Replicas can be set to 1, 2 or 3, provided that the cluster is running the Search service on n+1 nodes. With a single node development environment, maintain the default value of 0. 
7. For Index Type, the default value of “Version 6.0 (Scorch)” is appropriate for any newly created indexes. Scorch reduces the size of the index on disk and provides enhanced MongoDB in operator performance for indexing and mutation-handling.
8. Index Partitions can be left to the default value of 6. 
9. At this point, the create index page should look like the last frame captured in Figure 5.  Click “Create Index” to complete the process. 

Figure 5 – Creating FTS index with multiple arrays

Note: See Appendix for the JSON payload used to create this index through the REST API.

Testing queries against the index: 

1. On the Full Text Search UI, wait for indexing progress to show 100%, then click on the index name “hotel_mult_arrays”. 
2. To search for any hotels with likes or reviews by someone named “Ozella”, in the “search this index…” text box, enter “Ozella” and click Search.  Field-scoping of the search is not required because both indexed fields are included in the default field “_all”.
3. The results are shown (similar to Figure 6) with the key of each matching document and highlighted matching fields.  The document IDs returned are the same as those from our earlier N1QL query.  

Figure 6 – Index “hotel_mult_arrays” search results for “Ozella”

This is a single index on 2 array keys, which, as mentioned earlier, is something you could never do in a b-tree based index.  So now let’s take advantage of this FTS index in a N1QL query by using the SEARCH() function. Our query could look like this: 

A few things to note about the query: 

• The USE INDEX…USING FTS clause specifies that the FTS index should be used rather than a GSI index, so this query doesn’t use the index service. (Documentation)
• Because our FTS index uses a custom type mapping, the query needs to have the matching type specified in the WHERE clause (t.type=”hotel”).  
• The FTS index name is specified in the “index” field in the SEARCH() function as a hint, but that is optional since the USE INDEX clause takes precedence over a hint provided in the “index” field.  (Documentation)  

Using the FTS index we created, our N1QL query runs successfully and returns 5 results (hotel_5081, hotel_26020, hotel_10025, hotel_20425, hotel_25327) and the following execution plan: 

Figure 7 – Execution plan using multiple indexes (FTS)

Same plan in JSON:

In the single node cluster being used for these examples, the query elapsed time is around 20 milliseconds to return the same 5 documents.  As you can see in the plan, there is an IndexFtsSearch operator but there are no IndexScan3, DistinctScan, UnionScan, or IntersectScan operators.  The overall query is much more efficient without these expensive GSI operators. The IndexFtsSearch operator sends the 5 matching documents from the FTS index to the Fetch operator which gets only those 5 documents.  The fetch is much more efficient here than in the previous query since it’s only fetching 5 vs 904 documents, and this can also be observed in the comparison of overall elapsed times (and the servTime for the fetch operators: 170ms in query 1 and 1.5ms in query 2) between the queries.  

Conclusion

With GSI you can mix and match multiple array indexes in a single query, but with FTS you can mix and match multiple arrays in a single FTS index (and with FTS there is no leading key problem as in GSI regarding the order of the fields in the index).  As we’ve shown in this simple example of querying 2 arrays in the hotel documents, utilizing the new SEARCH() function in N1QL can result in simpler and more performant array queries. The same concept could be applied to queries utilizing several arrays, which would have even more favorable results over N1QL queries utilizing multiple GSI array indexes.  This approach uses fewer system resources and provides higher throughput, which results in an increase in overall system efficiency.  

This is just one example of the benefits of the integration between N1QL and FTS, and other benefits are documented in the blog posts in the references section below. 

References

• Couchbase Search Resources: https://www.couchbase.com/products/full-text-search
• Couchbase FTS Documentation: https://docs.couchbase.com/server/current/fts/full-text-intro.html
• Couchbase N1QL Search Documentation: https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/searchfun.html
• Couchbase FTS Blog Posts: https://www.couchbase.com/blog/category/full-text-search/
• Couchbase FTS Online Training: https://learn.couchbase.com/store/509465-cb121-intro-to-couchbase-full-text-search-fts

Appendix

Index Definition JSON: hotel_mult_arrays