View the Query Plan for a COPY Statement
Jim_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!
0