Using Multiple Indexes to Optimize Query Conditions

Indexes benefit queries that have multiple filter conditions. The OJAI Distributed Query Service can optimize these queries by creating query plans that scan multiple indexes and take the intersection of the matching documents.

Scanning multiple indexes is an alternative to using Composite Indexes. The following example illustrates how the OJAI Distributed Query Service does this.

Suppose you have a JSON table with an index on the Address.State field, another index on the Address.City field, and the query has the condition:
{
  "$and":[
    {"$lt":{"Address.State":"D"}},
    {"$gt":{"Address.City":"Oak"}}
  ]
}
The OJAI Distributed Query Service creates a query plan that uses the indexes as follows:
  • Performs a scan on the first index using the Address.State < "D" condition.
  • Performs a scan on the second index using the Address.City > "Oak" condition.
  • Takes the intersection of the document IDs that match both conditions.

If you do not apply conditions on both Address.State and Address.City in most of your queries, defining separate indexes instead of a single composite field index may be more desirable. With a composite index on fields Address.State and Address.City, the query service does not choose the index unless there is a condition on field Address.State. If there is a condition on Address.State, the query service can choose the composite index. However, in order to restrict the search on both fields, there must be an equality condition on Address.State. See Using Indexes to Optimize Equality Conditions for further details.

You can define separate single key indexes as well as a composite index, but this requires more storage and impacts performance throughput. See the sections on storage and throughput considerations in Designing Secondary Indexes for further guidance.

The following table illustrates the differences between using a composite index versus multiple indexes. The second column shows the behavior when you have a composite index defined on (Address.State, Address.City). The third column shows the behavior when you have separate simple indexes defined on each field.
Filter Condition Composite Index Two Simple Indexes 
{
  "$and":[
    {"$eq":{"Address.State":"CA"}},
    {"$eq":{"Address.City":"Oakland"}}
  ]
}
 Index searched using both conditions Separate index searches using each filter condition. Results intersected. 
{
  "$and":[
    {"$eq":{"Address.State":"CA"}},
    {"$ge":{"Address.City":"Oak"}}
  ]
}
 Index searched using both conditions Separate index searches using each filter condition. Results intersected. 
{"$eq":{"Address.State":"CA"}}
 Index searched using single filter condition Same as composite index case. However, since the simple index has only a single indexed field, it is smaller and more efficient to read. 
{
  "$and":[
    {"$ge":{"Address.State":"CA"}},
    {"$eq":{"Address.City":"Oakland"}}
  ]
}
 Index search initiated using only the Address.State condition. Address.City filter applied while reading the index. Separate index searches using each filter condition. Results intersected. 
{"$eq":{"Address.City":"Oakland"}}
 Cannot use index Index searched using single filter condition