Date: Fri, 13 Jun 2025 14:15:17 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: Thomas Munro <thomas.munro@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Performance implications of 8K pread()s
In-Reply-To: 
 <CA+hUKGLisdw=PLn-cjtnUh7QQ_6o85vSH8bk_FjKwyEgqd2mPw@mail.gmail.com>
Message-ID: <747a3cb2-ec15-d680-c95d-be5823aff934@gmx.net>
References: <218fa2e0-bc58-e469-35dd-c5cb35906064@gmx.net>
 <CA+hUKGLisdw=PLn-cjtnUh7QQ_6o85vSH8bk_FjKwyEgqd2mPw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="0-786393783-1749816918=:361063"
UI-OutboundReport: notjunk:1;M01:P0:7f5cMg8WCEQ=;tpl0whibSx9vBLCauC+y1k54Y3r
 ByNGUo9JWAW+wv6S8738Cx1lhCO4fHcVamJB5065qlohNs5AjUxywsQlqq3IAkQEqRHq6v3GR
 qkXMEcuUdEugDul9MwDyGFaZGoF1jtQOcvR1fa/TFCHjToHWHQ2E+kzDfS2gP+jeE4OrWw/lY
 eKgrhtf5Ohn2RjpQB3tSTe67qhdG5TbO9p1u2EJ2KdEChPrsfa1O/PervWGZG46W3nfSxfI8v
 PlQ5cg2YEgGYgAmtpT1HEFSc5CqtSC8ApD804v0lRDrUK29tCJyT+vH4Epn+GPZWqgnDz0qwo
 VOb4t2Dl1nZQgG5iSdU/CajTPNMuZVgoZlcNRJz8jorONaWQtJd2ecq51CY5mmH++1cKcIj7K
 OIONAe8st41oVN1LpKuIT38IFk8tk8UqA62qnyZKIDITR1hIpnOf6cLcJd4iKOJ9IDQjmiDxd
 qrxvRKrN8aVH1D5BZhNIoiCKcQ/6iX0QwS8AOk7rYhxerYsMbclOvHEknaLdZzw1atS7m/5WB
 RwW6xliLbYDFmKnrOWhbfvpCHhjPiU6KlvB9nwuZxXkIvL3JhWyEuuEY+8yDlP72czbg6E/s/
 zvIBtWwyvd3DOOpUdL+7cjOP7rfukid360ited5Z6fNotFPdCXvTzSDz5o93SPsapHQOIypra
 WQStabJCZ0Qj+ZCcOFZ7fnbWpqZ7VY2mI+QQobsQh1BSYU8UFBNbI6vRlSYW2NLFqOz6PB3K+
 H/KitcQW4Q/W4lqXwEs/QKAsfZYZ3GxYQKM0fYE5+dZJljBxUaZdpvjc/N6eoGKkKXD1Y3eVi
 gwaa0ymjcoHuFTqCb/05WOltJoSZeAgqhRkmte3dpIGXIqQ2nmVGsH4CTruNkeOlDJhuu0dFS
 mGmt31I2vSEkFnQGeROzQRwUlpD2aHNHgp5K6pPlNxRpvBDtfBdh4nccuu8PMpjvZyh9qTbSK
 8uutQuonmV84z1gRbYWPkRMd/he6MxecBi9gFfuh1qYiCPa7e194bAU0+NpEzUGH1P5IGB+NO
 2REdwevcnwGpwMIYryi4HOPNJ83GKuH3JMbRI6xCIb8qqeqV1Rh8bHgJO/GDKmSZ/k2mbtYJ1
 60qyN9E1O3/l7kq6SFG5az66YZf8tmDRXB8SPclqqNfO/NRCz+qFWyBpkKWEw+lzLhSKG7Zc9
 W4T0Xw4mwLNARXSzYXW4oEcryu+kVq9DEsO5Cwi9pfBCzYTXNeGBpznLaCcQqv3wAUZogjkR2
 N3yFLxrdLETuecajE3qLjmr1QJdJT/P+N6P1YdhWTt1NxLocsAEYYIOqpLF/5j1xmeO9JqRIE
 PXFk02OdpSClIHsMWyrD4xff6JGQ6onemBYpud+Nirj+lWgZ2cbm/r2JNfxGqBH/xHwOtAVQd
 PFtd+QLk0ljHUXG7sMiSivHDp2mBvegS88RCo9qvpedIIg2T1yCYbBU+E2Ylkli52+gRPgJh1
 iHiiuQ/UiWuZGc8ECvGPyzwDGzDqYOwxW58vFqNqLtYApYi/sz5HOj8HUt7Wn963Fi1b17En3
 U8h/kNu7V9ufsUx49soFDHIFsDU8Ags/yqo7KspIYPCBCiFBXvxwW7ZvUFhggF0gfBx0QA8jU
 1iHDH+cLzw3Bf2eSFQyazGW8Wu5KfR9cXvNkgkVN5mgKpY6S5jlV3AQG9B/1I8QS7pA0yUw6h
 5IP7PkZ0niXhiAgbOS9m4NJFQLzXP/851Sq9f3zYNK5hdVOQIx6XEvci0a7hYbIr28Eqn+e9o
 d8vEV3u/ABNOO6wv47881Njk7ZqRbde7NFCkQwjry4bE3OcOck7PUq2yuBV4pLvQ8//yB+9uP
 5nIS0VHN/k5LzBaM718kdSSFxi5D3f3Dfzjx/3YC1gicV0C6pxAP9m8lYZBmrMVYbTFP54hBt
 g086SaI0szCZMP1zkHbUR7Df+a9v33shpjwjxjw4lYB2gc4JrK6nQul9Tdv8Kz7B5fBSu24mK
 Y0aB5zD5IVoHKMjnyUwzFMRb+FOX7vaenw2pt+cQvkHhwhDXnMSWoyW9fta9nH/6uhIvE5n6o
 YK9BW1xC8NxGvo72nNF+OR1vVk6G9iOYJV4R3F8bbdQg4lM53doYVncX21f1gC+tAfINiiBNj
 kcTLbESu0iBEnpQ6gwp1uy/59DqqhekqQiXHUibqCzyKLL5M0aXBTXyWxq2HfcoRtxJJQMtm0
 hLPZkWl9o9w8aDsqr3rP6M0f3IyBx93x/FNbZLL6W2VZVI860Ywrgs5AkGnvuPn10gyFCDoix
 HogJMi42dF/6vU9Y2+LytZuFK4JelkS5QmI+/oAuTF6PlL4ivzZB1QJJljIW2FQny40n5IHvK
 F8PY2MeAQocYGKfv7siqnMzusPfJCSZ3tkruh/J8s1+UFe9FfNQFgSDlYe5mTgqlJLgd14eAe
 z1bRFW2x38nZwVXeBfVxTXBPiT0aTg9EShpLRxjpXaAouzKGyrpWMu4fCmDuYu5OzIAQrngOl
 iigjfCPrzAuMgMtS6/6cLnR6E7ORKCZwgEtMzTaZj9zN5MdwRgAkebcaBpZyjMAnB4NmsXiWg
 cJJf+BRBewruvGmGHhsdWZcdDi8FOBz3dpuehhonTOQAXmLn7wG9/AxoSWknOkG50aVsfuu2+
 +4kOvEqkUSGBq43aDSFIebTb6hoI83GHPDp4kgemfymc9fBrxoVhSFVlQ/GWMBhlzAMXQUxqn
 PM5mhcyv54RJLTTnVxtXWmaiKlJ7cSkhx9YGaeHt9G3NOX/8A2dmOA3TyJmhxnfsySiiwSEjV
 3m4PHwkzPcU54timrOQu4T0OeFb2tphxRXOpPC8ytiIdgt1Bc5GbJISo01hLFXRFQGizNWkk6
 /MS2f4VbsICQHqYiNkPighHPtpI1iABUojACzwwjFomr25zGuVELlcjdyamWTGXzhoU9hhZGZ
 AGxqRcGY3o/oLXVQ1x85bJS1arIxcykr7skFu/r3PJiCqsfAyf92jEnVmZafWsm40yU6oFoqX
 uG321cjsH9NNlwqEm1EQLQMInFdR/Jo4O1O/ymTHfgebuO5F7pn9salfCPHTpGMG3KahTkUrS
 oYVpayjzu0DpV9+k4NnF9T/R1fP8MidGxxSF59BtEjXjjohKgVZemjzT+6SZ3y/S2pt2nmj5q
 c6G2DY7szVELtCErQ6pIbk2KMWHnJiG0VGbXUwyVrOR6ENGzukEr+h1Vq3TE+YG/hgZdE2miq
 MC3/WVjWMUSva96x1sksHCfpplVBLXvGQr43eo/noy2buvg==
Archived-At: 
 <https://www.postgresql.org/message-id/747a3cb2-ec15-d680-c95d-be5823aff934%40gmx.net>
Precedence: bulk

  This message is in MIME format.  The first part should be readable text,
  while the remaining parts are likely unreadable without MIME-aware tools.

--0-786393783-1749816918=:361063
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

On Fri, 12 Apr 2024, Thomas Munro wrote:

> On Wed, Jul 12, 2023 at 1:11=E2=80=AFAM Dimitrios Apostolou <jimis@gmx.n=
et> wrote:
>> So would it make sense for postgres to perform reads in bigger blocks? =
Is it
>> easy-ish to implement (where would one look for that)? Or must the I/O =
unit be
>> tied to postgres' page size?
>
> FYI as of last week we can do a little bit of that on the master branch:
>
> postgres=3D# select count(*) from t;
>
> preadv(46, ..., 8, 256237568) =3D 131072
> preadv(46, ..., 5, 256368640) =3D 131072
> preadv(46, ..., 8, 256499712) =3D 131072
> preadv(46, ..., 5, 256630784) =3D 131072
>
> postgres=3D# set io_combine_limit =3D '256k';
> postgres=3D# select count(*) from t;
>
> preadv(47, ..., 5, 613728256) =3D 262144
> preadv(47, ..., 5, 613990400) =3D 262144
> preadv(47, ..., 5, 614252544) =3D 262144
> preadv(47, ..., 5, 614514688) =3D 262144
>
> Here's hoping the commits implementing this stick, for the PostgreSQL
> 17 release.  It's just the beginning though, we can only do this for
> full table scans so far (plus a couple of other obscure places).
> Hopefully in the coming year we'll get the "streaming I/O" mechanism
> that powers this hooked up to lots more places... index scans and
> other stuff.  And writing.  Then eventually pushing the I/O into the
> background.  Your questions actually triggered us to talk about why we
> couldn't switch a few things around in our project and get the I/O
> combining piece done sooner.  Thanks!

Better late than never, but I have verified that this makes quite a bit of=
=20
difference on Postgres 17, on compressed Btrfs. Sequential scans on=20
transparently compressed tables are now many times faster. Same good=20
experience (if not better) with Postgres 18 and method io_uring. Can't=20
directly compare the two though, since they run on different systems.=20
Thanks!


Dimitris

--0-786393783-1749816918=:361063--