We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


View the Query Plan for a COPY Statement — Vertica Forum

View the Query Plan for a COPY Statement

Jim_KnicelyJim_Knicely - Select Field - Administrator

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!

Sign In or Register to comment.