A query plan is a sequence of step-like statements containing nodes that the Greenplum cost-based query optimizer uses to execute queries. Based on the complexity of the supplied SQL query, Greenplum planner generates different plan. In this article, we will check Greenplum Explain Command and its usage with some examples.
Greenplum Explain Command
The Greenplum EXPLAIN displays the query plan that the Greenplum planner generates for the supplied SQL query statement. These query plans are usually a tree plan of nodes. Each node in the plan represents a single operation, such as table scan, join, aggregation or a sort.
How to Read Greenplum Query Plan?
As mentioned in previous section, query plans are tree plan of nodes. Plans should be read from the bottom up as each node feeds rows into the node directly above it.
- Bottom Nodes: Bottom nodes in a plan are usually a table scan operation. If there are any join, aggregation, or sort, then there will be an additions node above bottom nodes.
- Top Nodes: Top nodes are usually a Greenplum data moving operations such as redistribute, explicit redistribute, broadcast, or gather motions.
The output of Greenplum explain command consists of following measures:
- Cost: This is measured in units of disk page fetches; for example, 1.0 equals one sequential disk page read. The first estimate is cost of getting first row and second is total cost. Cost value will differ if you use LIMIT clause in your query statements.
- Rows: Total number of rows output by particular node.
- Width: Total bytes of all the rows output by this plan node.
Greenplum Explain Command Syntax
Below is the Greenplum Explain command syntax:
EXPLAIN [ANALYZE] [VERBOSE] query_statement;Note that, EXAPLIN ANALYZE will execute the query on Greenplum database to get additional information’s such as, total elapsed time to run query, number of nodes involved in the plan node operation, etc.
Use EXPLAIN ANALYZE only when necessary as it will take time to execute query and return plan.
Also Read:
Greenplum Explain Command Example
Below is the example on how to use EXPLAIN command in Greenplum:
template1=# explain select * from store.store_dimension where store_name = 'abc';
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..431.00 rows=1 width=530)
-> Table Scan on store_dimension (cost=0.00..431.00 rows=1 width=530)
Filter: store_name::text = 'abc'::text
Optimizer status: PQO version 3.10.0
(4 rows)Hope this helps 🙂