1.1

QUERY-SCATTER execute_time 16.163 ms member_node peridot(38733):2790
QUERY-SEND execute_time 0.222 ms member_node peridot(38733):2790
RESULT-RECEIVE execute_time 0.083 ms member_node peridot(38733):2790
SEQUENTIAL-ITERATION (3.83%) execute_time 0.662 ms returned_rows
14 no_opens 1
RESULT-HOLDER execute_time 0.588 ms returned_rows 14 no_opens
1 member_node peridot(38733):2790
DISTRIBUTION-END (96.16%) execute_time 16.609 ms returned_rows
14
member peridot(38733):2790 begin_execution 2013-01-30 15:33:46.857
end_execution 2013-01-30 15:33:46.872
QUERY-RECEIVE execute_time 14.8 ms member_node peridot(38741)<v1>:27275
RESULT-SEND execute_time 0.031 ms member_node peridot(38741)<v1>:27275
RESULT-HOLDER execute_time 1.309 ms returned_rows 14 no_opens 1
NLJOIN (16.30%) execute_time 0.265 ms returned_rows 14 no_opens 1
ROWIDSCAN (2.46%) execute_time 0.04 ms returned_rows 1 no_opens
1 node_details FLIGHTS :
INDEXSCAN (35.63%) execute_time 0.579 ms returned_rows 1 no_opens
1 scan_qualifiers None scanned_object FA_INDEX scan_type node_details
WHERE : ((F.FLIGHT_ID = CONSTANT:AA1116) and true)
ROWIDSCAN (5.78%) execute_time 0.094 ms returned_rows 14 no_opens
1 node_details FLIGHTAVAILABILITY :
CONSTRAINTSCAN (39.81%) execute_time 0.647 ms returned_rows 14
no_opens 1 scan_qualifiers None scanned_object
APP.6__FLIGHTAVAILABILITY__FLIGHT_ID__SEGMENT_NUMBER:base-table:APP.FLIGHTAVAILABILITY
scan_type node_details WHERE : ((F.FLIGHT_ID = FA.FLIGHT_ID) and true)
You can see that the table scan is now replaced by a more efficient INDEXSCAN task in the join. Although
the query on the original, unindexed table completed very quickly, note that the index scan took less than a
millisecond to complete, whereas the original table scan required over 7 milliseconds. This performance
improvement is magnified as the number of rows increases, so always ensure that indexes are used when
possible for long-running queries.
Query Plan Codes
SQLFire 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 SQLFire member that distributes a query to other data
store members and processes the results).
DescriptionQuery Plan Code
The total time spent processing the query on the data store members.DISTRIBUTION-END
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.
GROUPED-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.
ORDERED-ITERATION
The time spent evaluating outer joins after receiving rows from other
members.
OUTER-JOIN-ITERATION
QUERY-SCATTER is the top-level code that organizes all tasks
associated with sending queries from the query coordinator and
QUERY-SCATTER
receiving and processing results on the coordinator. The execute time
271
Evaluating Query Plans and Query Statistics