2

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?).

2 Answers 2

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.

1
  • 1
    log_duration seems to be exactly what I'm looking for, thanks! Jun 17, 2020 at 6:45
1

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

Subtract the values for a good estimate.

3
  • Thanks! datapoint: with log_duration on, psql timing for select 1 is 0.368 ms, time in log is 0.157 ms Jun 17, 2020 at 6:44
  • So my method would be off by 0.157 ms. But admittedly the other answer is better. Jun 17, 2020 at 6:50
  • But your method is handy in environments where config can't be modified or log can't be accessed. I just realized—and I'd almost guess that this is what you had in mind—we can compare timing's value with explain analyze select 1. timing - execution time - planning time = client overhead. Jun 17, 2020 at 10:07

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.