Query Plan Codes

GemFire XD may add the following codes to a generated query plan. All times reported in the plan are in milliseconds.

The following codes apply to the query coordinator (the GemFire XD member that distributes a query to other data store members and processes the results).

Query Plan Code Description
DISTRIBUTION-END The total time spent processing the query on the data store members.
GROUPED-ITERATION The time spent grouping rows on the query coordinator. This code is displayed only if the query contains an GROUP BY clause and the query was processed over multiple table partitions.
ORDERED-ITERATION The time spent resorting rows on the query coordinator. This code is displayed only if the query contains an ORDER BY clause and the query was processed over multiple table partitions.
OUTER-JOIN-ITERATION The time spent evaluating outer joins after receiving rows from other members.
QUERY-SCATTER QUERY-SCATTER is the top-level code that organizes all tasks associated with sending queries from the query coordinator and receiving and processing results on the coordinator. The execute time reported for QUERY-SCATTER is the total, aggregate time of all the subordinate tasks. QUERY-SCATTER lists the GemFire XD members to which the query was distributed; it does not list the query coordinator member.
QUERY-SEND The network time required to send the query, as well as the target member. This is an "OUT" operation from the perspective of the query coordinator. A separate QUERY-SEND code is displayed for each data store that receives the query.
REGION-GET An atomic "get" operation in the GemFire component.
REGION-GETALL An atomic "getall" operation in the GemFire component.
REGION-PUT An atomic "put" operation in the GemFire component.
REGION-PUTALL An atomic "putall" operation in the GemFire component.
RESULT-RECEIVE The network time required to receive the results from data stores back to the query coordinator. This is an "IN" operation from the perspective of the query coordinator. A separate RESULT-RECEIVE code is displayed for each data store that processes the query.
RESULT-HOLDER Shows the total time spent batching up results from the target member. This is an "IN" operation from the perspective of the query coordinator. RESULT-HOLDER displays the number of rows returned from the specified data store member. A separate RESULT-HOLDER code is displayed for each data store that processes the query.

RESULT-HOLDER is a blocking operation that is connected to SEQUENTIAL-ITERATION.

ROUNDROBIN-ITERATION The time required to merge results from an n-way merge.
ROW-COUNT-ITERATION The time spent returning rows specified in a FETCH NEXT clause. (See OFFSET and FETCH FIRST Clauses.)
SEQUENTIAL-ITERATION SEQUENTIAL-ITERATION is an operator that gathers results and sends them to the client as the client requests, one block at a time. This code shows the total aggregate row count of the query. If a FETCH FIRST clause was used, it is displayed in this operator.

The following codes are shown only for data store members in a query plan:

Query Plan Code Description
AGGREGATION An aggregate operation such as SUM, AVG, MIN, or MAX.
CONSTRAINTSCAN The time spent on an index scan created from a table constraint.
EXCEPT An EXCEPT, EXCEPT ALL, or EXCEPT DISTINCT operation. See SQL Language Limitations.
FILTER A predicate filtering operation.
GLOBALINDEXSCAN The time spent performing a global hash index scan (for example, for reference checks).
GROUPBY Time spent grouping columns.
HASHJOIN The time spent on a hash join operation.
HASHSCAN The time spent scanning a generated hash table.
HEAP Time spent scanning GemFire regions.
INDEXSCAN If the query required a scan on an index, then this code indicates the time required for the scan.
INTERSECT An INTERSECT, INTERSECT ALL, or INTERSECT DISTINCT operation. See SQL Language Limitations.
LOHASHJOIN The time spend for a left or right hash outer join operation.
LONLJOIN The time spend for a left or right nested loop outer join operation.
MERGEJOIN The time spent on a merge join operation.
NLJOIN The time taken to perform a nested loop join (matching all of the left table rows with the right table rows in a query).

NLJOIN always has two nested entries. The first entry corresponds to the outer table, which is read once and returns some number (n) of rows. The second entry corresponds to the inner table which is iterated over n times (once for each result in the outer table).

The individual seeks for the inner and outer tables are timed separately and are listed in each nested entry.

Example Query Plan Analysis describes an example query plan that performs an NLJOIN.

PROJECT-FILTER A predicate filtering operation that occurs while extracting columns (PROJECTION operation).
PROJECTION The time spent to extract the selected columns from the base row of the table. This code can appear multiple times for a query depending on how the engine processes the query.
QUERY-RECEIVE QUERY-RECEIVE is the top-level code that organizes all tasks associated with processing a query on a specific data store member. QUERY-RECEIVE displays the member ID and process ID of the data store, as well as the total aggregate time for all of the subordinate tasks. If the query was distributed to multiple data store members, then each member displays a separate QUERY-RECEIVE code. The contents of multiple QUERY-RECEIVE codes are similar unless tables are partitioned across different VMs or physical machines.
RESULT-HOLDER This value is linked to the RESULT-HOLDER code on the query coordinator. All of the tasks beneath RESULT-HOLDER represent query operators performed on the data store member.
RESULT-SEND The network time that was spent sending result rows back to the query coordinator. The target node is the query coordinator.
ROW-COUNT The time spent processing results for the FETCH clause.
ROWIDSCAN The total time spent for index entries to fetch the base row from the table. This occurs when the projection of the query is different from the index column.
SET Indicates a set operation (a UNION, UNION ALL, UNION DISTINCT, INTERSECT, INTERSECT ALL, INTERSECT DISTINCT, EXCEPT, EXCEPT ALL, or EXCEPT DISTINCT operation). See SQL Language Limitations.
SCROLL-INSENSITIVE A scroll insensitive cursor operation.
SORT The time spent sorting rows to execute an ORDER BY, DISTINCT, or GROUP BY clause.
TABLESCAN The time required to scan a table.
UNION The time spent performing a UNION, UNION ALL, or UNION DISTINCT operation of two queries. See SQL Language Limitations.