Why is DEFAULT much faster than UPDATE?

Поиск
Список
Период
Сортировка
От André Hänsel
Тема Why is DEFAULT much faster than UPDATE?
Дата
Msg-id 0d4101d8ad0d$3af0cf70$b0d26e50$@webkr.de
обсуждение исходный текст
Ответы Re: Why is DEFAULT much faster than UPDATE?  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general
This question is out of curiosity, just to learn more about the internals of
PostgreSQL.

The goal was to add a not null bool column filled with "false", but with
"true" as the default for new rows.

The naïve approach would be:
ALTER TABLE foo ADD COLUMN slow bool NOT NULL DEFAULT true;
UPDATE foo SET slow = false;

This takes a certain, non-negligible amount of time.

This on the other hand achieves the same result and is almost instant:
ALTER TABLE foo ADD COLUMN fast bool NOT NULL DEFAULT false;
ALTER TABLE foo ALTER COLUMN fast SET DEFAULT true;

Where does the difference come from, how are those handled internally?

Fiddle:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=56595e8ee397a5bc48b84277da3133
a9




В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Surprisingly forgiving behavior when a case expression is terminated with "end case"
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: Why is DEFAULT much faster than UPDATE?