From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Frost <sfrost@snowman.net>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        Thomas Kellerer <shammat@gmx.net>,
        "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: PostgreSQL does not choose my indexes well
In-reply-to: <20200423171858.GE13712@tamriel.snowman.net>
References: <35dc90d6-e0c2-13b8-3184-e3db5d854a78@gmail.com>
 <1bb37a19-77f6-8c49-073e-ed6251c98583@gmx.net>
 <CAKFQuwY9twbudMDvmuo+9Mjy0y-cJcvzPazD07-oB+WV1qOMnA@mail.gmail.com>
 <4315.1587655779@sss.pgh.pa.us> <20200423171858.GE13712@tamriel.snowman.net>
Comments: In-reply-to Stephen Frost <sfrost@snowman.net>
	message dated "Thu, 23 Apr 2020 13:18:58 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <18680.1587664601.1@sss.pgh.pa.us>
Date: Thu, 23 Apr 2020 13:56:41 -0400
Message-ID: <18681.1587664601@sss.pgh.pa.us>
Precedence: bulk

Stephen Frost <sfrost@snowman.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