Re: BUG #16130: planner does not pick unique btree index and goes for seq scan but unsafe hash index works.

Thanks Tom,
Indeed old_snapshot_threshold is enabled. I don't know the reason behind it as it is pretty old database but new created development and test environments don't have it. I will remove this and test this again.

Best Regards

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Thursday, 21 November 2019 19:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form noreply(at)postgresql(dot)org writes:
>
> > Planner does not pick unique btree index and goes for seq scan but unsafe
> > hash index works.
>
> I couldn't reproduce this from the given instructions.
>
> Looking at the code, it looks like it might be possible to explain it
> with a combination of (1) old_snapshot_threshold being enabled (not -1),
> and (2) something holding back global xmin, such as a long-running
> transaction. (Maybe you have an uncommitted prepared transaction?)
> In that situation a newly-created index won't be used until all older
> transactions have gone away. But the existence of an unlogged index
> (hash index) disables the snapshot threshold feature for the associated
> table.
>
> There may be some additional condition needed to cause it, because
> I still couldn't reproduce the behavior with those two conditions
> set up.
>
> IMO old_snapshot_threshold is a complete kluge and you should not
> have it turned on unless you desperately need it. It has a lot of
> poorly-documented drawbacks, including this one.
>
> regards, tom lane