Search code examples
sqlpostgresqlexplain

EXPLAIN ANALYZE not working with ALTER TABLE

I'm trying to alter the type of a column from a table but I also want to know how long it takes to alter the table. I therefore combined:

EXPLAIN ANALYSE

command with:

ALTER TABLE tbl_name ALTER COLUMN col_name TYPE new_type;

So I did:

EXPLAIN ANALYSE ALTER TABLE my_table ALTER COLUMN my_column TYPE text;

But I get the error:

ERROR:  syntax error at or near "ALTER"
LINE 1: EXPLAIN ANALYSE ALTER TABLE my_table ALTER COLUMN my_column...
                        ^

But when I EXPLAIN ANALYSE with a SELECT it has no problem. I.e.:

EXPLAIN ANALYSE SELECT * FROM my_table LIMIT 300;

I must be missing something really simple but I'm not seeing it?

Solution

  • The manual for EXPLAIN:

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

    ALTER TABLE is not covered.

    There are other ways to get the execution time: