индекс & автовакуум? & как понять проблему? - Mailing list pgsql-ru-general

имеется большая таблица

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