Query 2: Using primary key index with an index predicate

Fetch the bag details of passengers for ticket numbers satisfying one of the two ranges of values.

SELECT fullname, ticketNo, bag.bagInfo[].tagNum,bag.bagInfo[].routing
FROM BaggageInfo bag WHERE ticketNo > 1762340000000 OR ticketNo < 1762352000000;

Plan:

{
  "iterator kind" : "RECEIVE",
  "distribution kind" : "ALL_PARTITIONS",
  "input iterator" :
  {
    "iterator kind" : "SELECT",
    "FROM" :
    {
      "iterator kind" : "TABLE",
      "target table" : "BaggageInfo",
      "row variable" : "$$bag",
      "index used" : "primary index",
      "covering index" : false,
      "index scans" : [
        {
          "equality conditions" : {},
          "range conditions" : {}
        }
      ],
      "index filtering predicate" :
      {
        "iterator kind" : "OR",
        "input iterators" : [
          {
            "iterator kind" : "GREATER_THAN",
            "left operand" :
            {
              "iterator kind" : "FIELD_STEP",
              "field name" : "ticketNo",
              "input iterator" :
              {
                "iterator kind" : "VAR_REF",
                "variable" : "$$bag"
              }
            },
            "right operand" :
            {
              "iterator kind" : "CONST",
              "value" : 1762340000000
            }
          },
          {
            "iterator kind" : "LESS_THAN",
            "left operand" :
            {
              "iterator kind" : "FIELD_STEP",
              "field name" : "ticketNo",
              "input iterator" :
              {
                "iterator kind" : "VAR_REF",
                "variable" : "$$bag"
              }
            },
            "right operand" :
            {
              "iterator kind" : "CONST",
              "value" : 1762352000000
            }
          }
        ]
      }
    },
    "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" : "tagNum",
        "field expression" :
        {
          "iterator kind" : "ARRAY_CONSTRUCTOR",
          "conditional" : true,
          "input iterators" : [
            {
              "iterator kind" : "FIELD_STEP",
              "field name" : "tagNum",
              "input iterator" :
              {
                "iterator kind" : "ARRAY_FILTER",
                "input iterator" :
                {
                  "iterator kind" : "FIELD_STEP",
                  "field name" : "bagInfo",
                  "input iterator" :
                  {
                    "iterator kind" : "VAR_REF",
                    "variable" : "$$bag"
                  }
                }
              }
            }
          ]
        }
      },
      {
        "field name" : "routing",
        "field expression" :
        {
          "iterator kind" : "ARRAY_CONSTRUCTOR",
          "conditional" : true,
          "input iterators" : [
            {
              "iterator kind" : "FIELD_STEP",
              "field name" : "routing",
              "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 primary key index is used and the index is not covering (as you need to scan the table to fetch columns other than the index entries).

• The index filtering predicate evaluates the filter criteria on the ticketNo 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 four fields (fullname,ticketNo,bag.bagInfo[].tagNum, bag.bagInfo[].routing) are fetched. These correspond to four field names and field expressions in the SELECT expression clause. For the first two fields, the field expression is computed using FIELD_STEP iterator. For the last 2 fields, an ARRAY_CONSTRUCTOR iterator is used which iterates over the corresponding arrays to fetch the field value.