Updated March 8, 2023
Introduction to Redshift EXPLAIN
Redshift EXPLAIN is used for checking and seeing the complete flow of execution steps that will be performed while executing the query that you have mentioned in the explain command itself. We can check the performance and try to make our queries and the clauses that we are using in it such as join clause a little more optimized by changing the columns on which the joins are carried out and more such stuff to make our query more optimized. The explain command shows the execution plan in summarized format and in case if the verbose parameter is specified then it shows the complete detailed plan of execution.
Syntax of Redshift EXPLAIN
The syntax of EXPLAIN command when used in Amazon Redshift is quite similar to what it is in SQL EXPLAIN command.
The below statement shows the syntax of EXPLAIN command in Redshift.
EXPLAIN [VERBOSE] statement to be analyzed/query
In the above syntax the terminologies that are used are given here:
- VERBOSE: Usually, for whichever query statement you specify in the EXPLAIN command the command only returns a summarized form of explanation that specifies all the steps of the execution flow in short. In case, if you will specify this optional parameter here in syntax named VERBOSE then the command will retrieve the detailed step-by-step information of all the steps that will be carried out by query statement execution instead of just short summary of the query plan.
- statement to be analyzed/query: This can be any of the SQL statements that need to be calculated, analyzed, and studied and will be considered by the explain command for retrieving the query plan of the statement query specified here. These statements can only include the Data manipulation commands such as UPDATE, INSERT, SELECT, DELETE or CREATE TABLE and cannot include any other query statements related to Data Definition Language.
Performance of EXPLAIN Command
- The performance of the EXPLAIN statement is mostly affected by the factor of the time taken for the creation of the temporary tables. While executing query statements that include the subexpression or nested queries, there might be a necessity of creation of the internal temporary tables by the query for referring to it and considering it for manipulation and retrieval of the final result set of the query statement.
- In case if EXPLAIN command is used with such queries then we will have to create those temporary tables for analysis purposes as well. Because even though we don’t have to retrieve the final result set of the query. The creation of the intermediatory temporary table and the time required to create them, other joins on them, and many other execution-related steps need to be considered and displayed in the output of EXPLAIN command.
- This is the reason why if the query statement used in EXPLAIN involves the creation of temporary tables, it may be a little time-consuming to get the output of execution steps of the query statement specified in EXPLAIN command in case of temporary table creation. The ultimate performance depends on the statistics and schema of the query plan.
Examples of Redshift EXPLAIN
Given below are the examples to see the implementation of the EXPLAIN command with SQL queries:
Example #1
We will firstly consider the query statement in SQL which will retrieve the values of the columns named Article_id and Article_name from Educba_articles table and the information of writer_id and writer_name from the table Educba_writers. For this, we can make the use of EXPLAIN command to understand the query plan and flow of execution of this select query that will be followed by the DBMS internally in Redshift.
Code:
EXPLAIN
select Article_id, Article_name, Educba_articles. writer_id, writer_name
from Educba_articles, Educba_writers
where Educba_articles. writer_id = Educba_writers. writer_id;
The output of the execution of the above command in Amazon Redshift gives out as shown in the following image.
Output:
Example #2
Now, if you want to get the execution plan of the query statement the same as specified above but in a more detailed way including step-by-step information then you can make use of just the parameter VERBOSE in the EXPLAIN command.
Code:
EXPLAIN VERBOSE
select Article_id, Article_name, Educba_articles. writer_id, writer_name
from Educba_articles, Educba_writers
where Educba_articles. writer_id = Educba_writers. writer_id;
The output of the execution of the above command in Amazon Redshift gives out as shown in the following image consisting of detailed steps of the execution plan.
Output:
Example #3
Now, we will try to get the execution flow information for a query statement that involves the creation of the table. Let us say that we have to create one table named Educba_topics by selecting the column values from Educba_subjects table by using CREATE TABLE AS statement. For this, we want to see the detailed execution plan by using EXPLAIN.
Code:
explain create table Educba_topics as
select * from Educba_subjects
where SubjectName is not null;
In order to retrieve a detailed plan, you can use the VERBOSE in the above command. The output of the execution of the above command in Amazon Redshift gives out as shown in the following image consisting of the summarized execution plan.
Output:
Conclusion
The EXPLAIN command can be used in Amazon Redshift to study the detailed flow of execution steps followed while executing any of the SQL query statements mentioned in the EXPLAIN command. While using this command, we have to be careful as it does not support the retrieval of the execution plan of the DDL commands other than the ones specified in the article. In case, if you want to retrieve the summarized information of the execution plan then you can skip the specification of the optional parameter VERBOSE while to get the detailed information of execution flow you can specify VERBOSE in EXPLAIN command.
Recommended Articles
This is a guide to Redshift EXPLAIN. Here we discuss the introduction, performance of EXPLAIN command and examples respectively. You may also have a look at the following articles to learn more –
