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.
