Suggest note in index documentation about long running transactions

Поиск
Список
Период
Сортировка
От Chris Travers
Тема Suggest note in index documentation about long running transactions
Дата
Msg-id CAKt_ZfsvdE3WiHUjg81MT1KzOkebqz_bg=DBHf4L6rkfjtEJ_w@mail.gmail.com
обсуждение исходный текст
Ответы Re: Suggest note in index documentation about long running transactions
Список 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.

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