MIME-Version: 1.0
References: <CAAdwFAxBjyrYUkH7u+EceTaztd1QxBtBY1Teux8K=vcGKe==-A@mail.gmail.com>
In-Reply-To: <CAAdwFAxBjyrYUkH7u+EceTaztd1QxBtBY1Teux8K=vcGKe==-A@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sat, 11 May 2024 00:05:22 -0400
Message-ID: <CANzqJaBQHxdipDNM5KkfTmi4H1iT6y1pc4kpqyp5OucPROuYKw@mail.gmail.com>
Subject: Re: Unnecessary buffer usage with multicolumn index, row comparison,
 and equility constraint
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009352e6061825c3aa"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBQHxdipDNM5KkfTmi4H1iT6y1pc4kpqyp5OucPROuYKw%40mail.gmail.com>
Precedence: bulk

--0000000000009352e6061825c3aa
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, May 10, 2024 at 11:28=E2=80=AFPM WU Yan <4wuyan@gmail.com> wrote:

> Hi everyone, first time here. Please kindly let me know if this is not th=
e
> right place to ask.
>
> I notice a simple query can read a lot of buffer blocks in a meaningless
> way, when
> 1. there is an index scan on a multicolumn index
> 2. there is row constructor comparison in the Index Cond
> 3. there is also an equality constraint on the leftmost column of the
> multicolumn index
>
>
> ## How to reproduce
>
> I initially noticed it on AWS Aurora RDS, but it can be reproduced in
> docker container as well.
> ```bash
> docker run --name test-postgres -e POSTGRES_PASSWORD=3Dmysecretpassword -=
d
> -p 5432:5432 postgres:16.3
> ```
>
> Create a table with a multicolumn index. Populate 12 million rows with
> random integers.
> ```sql
> CREATE TABLE t(a int, b int);
> CREATE INDEX my_idx ON t USING BTREE (a, b);
>
> INSERT INTO t(a, b)
> SELECT
>     (random() * 123456)::int AS a,
>     (random() * 123456)::int AS b
> FROM
>     generate_series(1, 12345678);
>
> ANALYZE t;
> ```
>
> Simple query that uses the multicolumn index.
> ```
> postgres=3D# explain (analyze, buffers) select * from t where row(a, b) >
> row(123450, 123450) and a =3D 0 order by a, b;
>

Out of curiosity, why "where row(a, b) > row(123450, 123450)" instead of "w=
here
a > 123450 and b > 123450"?

--0000000000009352e6061825c3aa
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, May 10, 2024 at 11:28=E2=80=AFPM =
WU Yan &lt;<a href=3D"mailto:4wuyan@gmail.com">4wuyan@gmail.com</a>&gt; wro=
te:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex"><div dir=3D"ltr">Hi everyone, first time here. Please kindly=
 let me know if this is not the right place to ask.<br><br>I notice a simpl=
e query can read a lot of buffer blocks in a meaningless way, when<br>1. th=
ere is an index scan on a multicolumn index<br>2. there is row constructor =
comparison in the Index Cond<br>3. there is also an equality constraint on =
the leftmost column of the multicolumn index<br><br><br>## How to reproduce=
<br><br>I initially noticed it on AWS Aurora RDS, but it can be reproduced =
in docker container as well.<br>```bash<br>docker run --name test-postgres =
-e POSTGRES_PASSWORD=3Dmysecretpassword -d -p 5432:5432 postgres:16.3<br>``=
`<br><br>Create a table with a multicolumn index. Populate 12 million rows =
with random integers.<br>```sql<br>CREATE TABLE t(a int, b int);<br>CREATE =
INDEX my_idx ON t USING BTREE (a, b);<br><br>INSERT INTO t(a, b)<br>SELECT<=
br>=C2=A0 =C2=A0 (random() * 123456)::int AS a,<br>=C2=A0 =C2=A0 (random() =
* 123456)::int AS b<br>FROM<br>=C2=A0 =C2=A0 generate_series(1, 12345678);<=
br><br>ANALYZE t;<br>```<br><br>Simple query that uses the multicolumn inde=
x.<br>```<br>postgres=3D# explain (analyze, buffers) select * from t where =
row(a, b) &gt; row(123450, 123450) and a =3D 0 order by a, b;<br></div></bl=
ockquote><div><br></div><div>Out of curiosity, why &quot;<font face=3D"mono=
space">where row(a, b) &gt; row(123450, 123450)</font>&quot; instead of &qu=
ot;<font face=3D"monospace">where a &gt; 123450 and b &gt; 123450&quot;</fo=
nt>?</div><div><br></div></div></div>

--0000000000009352e6061825c3aa--