Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ow94E-0004iQ-PP for pgsql-general@arkaria.postgresql.org; Fri, 18 Nov 2022 21:38:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ow94D-0008OV-Im for pgsql-general@arkaria.postgresql.org; Fri, 18 Nov 2022 21:38:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ow94D-0008NT-8U for pgsql-general@lists.postgresql.org; Fri, 18 Nov 2022 21:38:33 +0000 Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ow94A-0006VZ-Pk for pgsql-general@lists.postgresql.org; Fri, 18 Nov 2022 21:38:32 +0000 Received: by rorschach.hjp.at (Postfix, from userid 1000) id 4E97E81AD; Fri, 18 Nov 2022 22:38:28 +0100 (CET) Date: Fri, 18 Nov 2022 22:38:28 +0100 From: "Peter J. Holzer" To: pgsql-general@lists.postgresql.org Subject: Re: Lots of read activity on index only scan Message-ID: <20221118213828.4gihtcely4pgbdh2@hjp.at> Mail-Followup-To: pgsql-general@lists.postgresql.org References: <20221118204617.z5pziyz4n6mllvhe@hjp.at> <2267925.1668805186@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="jqgabazeogduv66k" Content-Disposition: inline In-Reply-To: <2267925.1668805186@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --jqgabazeogduv66k Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On 2022-11-18 15:59:46 -0500, Tom Lane wrote: > "Peter J. Holzer" writes: > > Both do a parallel index only scan. Both perform 0 heap fetches. > > But one reads 27336 buffers (or about 22 bytes per index entry, which > > sounds reasonable) while the other reads 9995216 buffers (or almost one > > full buffer per row). Why? The entries should be dense in the index in > > both cases and since it's an index only scan (and explain says there > > were 0 heap fetches) I would not expect extra accesses. Where do these > > buffer reads come from? >=20 > An "index only" scan is only that if the table's all-visible map is > fully set. Yes, but shouldn't that be reflected in the heap fetches? Or am I misunderstanding what that means? Plus: > Did you vacuum this table after building it, or wait long enough for > autovacuum to do so? Both actually. I built the table this morning and did all the tests again just before posting. And I explicitely vacuumed it during this second set of tests. I should have mentioned that, sorry. Also possibly relevant: This is on Pg 15.1 (PGDG build for Ubuntu). hp --=20 _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" --jqgabazeogduv66k Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmN3+08ACgkQ8g5IURL+ KF1wtBAAlI8jihjSmfP6mWndyBk5jL4JCMFoPYVqIIsmGdUienN6wvaU4EnM8Nsy XxmkwmJV0wKv0jgECBmr05RuVs2/c4XYLfjljvekgqfMkZlQO10Vpopnx3PdduJT TdvEMEtzQT5L+rQB+tuYX98we83jtM2j/9o6zP4jbZKTS2wBvRWkVS4zUPSvtykv WeMfAgcjwuRGhAjyz+gMXMZxUfMaDLNfOFhSkHk3/JvjjFcas/WPjcFu7b0dNInW FcUapBBzxgrxSDEVHXg3YWtbSiBEO5860wIAvn/aXMCMkbZ/sLOGGE4WHGAWSfjl L09rV+6VEwIkfcEy6N9MiwjINitKWuzTjNuw8Zn6S6u7ZBMOHKtkpinup0KPyWCd GS+7kV703dniPUCQ4vNa6qcMr3xk4m4lRI3ta5Yxv3pRRyY5kcaRE5/r1sTylugj lH5jyJc4S4vkFge051/7QsValopQKgmjBz3YlXiYZAUhDDr80Wfq6bqdTMf3sxpU pypbRaGMSsK1Scu+JcK1OVu5N4DDzCp5p3kDgSiy4i5isRTISODEpDXKLWbW3h+l PbJL8bvGJUqlfqoF/2vo6cnVvbbQP7epn7bxdf5OKwdo0JOdzKQVckhiEcvO0T+d 8RfB81o8BI1TdZQxmjjxHOEmeEaZlGtEVq3w11Rts0n1Zg/jvZQ= =skYy -----END PGP SIGNATURE----- --jqgabazeogduv66k--