Example 3: Using a non-covering index in a query plan with index scans

An index becomes non-covering when query cannot be fully evaluated using only the entries of an index.

Example 10-3 Fetch id, age, and income of users residing in CA whose income is greater than 5000

SELECT id, age, income FROM Users u WHERE u.address.state = "CA" 
AND income >5000

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" : false,
      "index row variable" : "$$u_idx",
      "index scans" : [
        {
          "equality conditions" : {"address.state":"CA"},
          "range conditions" : {}
        }
      ],
      "index filtering predicate" :
      {
        "iterator kind" : "GREATER_THAN",
        "left operand" :
        {
          "iterator kind" : "FIELD_STEP",
          "field name" : "income",
          "input iterator" :
          {
            "iterator kind" : "VAR_REF",
            "variable" : "$$u_idx"
          }
        },
        "right operand" :
        {
          "iterator kind" : "CONST",
          "value" : 5000
        }
      }
    },
    "FROM variable" : "$$u",
    "SELECT expressions" : [
      {
        "field name" : "id",
        "field expression" :
        {
          "iterator kind" : "FIELD_STEP",
          "field name" : "id",
          "input iterator" :
          {
            "iterator kind" : "VAR_REF",
            "variable" : "$$u"
          }
        }
      },
      {
        "field name" : "age",
        "field expression" :
        {
          "iterator kind" : "FIELD_STEP",
          "field name" : "age",
          "input iterator" :
          {
            "iterator kind" : "VAR_REF",
            "variable" : "$$u"
          }
        }
      },
      {
        "field name" : "income",
        "field expression" :
        {
          "iterator kind" : "FIELD_STEP",
          "field name" : "income",
          "input iterator" :
          {
            "iterator kind" : "VAR_REF",
            "variable" : "$$u"
          }
        }
      }
    ]
  }
}

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 in this example, it is a not a covering index as the age field to be fetched is not part of the index entry.
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"},
      "range conditions" : {}
   }
]
```
In this example, only one index scan will be performed. The conditions correspond to the predicates u.address.state = "CA" from the query. Specifically, the starting index entry must have the value CA on the address.state field. All subsequent entries must have CA as the value of their address.state field, and the scan will stop as soon as an entry with a different state value is encountered. Although the query contains a range predicate on income, this predicate does not appear as a range condition of the index scan. This is because there is no equality condition on the address.city field that appears before the income field in the index definition, and as a result, the income predicate cannot be used to determine the boundaries of the scan. Instead, the income predicate can be used as an index filtering predicate that is applied on every index entry produced by the index scan.

The index filtering predicate evaluates the filter criteria on the income field. Using the greater than operator the filter condition is evaluated.

"index filtering predicate" :
{
   "iterator kind" : "GREATER_THAN",
   "left operand" :
   {
      "iterator kind" : "FIELD_STEP",
      "field name" : "income",
      "input iterator" :
      {
         "iterator kind" : "VAR_REF",
         "variable" : "$$u_idx"
      }
   },
   "right operand" :
   {
      "iterator kind" : "CONST",
      "value" : 5000
   }
}

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 the index 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 row variable as the index is not covering.
This row variable ($$u) will be referenced by iterators implementing the other clauses of the SELECT expression.
In the SELECT expression, three fields (id, income and age) are fetched. These correspond to three 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"
      }
   }
}
```
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.