Example 1 : Using a covering index in a query plan with only index scans

An index is called a covering index with respect to a query if the query can be evaluated using only the entries of that index, that is, without the need to retrieve the associated rows.

Example 1 - Fetch the id and income of users whose state is CA and their city value must be greater or equal to “S” and whose income is between 1000 and 2000

SELECT id, income FROM Users u WHERE u.address.state = "CA" AND
u.address.city >= "S" AND 1000 < income and income < 2000

Query execution plan:

{
  "iterator kind" : "RECEIVE",
  "distribution kind" : "ALL_SHARDS",
  "input iterator" :
  {
    "iterator kind" : "SELECT",
    "FROM" :
    {
      "iterator kind" : "TABLE",
      "target table" : "users",
      "row variable" : "$$u",
      "index used" : "idx_state_city_income",
      "covering index" : true,
      "index row variable" : "$$u_idx",
      "index scans" : [
        {
          "equality conditions" : {"address.state":"CA","address.city":"Santaclara"},
          "range conditions" : { "income" : { "start value" : 1000, "start inclusive" : false, "end value" : 2000, "end inclusive" : false } }
        }
      ]
    },
    "FROM variable" : "$$u_idx",
    "SELECT expressions" : [
      {
        "field name" : "id",
        "field expression" :
        {
          "iterator kind" : "FIELD_STEP",
          "field name" : "#id",
          "input iterator" :
          {
            "iterator kind" : "VAR_REF",
            "variable" : "$$u_idx"
          }
        }
      },
      {
        "field name" : "income",
        "field expression" :
        {
          "iterator kind" : "FIELD_STEP",
          "field name" : "income",
          "input iterator" :
          {
            "iterator kind" : "VAR_REF",
            "variable" : "$$u_idx"
          }
        }
      }
    ]
  }
}

Explanation of the query execution plan :

The root iterator of this query plan is a RECEIVE iterator with a single child (input iterator) that is a SELECT iterator. The only property of the RECEIVE iterator in this example, is the distribution kind whose value is ALL_SHARDS.
The index idx_state_city_income is used here and this is a covering index as all the fields in the SELECT expression can be fetched only using the index entries. .
The index scan property contains the start and stop conditions that define the index scans to be performed.
```
"index scans" : [
   {
      "equality conditions" : {"address.state":"CA","address.city":"Santaclara"},
      "range conditions" : { "income" : { "start value" : 1000, "start inclusive" : false, "end value" : 2000, "end inclusive" : false } }
   }
]
```
In this query, only one index scan will be performed. The equality conditions correspond to the predicates u.address.state = “CA” and u.address.city = “Santaclara” from the query. The range conditions correspond to the predicates 1000 < income and income < 2000. The index scan will start at the first entry whose address.state field is equal to CA, its address.city field is equal to Santaclara, and its income field is greater than 1000. The index scan will return all subsequent entries until the first entry whose address.state field is not CA, or its address.city field is not Santaclara, or its income field is greater that or equal to 2000.
The index row variable is $$u_idx, which is the name of a variable ranging over the index entries produced by the TABLE iterator. Every time a new index entry is produced by the index scan, the $$u_idx variable will be bound to that entry.
When the FROM iterator is a TABLE iterator, the FROM variable is the same as either theindex row variable or the row variable of the TABLE iterator, depending on whether the used index is covering or not. In this example, the FROM variable is same as the index row variable ($$u_idx) as the index is covering because all the fields in the SELECT expression can be evaluated only using the index entries.
This index row variable ($$u_idx) will be referenced by iterators implementing the other clauses of the SELECT expression.
In the SELECT expression, two fields (id and income) are fetched. These correspond to two field names and field expressions in the SELECT expression clause.
```
{
   "field name" : "id",
   "field expression" :
   {
      "iterator kind" : "FIELD_STEP",
      "field name" : "#id",
      "input iterator" :
      {
         "iterator kind" : "VAR_REF",
         "variable" : "$$u_idx"
      }
   }
}
```
For every field to be fetched by the SELECT expression, the field expression is computed by a FIELD_STEP iterator. The FIELD_STEP iterator extracts and returns the value of a field (id as shown above) from the records produced by its input iterator. The same is done for every field to be fetched in the SELECT expression.