Vertica Analytics Platform Version 9.2.x Documentation

EXPLAIN

Returns a formatted description of the Vertica optimizer's plan for executing the specified statement.

Syntax

EXPLAIN [/*+ ALLNODES */] [explain-options] sql-statement

Parameters

/*+ALLNODES*/ Specifies to create a query plan that assumes all nodes are active, not valid with LOCAL option.
explain‑options

One or more EXPLAIN options, specified in the order shown:

[ LOCAL ] [ VERBOSE ] [ JSON ] [ ANNOTATED ]
  • LOCAL: On a multi-node database, shows the local query plans assigned to each node, which together comprise the total (global) query plan. If you omit this option, Vertica shows only the global query plan. Local query plans are shown only in DOT language source, which can be rendered in Graphviz.

    This option is incompatible with the hint /*+ALL NODES*/. If you specify both, EXPLAIN returns with an error.

  • VERBOSE: Increases the level of detail in the rendered query plan.
  • JSON: Renders the query plan in JSON format. This option is compatible only with VERBOSE.
  • ANNOTATED: Embeds optimizer hints that encapsulate the query plan for this query. Vertica uses these hints to create directed queries. For more information, see Directed Queries in the Administrator's Guide. This option is compatible with LOCAL and VERBOSE.
sql‑statement A query or DML statement—for example, SELECT, INSERT, UPDATE, COPY, and MERGE.

Privileges

The same privileges required by the specified statement.

Requirements

The following requirements apply to EXPLAIN's ability to produce useful information:

  • Reasonably representative statistics of your data must be available. See Collecting Statistics in the Administrator's Guide for details.
  • EXPLAIN produces useful output only if projections are available for the queried tables.
  • Qualifier options must be specified in the order shown earlier, otherwise EXPLAIN returns with an error. If an option is incompatible with any preceding options, EXPLAIN ignores them.

See Also

Viewing Query Plans in the Administrator's Guide