EXPLAIN ANALYZE not working with ALTER TABLE

Question

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?

Lattice

Answer 1

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.

Maimaia

Thank you, should have checked the docs for EXPLAIN, is there a way for me to time how long it would take to alter a column then? – Lattice

Are you using psql? Just turn timing on: \timing on. – Weismann

Thanks, I just saw that from dba.stackexchange.com/questions/269251/… – Lattice

@Mark: Ways to get the execution time: https://mcmap.net/q/193290/-get-execution-time-of-postgresql-query – Maimaia