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?

Erwin Brandstetter · Accepted Answer · 2025-01-11 05:34:05Z

6

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:

Get execution time of PostgreSQL query

edited Jan 11 at 5:34

answered Mar 23, 2022 at 18:35

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mark Over a year ago

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?

Jeremy Over a year ago

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

Mark Over a year ago

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

Erwin Brandstetter Over a year ago

@Mark: Ways to get the execution time: stackoverflow.com/a/9065976/939860

Collectives™ on Stack Overflow

EXPLAIN ANALYZE not working with ALTER TABLE

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related