View the Query Plan for a COPY Statement

Jim_Knicely

Vertica 9.2.1 now supports the ability to see the query plan produced by the EXPLAIN command for a COPY command.

Example:

dbadmin=> CREATE TABLE test (c1 INT, c2 VARCHAR(30), c3 INT);
CREATE TABLE

dbadmin=> EXPLAIN VERBOSE COPY test FROM '/home/dbadmin/test1.txt';
                                                                                                                      QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 Opt Vertica Options
 --------------------
 PLAN_OUTPUT_SUPER_VERBOSE


 EXPLAIN VERBOSE COPY test FROM '/home/dbadmin/test1.txt';

 Access Path:
 LDISTRIB_UNSEGMENTED
 ------------------------------

Wait, where is the plan? Oh, I need at least one table projection to get a plan!

dbadmin=> CREATE PROJECTION test_pr AS SELECT * FROM test;
CREATE PROJECTION

dbadmin=> EXPLAIN VERBOSE COPY test FROM '/home/dbadmin/test1.txt';

                           QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 Opt Vertica Options
 --------------------
 PLAN_OUTPUT_SUPER_VERBOSE


 EXPLAIN VERBOSE COPY test FROM '/home/dbadmin/test1.txt';

 Access Path:
 +-DML INSERT [Cost: 0.000000, Rows: 0.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 1.000000] [OutRowSz (B): 0]
 |  Target Projection: public.test_pr_b1 (SORT BY PROJECTION SORT ORDER) (RESEGMENT)
 |  Target Projection: public.test_pr_b0 (SORT BY PROJECTION SORT ORDER) (RESEGMENT)
 |  Target Prep:
 |  Execute on: Query Initiator
 |  LDISTRIB_UNSEGMENTED
 |  Execute on: Query Initiator
 |  LDISTRIB_UNSEGMENTED
 +-LDISTRIB_UNSEGMENTED
 ------------------------------

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/EXPLAIN.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/QueryManagement/QueryPlans/ExplainOutputOptions/EXPLAINOutputOptions.htm

Have fun!