public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: Ron Johnson <[email protected]>
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Unnecessary buffer usage with multicolumn index, row comparison, and equility constraint
Date: Sat, 11 May 2024 00:18:36 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CANzqJaBQHxdipDNM5KkfTmi4H1iT6y1pc4kpqyp5OucPROuYKw@mail.gmail.com>
References: <CAAdwFAxBjyrYUkH7u+EceTaztd1QxBtBY1Teux8K=vcGKe==-A@mail.gmail.com>
<CANzqJaBQHxdipDNM5KkfTmi4H1iT6y1pc4kpqyp5OucPROuYKw@mail.gmail.com>
Ron Johnson <[email protected]> writes:
> On Fri, May 10, 2024 at 11:28 PM WU Yan <[email protected]> wrote:
>> Simple query that uses the multicolumn index.
>> postgres=# explain (analyze, buffers) select * from t where row(a, b) >
>> row(123450, 123450) and a = 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 = 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 >= 123450 and a = 0
order by a, b;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Index Only Scan using my_idx on t (cost=0.43..4.45 rows=1 width=8) (actual time=0.001..0.002 rows=0 loops=1)
Index Cond: ((a >= 123450) AND (a = 0))
Heap Fetches: 0
Planning:
Buffers: shared hit=4
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
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Unnecessary buffer usage with multicolumn index, row comparison, and equility constraint
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox