How can I see execution time of an ALTER TABLE statement?

Question

Is it possible to see the execution time of an alter table command?

I think with \timing on in psql, it shows more than the execution time, but also the client round trip, and maybe other things as well. What I want is the "execution time" shown by explain analyze. But I can't run alter table through explain analyze (right?).

Answer 1

You can't run alter table commands through EXPLAIN ANALYZE.

You could use \timing and compare that with other queries and do some math to see the overhead, but I think I'd probably recommend wrapping the DDL statement between 2 select now(); commands (all in the same line, to eliminate round trips). Technically this will also have some additional overhead, but probably less than \timing.

You might also just try turning on log_statement and log_duration in the conf, and capturing the timing of the command there. Anything more specific though and you'd probably need something overly low level like strace or gdb.

Answer 2

Turn on \timing, run your ALTER TABLE and SELECT 1.

Subtract the values for a good estimate.