Suggest note in index documentation about long running transactions

Поиск

Список

Период

Сортировка

От	Chris Travers
Тема	Suggest note in index documentation about long running transactions
Дата	15 февраля 2016 г. 22:22:16
Msg-id	CAKt_ZfsvdE3WiHUjg81MT1KzOkebqz_bg=DBHf4L6rkfjtEJ_w@mail.gmail.com обсуждение исходный текст
Ответы	Re: Suggest note in index documentation about long running transactions (Chris Travers <chris.travers@gmail.com>)
Список	pgsql-general

Дерево обсуждения

Hi;

Today I ran into a question from a client as to why an index was not used. The index had been freshly created and was on a relatively small table (16k live rows, but 300k dead tuples). The resulting sequential scan was taking half a second.

I found that even when setting enable_seqscan to off it was still refusing to use the index. After reading carefully through the index documentation yet again, it was not clear why it was not used.

After much research I came across an email by Tom Lane about how the HOT enhancements in 8.3 meant that indexes might not be usable until after the longest running transaction committed. This turned out to be the culpret (we had a transaction that took about 15 hours to complete and when it committed the index was used).

It might help if there is a note that indexes in some cases cannot be used until the min xid advances to the point where the index was created.

Best Wishes,

Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.

http://www.efficito.com/learn_more

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