Query 6: Using a secondary index with an index predicate
Fetch the name, ticket number, and arrival date of passengers whose arrival date is
greater than a given
value.
SELECT fullName, bag.ticketNo, bag.bagInfo[].bagArrivalDate
FROM BaggageInfo bag WHERE EXISTS
bag.bagInfo[$element.bagArrivalDate >="2019-01-01T00:00:00"]
Plan:
{
"iterator kind" : "RECEIVE",
"distribution kind" : "ALL_SHARDS",
"distinct by fields at positions" : [ 1 ],
"input iterator" :
{
"iterator kind" : "SELECT",
"FROM" :
{
"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "simpleindex_arrival",
"covering index" : false,
"index row variable" : "$$bag_idx",
"index scans" : [
{
"equality conditions" : {},
"range conditions" : {}
}
],
"index filtering predicate" :
{
"iterator kind" : "GREATER_OR_EQUAL",
"left operand" :
{
"iterator kind" : "FIELD_STEP",
"field name" : "bagInfo[].bagArrivalDate",
"input iterator" :
{
"iterator kind" : "VAR_REF",
"variable" : "$$bag_idx"
}
},
"right operand" :
{
"iterator kind" : "CONST",
"value" : "2019-01-01T00:00:00"
}
}
},
"FROM variable" : "$$bag",
"SELECT expressions" : [
{
"field name" : "fullName",
"field expression" :
{
"iterator kind" : "FIELD_STEP",
"field name" : "fullName",
"input iterator" :
{
"iterator kind" : "VAR_REF",
"variable" : "$$bag"
}
}
},
{
"field name" : "ticketNo",
"field expression" :
{
"iterator kind" : "FIELD_STEP",
"field name" : "ticketNo",
"input iterator" :
{
"iterator kind" : "VAR_REF",
"variable" : "$$bag"
}
}
},
{
"field name" : "bagArrivalDate",
"field expression" :
{
"iterator kind" : "ARRAY_CONSTRUCTOR",
"conditional" : true,
"input iterators" : [
{
"iterator kind" : "FIELD_STEP",
"field name" : "bagArrivalDate",
"input iterator" :
{
"iterator kind" : "ARRAY_FILTER",
"input iterator" :
{
"iterator kind" : "FIELD_STEP",
"field name" : "bagInfo",
"input iterator" :
{
"iterator kind" : "VAR_REF",
"variable" : "$$bag"
}
}
}
}
]
}
}
]
}
}
Explanation:
- The root iterator of this query plan is a RECEIVE iterator with a single child (input iterator) that is a SELECT iterator.
- The value of the
FROM
field is an iterator; in this case, it is a TABLE iterator. - The EXISTS condition is actually converted to a filtering
predicate. There is one filtering predicate which is the
whole WHERE expression. The index
simpleindex_arrival
is the only one applicable here and is used. - The
index filtering predicate
evaluates the filter criteria on thebagArrivalDate
field. Using the greater than and less than operators the filter condition is evaluated. - The FROM variable is the name of a variable ranging
over the records produced by the FROM iterator. Here the
FROM iterator is a TABLE iterator, and the FROM variable
(
$$bag
)is the same as the row variable of the TABLE iterator, as the index used is not covering. - In the SELECT expression three fields
(
fullname, ticketNo,bag.bagInfo[].bagArrivalDat
) are fetched. These correspond to three field names and field expressions in the SELECT expression clause. For the first two fields, the field expression is computed usingFIELD_STEP
iterator. For the last field, anARRAY_CONSTRUCTOR
iterator is used which iterates over the corresponding arrays to fetch the field value.