A query plan is a sequence of step-like statements that the Vertica cost-based query optimizer uses to execute queries. Vertica can produce different query plans for a given query. The Vertica cost-based optimizer uses plan to identify best methods for joining tables, identifying right segment to get required data, etc. You can use Vertica EXPLAIN command to display actual execution plan that Vertica generates and uses while executing any query on the analytical system.
In this article, we will check Vertica query plan and how to generate it using Vertica EXPLAIN plan command.
Vertica Cost-Based Optimizer
Just like any other modern analytical systems such as Netezza, Greenplum, Vertica uses cost-based optimizer to optimize your actual PostgreSQL queries.
Vertica cost-based optimizer uses the basic statistics to determine the optimal and best query execution plan for any queries that involves complex logic and multiple table joins.
The statistics include the following:
- The number of rows in the table.
- It also includes the number of unique or distinct values of each column.
- Distribution of data across different nodes.
- CPU and Network topology
The optimizer uses above information to generate several query plans, it compares those plans, and choses one with lowest cost.
Also Read:
- Connecting Vertica using Python JDBC- Working Example
- Run SQL Script File using Vertica vsql Variable Substitution
Vertica EXPLAIN command
The EXPLAIN command plan comes handy when you are trying to optimize the complex queries on Vertica. You use EXPLAIN command to display actual query execution steps.
Vertica EXPLAIN command Syntax
Below is the syntax to use EXPLAIN on Vertica to display query plan:
EXPLAIN Query_Statement;
EXPLAIN JSON Query_Statement;
EXPLAIN VERBOSE Query_Statement;
EXPLAIN LOCAL Query_Statement;Where, you can specify the < Query_Statement > to generate execution plan. You can use any of the above syntax to generate the execution plan statement.
Vertica EXPLAIN command takes following inputs:
| Input | Description |
| JSON | Returns query plan in JSON format. |
| VERBOSE | This option increases the amount of detail in the rendered query plan. |
| LOCAL | Shows local query plan assigned to each node in case of multi-node database. |
Vertica EXPLAIN command Examples
Below is example on how Vertica query execution plan looks like:
------------------------------
QUERY PLAN DESCRIPTION:
------------------------------
explain select 1;
Access Path:
+-STORAGE ACCESS for dual [Cost: 10, Rows: 1] (PATH ID: 1)
| Projection: v_catalog.dual_p
------------------------------
...
...Hope this helps 🙂