Re: Unnecessary buffer usage with multicolumn index, row comparison, and equility constraint

Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Fri, May 10, 2024 at 11:28 PM WU Yan <4wuyan@gmail.com> 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