From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ron Johnson <ronljohnsonjr@gmail.com>
cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: Unnecessary buffer usage with multicolumn index, row comparison, and equility constraint
In-reply-to: <CANzqJaBQHxdipDNM5KkfTmi4H1iT6y1pc4kpqyp5OucPROuYKw@mail.gmail.com>
References: <CAAdwFAxBjyrYUkH7u+EceTaztd1QxBtBY1Teux8K=vcGKe==-A@mail.gmail.com> <CANzqJaBQHxdipDNM5KkfTmi4H1iT6y1pc4kpqyp5OucPROuYKw@mail.gmail.com>
Comments: In-reply-to Ron Johnson <ronljohnsonjr@gmail.com>
	message dated "Sat, 11 May 2024 00:05:22 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1715347.1715401116.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 11 May 2024 00:18:36 -0400
Message-ID: <1715348.1715401116@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1715348.1715401116%40sss.pgh.pa.us>
Precedence: bulk

Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Fri, May 10, 2024 at 11:28=E2=80=AFPM WU Yan <4wuyan@gmail.com> wrote=
:
>> 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=
 "where
> a > 123450 and b > 123450"?

That row() condition actually means "a > 123450 OR
(a =3D 123450 AND b > 123450)", which is not the same.

(It'd be a little clearer with two different values in
the row constant, perhaps.)

It does seem like there's an optimization failure here.
I don't expect btree to analyze row comparisons exactly,
but it's sad that it seems to be stupider than for the
simplified case

explain (analyze, buffers) select * from t
where a >=3D 123450 and a =3D 0
order by a, b;
                                                  QUERY PLAN              =
                                     =

--------------------------------------------------------------------------=
-------------------------------------
 Index Only Scan using my_idx on t  (cost=3D0.43..4.45 rows=3D1 width=3D8)=
 (actual time=3D0.001..0.002 rows=3D0 loops=3D1)
   Index Cond: ((a >=3D 123450) AND (a =3D 0))
   Heap Fetches: 0
 Planning:
   Buffers: shared hit=3D4
 Planning Time: 0.081 ms
 Execution Time: 0.013 ms
(7 rows)

For that, it's able to see that the index conditions are
contradictory, so it fetches no index pages whatever.

			regards, tom lane