Netezza EXPLAIN command and its Usage

  • Post author:
  • Post last modified:August 23, 2019
  • Post category:Netezza
  • Reading time:3 mins read

Netezza uses a cost-based optimizer to determine the best method for scan and join operations, join order, and data movement between SPUs, that is, redistribute or broadcast operations. You can use the Netezza EXPLAIN command to display the actual execution plan that Netezza generates and uses while executing any query in the system.

Netezza EXPLAIN command

Netezza Optimizer

Netezza uses the cost based optimizer to determine the best methods for the query to be executed in the Netezza system.

Optimizer uses the statistics to determine the optimal and best execution plan for Netezza 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.
  • The number of NULL values in columns
  • The minimum and maximum of each column

Netezza EXPLAIN command

Netezza EXPLAIN command is very useful and comes handy when you are trying to optimize the query. You can use the EXPLAIN command to show a statement execution plan.

Netezza EXPLAIN command Syntax

Below is the syntax to use the EXPLAIN command:

EXPLAIN [ VERBOSE ] <query>
EXPLAIN DISTRIBUTION <query>
EXPLAIN [ PLANTEXT ] <query>
EXPLAIN [ PLANGRAPH ] <query>

Where, you can specify the <query> to generate execution plan. You can use any of the above syntax to generate the execution plan statement.

Netezza EXPLAIN command takes following inputs:

Input Description
VERBOSE Specifies verbose to show a detailed query plan.
DISTRIBUTION Requests explanation of the distribution plan
PLANTEXT Specifies the text plan
PLANGRAPH Specifies the HTML plan

Netezza EXPLAIN command Example

Below is example on how execution plan looks like:

TRAINING.ADMIN(ADMIN)=> EXPLAIN VERBOSE select * from TEST123; 
NOTICE: QUERY PLAN:

QUERY SQL: EXPLAIN VERBOSE select * from TEST123;

QUERY VERBOSE PLAN: 
Node 1. 
 [SPU Sequential Scan table "TEST123" {(TEST123.COL1)}] 
 -- Estimated Rows = 1, Width = 4, Cost = 0.0 .. 0.0, Conf = 100.0 
 Projections: 
 1:TEST123.COL1 
 [SPU Return] 
 [Host Return] 
QUERY PLANTEXT: 
Sequential Scan table "TEST123" (cost=0.0..0.0 rows=1 width=4 conf=100) {(TEST123.COL1)} 
(xpath_none, locus=spu subject=self) 
(spu_send, locus=host subject=self) 
(host_return, locus=host subject=self)

EXPLAIN

TRAINING.ADMIN(ADMIN)=>

Read:

Tags: