SQL VERBOSE

What is the VERBOSE keyword in PostgreSQL?

Optional flag in several PostgreSQL commands that produces extra descriptive output for debugging and performance analysis.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL VERBOSE: PostgreSQL: Supported MySQL/MariaDB: Not supported (use EXPLAIN EXTENDED instead) SQL Server: Not supported Oracle: Not supported SQLite: Not supported

SQL VERBOSE Full Explanation

VERBOSE is an optional keyword in PostgreSQL that increases the level of detail returned by certain utility commands. When appended to commands such as EXPLAIN, VACUUM, ANALYZE, REINDEX, and CLUSTER, PostgreSQL emits additional metadata that is normally hidden. Examples include internal expression trees in EXPLAIN, per-table vacuum statistics in VACUUM, and progress messages in ANALYZE.The keyword does not change how the command affects data; it strictly affects the amount of information reported back to the client or written to the server log. Because VERBOSE output can be lengthy, it is mainly used during development, query tuning, or database maintenance sessions when engineers need deeper insight into PostgreSQL’s processing steps. It is ignored by unsupported commands and raises a syntax error if placed where not allowed.

SQL VERBOSE Syntax

-- EXPLAIN
EXPLAIN VERBOSE SELECT * FROM table_name;

-- VACUUM
VACUUM (VERBOSE) table_name;

-- ANALYZE
ANALYZE VERBOSE table_name;

SQL VERBOSE Parameters

Example Queries Using SQL VERBOSE

-- Inspect the full plan tree, including target lists
EXPLAIN VERBOSE SELECT id, email FROM users WHERE active = true;

-- Vacuum a single table while printing progress details
VACUUM (VERBOSE, ANALYZE) orders;

Expected Output Using SQL VERBOSE

EXPLAIN VERBOSE returns a result set that includes the conventional execution plan plus internal nodes, expression trees, and full column lists
VACUUM VERBOSE streams progress messages such as number of pages scanned, tuples removed, and index cleanup information to the client

Use Cases with SQL VERBOSE

Debugging complex queries by viewing internal planner nodes
Validating that columns are being projected as expected
Monitoring long-running VACUUM or ANALYZE operations
Teaching or documenting PostgreSQL internals
Capturing detailed logs for post-mortem analysis

Common Mistakes with SQL VERBOSE

Assuming VERBOSE is available in all SQL dialects (it is PostgreSQL-specific)
Forgetting parentheses around VACUUM options: use VACUUM (VERBOSE) not VACUUM VERBOSE
Believing VERBOSE alters query performance; it only changes output volume
Using it in production automation without pagination, causing very large result sets

First Introduced In

PostgreSQL 7.4

Frequently Asked Questions

What commands support VERBOSE?

EXPLAIN, VACUUM, ANALYZE, CLUSTER, and REINDEX currently accept the VERBOSE option.

Do I need superuser rights to use VERBOSE?

No. Any role that can run the base command can add the VERBOSE modifier.

Where does the extra output go?

The output is sent to the client session by default and also recorded in the server log when log_statement or client_min_messages permit.

Can VERBOSE be combined with other options?

Yes. For example, VACUUM (VERBOSE, ANALYZE) or EXPLAIN (ANALYZE, VERBOSE) SELECT ... are both valid.