Re: Suggest note in index documentation about long running transactions

Unless there is a sense that this is a bad idea I will submit a doc patch.

On Mon, Feb 15, 2016 at 8:22 PM, Chris Travers <chris(dot)travers(at)gmail(dot)com>
wrote:

> 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
>

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more