2

I'm using Postgresql 12 (AWS RDS) and pgAdmin4.

While the following statement works

explain select * from table "Commands";

The next one

explain alter table "Commands" drop column "test";

gives this error

LINE 1: explain alter table "Commands" drop column "test";
                ^
SQL state: 42601
Character: 9

I made sure the statement alter table "Commands" drop column "test" itself can be run successfully so this cannot be an issue with that statement.

Does explain not work with alter or am I missing anything?

4
  • Why do you think you need to see the execution plan of an alter statement? What is the underlying problem are you trying to solve by that?
    – user1822
    Sep 16, 2021 at 11:01
  • I'm noob to DB :(. I just learned about the explain statement so I wanted to try it with the alter. Why do you think you need to see the execution plan of an alter statement? - Guess I need to understand what "Execution plan" is. Thanks
    – Tran Triet
    Sep 16, 2021 at 11:54
  • @a_horse_with_no_name It would be nice if you could EXPLAIN an ALTER in order to see if it would rewrite the table, or if it would just update metadata. That doesn't work, but it is not non-sensical.
    – jjanes
    Sep 16, 2021 at 17:23
  • 1
    Now that I understand more about execution plan, I understand why a normal person wouldn't want to explain and alter. Execution plan is mostly about figuring out the best way to finding something (apparent with SELECT OR UPDATE on a column with/without INDEX). So it doesn't make sense to explain ALTER because ALTER is not concerned with finding something. However, @jjanes pointed out exactly what I was hoping EXPLAIN would be able to do - to explicitly say if the table will be rewritten.
    – Tran Triet
    Oct 1, 2021 at 5:36

2 Answers 2

2

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;
1
  • I read this part. Actually, I don't think it explicitly mentioned anywhere that explain does not support alter.
    – Tran Triet
    Sep 16, 2021 at 11:54
3

The documentation clearly stats:

statement

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

Other statements are not supported.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.