SQL query is written against tables.In order to execute a query, the Vertica database generates a query plan. Query plan is the sequence of steps used to determine the execution path & resource cost for each step. Cost calculated at each step in a query plan is the estimation of resources used like:
- Data distribution statistics
- Disk space
- Network bandwidth
- CPU speed
- Data segmentation across cluster
When you submit a query, the initiator chooses the projections to use, optimizes and plans the query execution. Planning and optimization are quick, requiring at most a few milliseconds.
Based on the projections chosen, the query plan that the optimizer produces is decomposed into “mini-plans.” These mini-plans are distributed to the other nodes, known as executors.The nodes process the mini-plans in parallel, interspersed with data movement operations.
The query execution proceeds with intermediate result sets (rows) flowing through network connections between the nodes as needed. In the final stages of executing a query plan, some wrap up work is done at the initiator, such as:
- Combining results in a grouping operation
- Merging multiple sorted partial result sets from all the executors
- Formatting the results to return to the client
Some small queries, for example, queries on replicated dimension tables, can be executed locally. In these types of queries, the query planning avoids unnecessary network communication.