Skip to main content
Post Undeleted by Ergest Basha
deleted 942 characters in body
Source Link
Ergest Basha
  • 4.5k
  • 3
  • 7
  • 21

From the documentation: https://www.postgresql.org/docs/9.1/sql-explain.html, there is no EXPLAIN for ALTER command.

Important: Keep in mind that the statement is actually executed when the ANALYZE option is used. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual. If you wish to use EXPLAIN ANALYZE on an INSERT, UPDATE, DELETE, CREATE TABLE AS, or EXECUTE statement without letting the command affect your data, use this approach:

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;statement

Only the ANALYZE and VERBOSE options can be specifiedAny SELECT, and only in that orderINSERT, without surrounding the option list in parentheses. Prior to PostgreSQL 9.0UPDATE, the unparenthesized syntax was the only one supported. It is expected that all new options will be supported onlyDELETE, VALUES, EXECUTE, DECLARE, or in the parenthesized syntaxCREATE TABLE AS statement, whose execution plan you wish to see.

So in your case it should be:

BEGIN;
    EXPLAIN ANALYZE alter table Commands drop column test;statement;
ROLLBACK;

ANALYZE The ANALYZE option causes the sql_statement to be executed first and then actual run-time statistics in the returned information including total elapsed time expended within each plan node and the number of rows it actually returned.

The ANALYZE statement actually executes the SQL statement and discards the output information, therefore, if you want to analyze any statement such as INSERT, UPDATE, or DELETE without affecting the data, you should wrap the EXPLAIN ANALYZE in a transaction, as follows:

From the documentation: https://www.postgresql.org/docs/9.1/sql-explain.html

Important: Keep in mind that the statement is actually executed when the ANALYZE option is used. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual. If you wish to use EXPLAIN ANALYZE on an INSERT, UPDATE, DELETE, CREATE TABLE AS, or EXECUTE statement without letting the command affect your data, use this approach:

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

Only the ANALYZE and VERBOSE options can be specified, and only in that order, without surrounding the option list in parentheses. Prior to PostgreSQL 9.0, the unparenthesized syntax was the only one supported. It is expected that all new options will be supported only in the parenthesized syntax.

So in your case it should be:

BEGIN;
    EXPLAIN ANALYZE alter table Commands drop column test;
ROLLBACK;

ANALYZE The ANALYZE option causes the sql_statement to be executed first and then actual run-time statistics in the returned information including total elapsed time expended within each plan node and the number of rows it actually returned.

The ANALYZE statement actually executes the SQL statement and discards the output information, therefore, if you want to analyze any statement such as INSERT, UPDATE, or DELETE without affecting the data, you should wrap the EXPLAIN ANALYZE in a transaction, as follows:

From the documentation: https://www.postgresql.org/docs/9.1/sql-explain.html, there is no EXPLAIN for ALTER command.

Important: Keep in mind that the statement is actually executed when the ANALYZE option is used. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual. If you wish to use EXPLAIN ANALYZE on an INSERT, UPDATE, DELETE, CREATE TABLE AS, or EXECUTE statement without letting the command affect your data, use this approach:

statement

Any SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, or CREATE TABLE AS statement, whose execution plan you wish to see.

BEGIN;
EXPLAIN ANALYZE statement;
ROLLBACK;
Post Deleted by Ergest Basha
Source Link
Ergest Basha
  • 4.5k
  • 3
  • 7
  • 21

From the documentation: https://www.postgresql.org/docs/9.1/sql-explain.html

Important: Keep in mind that the statement is actually executed when the ANALYZE option is used. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual. If you wish to use EXPLAIN ANALYZE on an INSERT, UPDATE, DELETE, CREATE TABLE AS, or EXECUTE statement without letting the command affect your data, use this approach:

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

Only the ANALYZE and VERBOSE options can be specified, and only in that order, without surrounding the option list in parentheses. Prior to PostgreSQL 9.0, the unparenthesized syntax was the only one supported. It is expected that all new options will be supported only in the parenthesized syntax.

So in your case it should be:

BEGIN;
    EXPLAIN ANALYZE alter table Commands drop column test;
ROLLBACK;

ANALYZE The ANALYZE option causes the sql_statement to be executed first and then actual run-time statistics in the returned information including total elapsed time expended within each plan node and the number of rows it actually returned.

The ANALYZE statement actually executes the SQL statement and discards the output information, therefore, if you want to analyze any statement such as INSERT, UPDATE, or DELETE without affecting the data, you should wrap the EXPLAIN ANALYZE in a transaction, as follows: