Re: Question about behavior of conditional indexes

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: Question about behavior of conditional indexes
Дата
Msg-id CAHOFxGo_H2Vv7EF3ZCBLMk5QCANGJOtiW4JBOP2Vi9kJ9S6MDg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Question about behavior of conditional indexes  (Ninad Shah <nshah.postgres@gmail.com>)
Ответы Re: Question about behavior of conditional indexes  (Koen De Groote <kdg.dev@gmail.com>)
Список pgsql-general
You likely need to tune your autovacuum settings such that the index bloat reaches a steady-state and does not continue to increase. When vacuum runs, it will remove dead (and no longer visible to any running transactions) tuples aka row versions from each page (8KB block by default) in the file for that table's data. It will also update the index, except in newer versions of Postgres where that behavior becomes optional (see manual for which version and whether it still runs by default). If you are I/O bound and cannot tune the system defaults to autovacuum more proactively (when a smaller percent of rows are dead), then perhaps just change the settings for that table as it seems to be functioning a bit like a queue. Or you might consider setting up a daily job to vacuum analyze on all tables, if there is a period of low system activity. If you didn't have the index on the columns you are updating, then reducing fillfactor would be an option to increase HOT updates and thereby prevent the index bloat. Alas, that's not an option with the index needing to reference those values that are being changed.

"index002" btree (action_performed, should_still_perform_action, action_performed_at DESC) WHERE should_still_perform_action = false AND action_performed = true
That index seems odd to me. Why index a single value for the first two columns? I would recreate it with those skipped. Also, based on the names, I'd expect the opposite for true and false. That is, the "interesting" rows are where the action has NOT yet been performed yet and it is needed. I'd expect the index as defined to cover most of the table rather than a small fraction. Perhaps just a typo from manual obfuscation.
For what it's worth, you can create new concurrently, drop old concurrently, then rename new to old. That would be the same result as a reindex concurrently.

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Timestamp with vs without time zone.
Следующее
От: "Peter J. Holzer"
Дата:
Сообщение: Re: Re: Timestamp with vs without time zone.