Date: Thu, 23 Apr 2020 16:01:43 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
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
Message-ID: <20200423200143.GK13712@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>
 <18681.1587664601@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="NSxzTiaShDidwUu+"
Content-Disposition: inline
In-Reply-To: <18681.1587664601@sss.pgh.pa.us>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--NSxzTiaShDidwUu+
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> 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?
>=20
> 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.)

That README isn't exactly small, but the mention of VACUUM having a
trick there helped me find this:

-------
The tricky part of this is to avoid missing any deletable tuples in the
presence of concurrent page splits: a page split could easily move some
tuples from a page not yet passed over by the sequential scan to a
lower-numbered page already passed over.  (This wasn't a concern for the
index-order scan, because splits always split right.)  To implement this,
we provide a "vacuum cycle ID" mechanism that makes it possible to
determine whether a page has been split since the current btbulkdelete
cycle started.  If btbulkdelete finds a page that has been split since
it started, and has a right-link pointing to a lower page number, then
it temporarily suspends its sequential scan and visits that page instead.
It must continue to follow right-links and vacuum dead tuples until
reaching a page that either hasn't been split since btbulkdelete started,
or is above the location of the outer sequential scan.  Then it can resume
the sequential scan.  This ensures that all tuples are visited.
-------

So the issue is with a page split happening and a tuple being moved to
an earlier leaf page, resulting in us potentially not seeing it even
though we should have during a sequential scan.  The trick that VACUUM
does seems pretty involved and would be more complicated for use for
this as it's not ok to return the same tuples multiple times (though
perhaps in a BitmapIndexScan we could handle that..).  Then again, maybe
the skipping scan mechanism that's been talked about recently would let
us avoid having to scan the entire index even in cases where the
conditional doesn't include the initial index columns, since it looks
like that might be what we're doing now.

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

Hrmpf, even then I seem to end up with an IndexOnlyScan-

=3D# select * from pg_visibility_map('entidad') where all_visible;
blkno | all_visible | all_frozen=20
-------+-------------+------------
(0 rows)

analyze entidad;

=3D# select relallvisible from pg_class where relname =3D 'entidad';
 relallvisible=20
---------------
             0
(1 row)

=3D# explain (analyze, buffers) select * from entidad where cod_tabla =3D 4;
                                                                      QUERY=
 PLAN                                                                      =
=20
---------------------------------------------------------------------------=
----------------------------------------------------------------------------
 Index Only Scan using idx_entidad_tabla_4 on entidad  (cost=3D0.43..170908=
=2E14 rows=3D1657114 width=3D24) (actual time=3D0.312..3511.629 rows=3D1720=
668 loops=3D1)
   Heap Fetches: 3441336
   Buffers: shared hit=3D6444271 read=3D469499
 Planning Time: 2.831 ms
 Execution Time: 3563.413 ms
(5 rows)

I'm pretty suspicious that they've made some odd planner configuration
changes or something along those lines to end up with the plan they got,
or there's some reason we don't think we can use the partial index.

Thanks,

Stephen

--NSxzTiaShDidwUu+
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJeofQnAAoJEO1sijiDR2RV0DcP/AtPcjm3kc521pL8uFGhiphi
8SFuqSjPs6///X6FzsbtsaiEXlughLuzWIxm8THlDJ/DX94WJph1EMnRnxjaNcpJ
wVDdlW0CEg2rT5037n6onFM8bX/83sY4GIvRgUAZAnnMQ81bF5LHzIxeM6OBjAWj
4P2N8336O0KegAgOZIxDpyFNJZCpmpeNcPv5cQ6FpthWN85os2zrVcdff663t3g6
YKIu7ibc/QS/gdt9Lpt/RgzlGSVbIa1Ck/gWrn6IcCiuGo8LfYY1buLX2R8hwWBG
NWGyz2Xq/Vfsvhsxijh63MX8I4AX5LvR4FqPaD/lZoLUgdiRMCD4ecHlsmUua2Sc
0bz+mno3nDMKFdwB6mZ+M3v+c0o5np3XhtBxsvlDCowrH0JdpsDa5dqec1srum2F
ueQmZ/tI3y3+QM4H8c+iVtyM6BvB/irYRnnrjywh2m6iLz5GrX0b5HK7hsQUfnAf
sGUYjxrxHAXmPltoha+X9x+Rpkzp9ghqCcneFtcfrlo5wUBseL6MbLBL2i2aRd+O
8VJhMiMhpNuV0B9yTJRv95YA9ZCW7KZin1TMlx+iUGM2GkPvz8n3cNqRceqhk4zT
fX8PLq0cCx0kmJhpK0Cdqe0cb1XeN8Q9+77MR+cjzQHNTNA/U3C29iUinNjFgDGz
s2z9Ym3LXBcKn1VWUyBY
=BR++
-----END PGP SIGNATURE-----

--NSxzTiaShDidwUu+--