1

In PostgreSQL, is there any performance gain when using a single ALTER TABLE statement to drop multiple columns, instead of using a separate statement to drop each column?

ALTER TABLE table DROP COLUMN col1, DROP COLUMN col2, DROP COLUMN 3;

// vs

ALTER TABLE table DROP COLUMN col1;
ALTER TABLE table DROP COLUMN col2;
ALTER TABLE table DROP COLUMN col3;
  • I put this question here because it is about query-performance, a topic covered by this site. If it belongs to SO please let me know. – Tamer Shlash Aug 28 '18 at 7:26
3

Yes, but it's too small to be noticed unless you plan to drop hundreds or even thousands of columns ;)

Each drop column is executed separately regardless if it's a separate statement, or part of a statement. The performance gain would be in the work postgres do before and after each statement, for instance taking a lock on the table, creating implicit transactions, and parsing the statement which it would only have to do once if all columns are dropped in one statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.