View the Query Plan for a COPY Statement

Posted May 16, 2019 by Jim Knicely, Vertica Principal Solution Architect

Hand writing the text: Helpful Tips
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/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/EXPLAIN.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/QueryManagement/QueryPlans/ExplainOutputOptions/EXPLAINOutputOptions.htm

Have fun!