имеется большая таблица
id, status, ...
где status - поле enum
по некоторым причинам хотим преобразовать поле status в text
что делаем
1. добавили столбил status_text (NULL)
2. построили индекс
CREATE INDEX "upgrade_temp" ON "table" ("id") WHERE "status_text" IS NULL;
3. запустили скрипт который делает следующее
WITH "list" AS (
SELECT
"id"
FROM
"table"
WHERE
"status_text" IS NULL
LIMIT
500
)
UPDATE
"table"
SET
"status_text" = "status"::TEXT
FROM
"list"
WHERE
"list"."id" = "table"."id"
RETURNING
"list"."id"
и вот этот скрипт уже почти неделю работает вроде уже немного ему
осталось (где-то 7 млн из 40 млн переписать)
но вот такая фигня:
на реплике вижу что запрос
SELECT
min(id)
FROM
"table"
WHERE
"status_text" IS NULL
выполняется часами.
при этом EXPLAIN на этот запрос реплика показывает следующий
Result (cost=3.03..3.04 rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.56..3.03 rows=1 width=4)
-> Index Scan using upgrade_temp on table (cost=0.56..31983942.31 rows=12973820 width=4)
Index Cond: (id IS NOT NULL)
(5 строк)
то есть план запроса у него вроде простой - выбрать из индекса, но
запрос тупо вешается.
При этом тот же запрос на мастере (можно сделать EXPLAIN ANALYZE):
Result (cost=3.03..3.04 rows=1 width=0) (actual time=1504.294..1504.295 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.56..3.03 rows=1 width=4) (actual time=1504.283..1504.284 rows=1 loops=1)
-> Index Scan using upgrade_temp on table (cost=0.56..31985330.81 rows=12974386 width=4) (actual
time=1504.280..1504.280rows=1 loops=1)
Index Cond: (id IS NOT NULL)
Total runtime: 1504.382 ms
(6 строк)
то есть видим
- целых полторы секунды тупит на мастере
- дополнительная информация: по мере апгрейда размер индекса не
падает, а продолжает расти
Когда начали апгрейд - размер индекса был 800 Мб, сейчас уже 900Мб,
хотя записей в нем теперь в 4 раза меньше чем в начале.
из описанной информации я заключаю следующее
1. индекс на диске пришел в какое-то состояние что он слишком
неэффективен (фрагментация или что-то еще)
2. возможно я видимо сделал ошибку что записи апдейтятся пачками по
500 штук. лучше было по 1-10. Насколько я помню автовакуум у нас не
справлялся с работой когда мы пачками обновления делали
но останавливать скрипт я сейчас не хочу (рестартить скрипт апгрейда
не хочется)
соответственно вопросы:
1. правильно ли мое предположение что с индексом что-то не то в плане
хранения на диске? как это можно посмотреть/оценить?
2. есть связь с автовакуумом итп?
3. как устроен автовакуум на репликах отличается как-то? где можно
почитать про это?
--
. ''`. Dmitry E. Oboukhov
: :’ : email: unera@debian.org jabber://UNera@uvw.ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537