Re: Unnecessary buffer usage with multicolumn index, row comparison, and equility constraint - Mailing list pgsql-general

Hi everyone, first time here. Please kindly let me know if this is not the 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=mysecretpassword -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=# explain (analyze, buffers) select * from t where row(a, b) > row(123450, 123450) and a = 0 order by a, b;