Re: PostgreSQL does not choose my indexes well

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> I do wonder if we are maybe missing a bet at times though, considering
> that I'm pretty sure we'll always go through the index in order, and
> therefore randomly, even when we don't actually need the results in
> order..? Has there been much consideration for just opening an index
> and sequentially scanning it in cases like this where we have to go
> through all of the index anyway and don't need the results in order?

As I recall, it's unsafe to do so because of consistency considerations,
specifically there's a risk of missing or double-visiting some entries due
to concurrent index page splits. VACUUM has some way around that, but it
doesn't work for regular data-fetching cases. (nbtree/README has more
about this, but I don't feel like looking it up for you.)

My guess based on your results is that the OP's table *isn't* all-visible,
or at least the planner doesn't know it is.

regards, tom lane