Covering Indexes

A covering index is an index that allows HPE Ezmeral Data Fabric Database to process a query using secondary indexes without reading the JSON table. Using a covering index makes a query more efficient by avoiding the I/O overhead of fetching data from the JSON table.

If all fields referenced in a query are either indexed or included fields in a secondary index, then the secondary index is a covering index for that query. HPE Ezmeral Data Fabric Database determines whether an index is covering for a query.

A query that uses a covering index can reference only indexed fields from the index or a combination of indexed and included fields. While adding included fields to an index enables it to become a covering index, note that each field you add to an index increases its storage requirement. As the storage size increases, the cost of reading the index also increases; likewise, for the cost of adding and updating documents. Consider the impact on storage and updates when adding included fields to an index.

In contrast to a covering index, a noncovering index is an index that does not store all fields referenced by a query. In this case, lookups occur on the JSON table to retrieve the referenced fields that are not available in the index itself.

Whether an index is covering or noncovering depends on the query that uses the index. In the example at Types of Secondary Indexes, zipCodeIdx is a covering index for the noted query. If the query also selects the Gender field, zipCodeIdx is no longer a covering index for the query, but it still optimizes the filter condition.

Covering Indexes and Container Field Paths

When a query uses an index in which the indexed fields are container field paths, HPE Ezmeral Data Fabric Database cannot rely on only the indexed fields to treat the index as covering. This is due to the way HPE Ezmeral Data Fabric Database stores data in an index for container field paths. As described in Using Container Field Paths as Indexed Fields, HPE Ezmeral Data Fabric Database stores one row in the index for each array element. Thus, reading only the rows corresponding to matching array elements might not retrieve the other elements of the array.

To allow an index to be covering in this scenario, the referenced field must be an included field in the index.

For example, using the example at Types of Secondary Indexes, suppose you want to run the following query:

  • Filter where Hobbies[] contains "Baseball"
  • Select the FullName and all Hobbies

For an index to be covering for this query, you must define the index with following fields:

  • Indexed Fields: Hobbies[]
  • Included Fields: Hobbies, FullName
NOTE
HPE Ezmeral Data Fabric Database does not permit you to specify the same field as both an indexed and included field, unless the indexed field is a container field path.