On Fri, May 10, 2024 at 11:28 PM WU Yan <4wu...@gmail.com> wrote: > 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; >
Out of curiosity, why "where row(a, b) > row(123450, 123450)" instead of "where a > 123450 and b > 123450"?
