Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s5dOz-006wow-Pg for pgsql-general@arkaria.postgresql.org; Sat, 11 May 2024 03:28:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s5dOx-0032sH-AX for pgsql-general@arkaria.postgresql.org; Sat, 11 May 2024 03:27:59 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from <4wuyan@gmail.com>) id 1s5dOw-0032s7-Gt for pgsql-general@lists.postgresql.org; Sat, 11 May 2024 03:27:59 +0000 Received: from mail-yw1-x112c.google.com ([2607:f8b0:4864:20::112c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from <4wuyan@gmail.com>) id 1s5dOp-000Qmf-EX for pgsql-general@lists.postgresql.org; Sat, 11 May 2024 03:27:57 +0000 Received: by mail-yw1-x112c.google.com with SMTP id 00721157ae682-61e0f733e8aso30712807b3.0 for ; Fri, 10 May 2024 20:27:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1715398070; x=1716002870; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=DHHNFKR0nDxR8INwLr92W2zwoc/KVsTaZObYsdSs438=; b=ZavqYBc1iceeb5UYJnBzq+kP2YQSFg0vcJsoBswdi7v2ysoD8KA5Bm9lSYL3yHVCAS ftHKvnnMLgOhx4Bj79T5RAjNSQpnND9UOZAHYbzAR/m73labrJPif75TIiIF2/+3m+MH bW5UhFIAZpn0G2mcKQlZ+QhOek4nJWJnO07TS0V45kpzOdI+15+RLORUIQfCvKLLD0Ku /278VHaGbP9MMQYXweBWzFAXlksAkd5/vPn4Gi/6XnEmvhqYL/fUsz/F0+dUw4wSbtv8 8Lulg61Z7bMDB3Pq55rHAMGOjnBdSydGqwZMreT7dHUl/sRr240BvnEz3NJblSTGLQZX gqMg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1715398070; x=1716002870; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=DHHNFKR0nDxR8INwLr92W2zwoc/KVsTaZObYsdSs438=; b=eVPBC/+a9sq0bFSFAVTMNv4MUkqEcCEZ9z3KWyNVS4nnhAn6FiZ4ziNKwjjgb26PxF eWIaPmWfo8vcZqVQNtYCNyegxCweTvfUueEYGnX5xM+Pryi5/9YU01MA4CIb7qSuQsqy vMqFXmiHYU0APn8Zyax69Tq+tKuKt78ve6Pwz/zFG2Y31yAxe9TXqWkBKMKHMRpaXNy5 v7ht2Tgj9L9eJYVeC9qn5rEBb+ak94jM8ib/AY7sO0S3oFCQhqZOASTFdSsScOE67CYi mgWbaom7BDsI+Q2X39wYkmcV1yVRanWvwZkxVqd9YrjFFlY9f9OQq7ohixnIGyO1deyw Q4bQ== X-Gm-Message-State: AOJu0YyumKW4FSxGM9Ej7pgnFgeI3+C60RCmvu6zlR0tuTBCV/2Pdjut oflg71vlSPNiiw3B+zthnQgQRAj/IQSBe64noGIgFon64RqlflbLQYPsGGqH5pi4iWtPFmApwuQ LSkY9OBOuHNmgHg6EPwcP8bn43YtJhoxP X-Google-Smtp-Source: AGHT+IHQ7F3fNRzQv6ErBvQYW19kUwUOUHWQwXNbGk+dgBTc8tLKKkLk0u2JfoBGs/kUwgImQIU9H4qLqWqSyqbp8nM= X-Received: by 2002:a81:4e8b:0:b0:618:95a3:70b9 with SMTP id 00721157ae682-622affccb18mr44083477b3.36.1715398070407; Fri, 10 May 2024 20:27:50 -0700 (PDT) MIME-Version: 1.0 From: WU Yan <4wuyan@gmail.com> Date: Sat, 11 May 2024 13:27:40 +1000 Message-ID: Subject: Unnecessary buffer usage with multicolumn index, row comparison, and equility constraint To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000a29ed60618253c8a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a29ed60618253c8a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=3Dmysecretpassword -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=3D# explain (analyze, buffers) select * from t where row(a, b) > row(123450, 123450) and a =3D 0 order by a, b; QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------- Index Only Scan using my_idx on t (cost=3D0.43..8.46 rows=3D1 width=3D8) (actual time=3D284.312..284.314 rows=3D0 loops=3D1) Index Cond: ((ROW(a, b) > ROW(123450, 123450)) AND (a =3D 0)) Heap Fetches: 0 Buffers: shared hit=3D3777 read=3D37304 written=3D11713 Planning: Buffers: shared hit=3D22 read=3D4 Planning Time: 0.270 ms Execution Time: 284.341 ms (8 rows) ``` ## Expected output The number of buffer blocks used is high. I expect it to be no more than when there=E2=80=99s only one constraint. ``` postgres=3D# explain (analyze, buffers) select * from t where row(a, b) > row(123450, 123450) order by a, b; QUERY PLAN ---------------------------------------------------------------------------= ----------------------------------------- Index Only Scan using my_idx on t (cost=3D0.43..23.67 rows=3D642 width=3D= 8) (actual time=3D0.030..0.158 rows=3D542 loops=3D1) Index Cond: (ROW(a, b) > ROW(123450, 123450)) Heap Fetches: 0 Buffers: shared hit=3D254 read=3D3 Planning: Buffers: shared read=3D4 Planning Time: 0.232 ms Execution Time: 0.206 ms (8 rows) postgres=3D# explain (analyze, buffers) select * from t where a =3D 0 order= by a, b; QUERY PLAN ---------------------------------------------------------------------------= --------------------------------------- Index Only Scan using my_idx on t (cost=3D0.43..6.20 rows=3D101 width=3D8= ) (actual time=3D0.099..0.113 rows=3D57 loops=3D1) Index Cond: (a =3D 0) Heap Fetches: 0 Buffers: shared hit=3D27 read=3D2 Planning Time: 0.081 ms Execution Time: 0.131 ms (6 rows) ``` ## Postgres version 16.3 ## Platform information I can reproduce it on the latest postgres docker image, which is based on Debian Linux. Originally found the issue on AWS Aurora. The following are my own observation and thoughts. Please disregard if it= =E2=80=99s distraction. For a general form of ```sql select * from t where (a, b) > (x, y) and a =3D z order by a, b; ``` 1. The number of buffer blocks is proportional to the gap between x and z. Strictly, it=E2=80=99s max(0, min(x, max(a)) =E2=80=93 max(z, min(a))). ``` postgres=3D# explain (analyze, buffers) select * from t where row(a, b) > row(123450, 123450) and a =3D -30000 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=3D243.173..243.175 rows=3D0 loops=3D1) Index Cond: ((ROW(a, b) > ROW(123450, 123450)) AND (a =3D '-30000'::integer)) Heap Fetches: 0 Buffers: shared hit=3D1 read=3D41080 Planning: Buffers: shared hit=3D2 read=3D2 Planning Time: 0.174 ms Execution Time: 243.199 ms (8 rows) postgres=3D# explain (analyze, buffers) select * from t where row(a, b) > row(123450, 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=3D230.425..230.426 rows=3D0 loops=3D1) Index Cond: ((ROW(a, b) > ROW(123450, 123450)) AND (a =3D 0)) Heap Fetches: 0 Buffers: shared hit=3D1 read=3D41080 Planning: Buffers: shared read=3D4 Planning Time: 0.296 ms Execution Time: 230.460 ms (8 rows) postgres=3D# explain (analyze, buffers) select * from t where row(a, b) > row(123450, 123450) and a =3D 30000 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=3D171.787..171.788 rows=3D0 loops=3D1) Index Cond: ((ROW(a, b) > ROW(123450, 123450)) AND (a =3D 30000)) Heap Fetches: 0 Buffers: shared hit=3D1 read=3D31126 Planning: Buffers: shared read=3D4 Planning Time: 0.191 ms Execution Time: 171.812 ms (8 rows) postgres=3D# explain (analyze, buffers) select * from t where row(a, b) > row(123450, 123450) and a =3D 60000 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=3D137.516..137.518 rows=3D0 loops=3D1) Index Cond: ((ROW(a, b) > ROW(123450, 123450)) AND (a =3D 60000)) Heap Fetches: 0 Buffers: shared hit=3D1 read=3D21139 Planning: Buffers: shared read=3D4 Planning Time: 0.212 ms Execution Time: 137.543 ms (8 rows) postgres=3D# explain (analyze, buffers) select * from t where row(a, b) > row(123450, 123450) and a =3D 90000 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=3D57.868..57.870 rows=3D0 loops=3D1) Index Cond: ((ROW(a, b) > ROW(123450, 123450)) AND (a =3D 90000)) Heap Fetches: 0 Buffers: shared hit=3D11187 read=3D1 Planning: Buffers: shared hit=3D1 read=3D3 Planning Time: 0.240 ms Execution Time: 57.896 ms (8 rows) postgres=3D# explain (analyze, buffers) select * from t where row(a, b) > row(123450, 123450) and a =3D 120000 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=3D6.018..6.019 rows=3D0 loops=3D1) Index Cond: ((ROW(a, b) > ROW(123450, 123450)) AND (a =3D 120000)) Heap Fetches: 0 Buffers: shared hit=3D1173 read=3D1 Planning: Buffers: shared hit=3D4 Planning Time: 0.122 ms Execution Time: 6.052 ms (8 rows) ``` 2. It=E2=80=99s not an issue when `a=3Dx` becomes `ax`. ``` postgres=3D# explain (analyze, buffers) select * from t where row(a, b) > row(123450, 123450) and a < 100 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.006..0.006 rows=3D0 loops=3D1) Index Cond: ((ROW(a, b) > ROW(123450, 123450)) AND (a < 100)) Heap Fetches: 0 Buffers: shared hit=3D3 Planning: Buffers: shared hit=3D8 Planning Time: 0.119 ms Execution Time: 0.020 ms (8 rows) postgres=3D# explain (analyze, buffers) select * from t where row(a, b) > row(123450, 123450) and a > 100 order by a, b; QUERY PLAN ---------------------------------------------------------------------------= ----------------------------------------- Index Only Scan using my_idx on t (cost=3D0.43..25.25 rows=3D641 width=3D= 8) (actual time=3D0.040..0.339 rows=3D542 loops=3D1) Index Cond: ((ROW(a, b) > ROW(123450, 123450)) AND (a > 100)) Heap Fetches: 0 Buffers: shared hit=3D257 Planning: Buffers: shared hit=3D8 Planning Time: 0.233 ms Execution Time: 0.443 ms (8 rows) ``` 3. It=E2=80=99s not an issue when `a=3Dx` becomes `b=3Dx`. 4. The example query is trivial and for demo purpose only. Obviously there=E2=80=99s no need to supply `a =3D 0` when there=E2=80=99s `(a, b) > = (123450, 123450)`. However, in practice it can be a problem when the table is joined to other tables, resulting in a nested loop for a list of `a` values that we have no control of, while `(a, b) > (x, y)` is used for paging. 5. My current workaround is add `AND a >=3D x` to `(a, b) > (x, y)`. Howeve= r, this makes the planner underestimate the number of rows due to the multiplied selectivities. Best regards, Yan --000000000000a29ed60618253c8a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 rea= d 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 t= he Index Cond
3. there is also an equality constraint on the leftmost co= lumn of the multicolumn index


## How to reproduce

I initi= ally noticed it on AWS Aurora RDS, but it can be reproduced in docker conta= iner as well.
```bash
docker run --name test-postgres -e POSTGRES_PAS= SWORD=3Dmysecretpassword -d -p 5432:5432 postgres:16.3
```

Create= a table with a multicolumn index. Populate 12 million rows with random int= egers.
```sql
CREATE TABLE t(a int, b int);
CREATE INDEX my_idx ON= t USING BTREE (a, b);

INSERT INTO t(a, b)
SELECT
=C2=A0 =C2= =A0 (random() * 123456)::int AS a,
=C2=A0 =C2=A0 (random() * 123456)::in= t AS b
FROM
=C2=A0 =C2=A0 generate_series(1, 12345678);

ANALYZ= E t;
```

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;
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 QUERY PLAN
-----------------------------------------------= --------------------------------------------------------------------
=C2= =A0Index Only Scan using my_idx on t =C2=A0(cost=3D0.43..8.46 rows=3D1 widt= h=3D8) (actual time=3D284.312..284.314 rows=3D0 loops=3D1)
=C2=A0 =C2=A0= Index Cond: ((ROW(a, b) > ROW(123450, 123450)) AND (a =3D 0))
=C2=A0 = =C2=A0Heap Fetches: 0
=C2=A0 =C2=A0Buffers: shared hit=3D3777 read=3D373= 04 written=3D11713
=C2=A0Planning:
=C2=A0 =C2=A0Buffers: shared hit= =3D22 read=3D4
=C2=A0Planning Time: 0.270 ms
=C2=A0Execution Time: 28= 4.341 ms
(8 rows)
```

## Expected output

The number of = buffer blocks used is high. I expect it to be no more than when there=E2=80= =99s only one constraint.

```
postgres=3D# explain (analyze, buff= ers) select * from t where row(a, b) > row(123450, 123450) order by a, b= ;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN
----------------= ---------------------------------------------------------------------------= -------------------------
=C2=A0Index Only Scan using my_idx on t =C2=A0= (cost=3D0.43..23.67 rows=3D642 width=3D8) (actual time=3D0.030..0.158 rows= =3D542 loops=3D1)
=C2=A0 =C2=A0Index Cond: (ROW(a, b) > ROW(123450, 1= 23450))
=C2=A0 =C2=A0Heap Fetches: 0
=C2=A0 =C2=A0Buffers: shared hit= =3D254 read=3D3
=C2=A0Planning:
=C2=A0 =C2=A0Buffers: shared read=3D4=
=C2=A0Planning Time: 0.232 ms
=C2=A0Execution Time: 0.206 ms
(8 r= ows)

postgres=3D# explain (analyze, buffers) select * from t where a= =3D 0 order by a, b;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN
-= ---------------------------------------------------------------------------= --------------------------------------
=C2=A0Index Only Scan using my_id= x on t =C2=A0(cost=3D0.43..6.20 rows=3D101 width=3D8) (actual time=3D0.099.= .0.113 rows=3D57 loops=3D1)
=C2=A0 =C2=A0Index Cond: (a =3D 0)
=C2=A0= =C2=A0Heap Fetches: 0
=C2=A0 =C2=A0Buffers: shared hit=3D27 read=3D2=C2=A0Planning Time: 0.081 ms
=C2=A0Execution Time: 0.131 ms
(6 rows= )
```

## Postgres version

16.3

## Platform informat= ion

I can reproduce it on the latest postgres docker image, which is= based on Debian Linux. Originally found the issue on AWS Aurora.


The following are my own observation and thoughts. Please disregard i= f it=E2=80=99s distraction.

For a general form of
```sql
selec= t * from t where (a, b) > (x, y) and a =3D z order by a, b;
```
1. The number of buffer blocks is proportional to the gap between x and z= . Strictly, it=E2=80=99s max(0, min(x, max(a)) =E2=80=93 max(z, min(a))).
```
postgres=3D# explain (analyze, buffers) select * from t where = row(a, b) > row(123450, 123450) and a =3D -30000 order by a, b;
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN
-----------------------------= ---------------------------------------------------------------------------= -----------
=C2=A0Index Only Scan using my_idx on t =C2=A0(cost=3D0.43..= 4.45 rows=3D1 width=3D8) (actual time=3D243.173..243.175 rows=3D0 loops=3D1= )
=C2=A0 =C2=A0Index Cond: ((ROW(a, b) > ROW(123450, 123450)) AND (a = =3D '-30000'::integer))
=C2=A0 =C2=A0Heap Fetches: 0
=C2=A0 = =C2=A0Buffers: shared hit=3D1 read=3D41080
=C2=A0Planning:
=C2=A0 =C2= =A0Buffers: shared hit=3D2 read=3D2
=C2=A0Planning Time: 0.174 ms
=C2= =A0Execution Time: 243.199 ms
(8 rows)

postgres=3D# explain (anal= yze, buffers) select * from t where row(a, b) > row(123450, 123450) and = a =3D 0 order by a, b;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN
-= ---------------------------------------------------------------------------= ---------------------------------------
=C2=A0Index Only Scan using my_i= dx on t =C2=A0(cost=3D0.43..4.45 rows=3D1 width=3D8) (actual time=3D230.425= ..230.426 rows=3D0 loops=3D1)
=C2=A0 =C2=A0Index Cond: ((ROW(a, b) > = ROW(123450, 123450)) AND (a =3D 0))
=C2=A0 =C2=A0Heap Fetches: 0
=C2= =A0 =C2=A0Buffers: shared hit=3D1 read=3D41080
=C2=A0Planning:
=C2=A0= =C2=A0Buffers: shared read=3D4
=C2=A0Planning Time: 0.296 ms
=C2=A0E= xecution Time: 230.460 ms
(8 rows)

postgres=3D# explain (analyze,= buffers) select * from t where row(a, b) > row(123450, 123450) and a = =3D 30000 order by a, b;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN-------------------------------------------------------------------------= ------------------------------------------
=C2=A0Index Only Scan using m= y_idx on t =C2=A0(cost=3D0.43..4.45 rows=3D1 width=3D8) (actual time=3D171.= 787..171.788 rows=3D0 loops=3D1)
=C2=A0 =C2=A0Index Cond: ((ROW(a, b) &g= t; ROW(123450, 123450)) AND (a =3D 30000))
=C2=A0 =C2=A0Heap Fetches: 0<= br>=C2=A0 =C2=A0Buffers: shared hit=3D1 read=3D31126
=C2=A0Planning:
= =C2=A0 =C2=A0Buffers: shared read=3D4
=C2=A0Planning Time: 0.191 ms
= =C2=A0Execution Time: 171.812 ms
(8 rows)

postgres=3D# explain (a= nalyze, buffers) select * from t where row(a, b) > row(123450, 123450) a= nd a =3D 60000 order by a, b;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PL= AN
---------------------------------------------------------------------= ----------------------------------------------
=C2=A0Index Only Scan usi= ng my_idx on t =C2=A0(cost=3D0.43..4.45 rows=3D1 width=3D8) (actual time=3D= 137.516..137.518 rows=3D0 loops=3D1)
=C2=A0 =C2=A0Index Cond: ((ROW(a, b= ) > ROW(123450, 123450)) AND (a =3D 60000))
=C2=A0 =C2=A0Heap Fetches= : 0
=C2=A0 =C2=A0Buffers: shared hit=3D1 read=3D21139
=C2=A0Planning:=
=C2=A0 =C2=A0Buffers: shared read=3D4
=C2=A0Planning Time: 0.212 ms<= br>=C2=A0Execution Time: 137.543 ms
(8 rows)

postgres=3D# explain= (analyze, buffers) select * from t where row(a, b) > row(123450, 123450= ) and a =3D 90000 order by a, b;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY = PLAN
-------------------------------------------------------------------= ----------------------------------------------
=C2=A0Index Only Scan usi= ng my_idx on t =C2=A0(cost=3D0.43..4.45 rows=3D1 width=3D8) (actual time=3D= 57.868..57.870 rows=3D0 loops=3D1)
=C2=A0 =C2=A0Index Cond: ((ROW(a, b) = > ROW(123450, 123450)) AND (a =3D 90000))
=C2=A0 =C2=A0Heap Fetches: = 0
=C2=A0 =C2=A0Buffers: shared hit=3D11187 read=3D1
=C2=A0Planning:=C2=A0 =C2=A0Buffers: shared hit=3D1 read=3D3
=C2=A0Planning Time: 0.2= 40 ms
=C2=A0Execution Time: 57.896 ms
(8 rows)

postgres=3D# ex= plain (analyze, buffers) select * from t where row(a, b) > row(123450, 1= 23450) and a =3D 120000 order by a, b;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY= PLAN
------------------------------------------------------------------= ---------------------------------------------
=C2=A0Index Only Scan usin= g my_idx on t =C2=A0(cost=3D0.43..4.45 rows=3D1 width=3D8) (actual time=3D6= .018..6.019 rows=3D0 loops=3D1)
=C2=A0 =C2=A0Index Cond: ((ROW(a, b) >= ; ROW(123450, 123450)) AND (a =3D 120000))
=C2=A0 =C2=A0Heap Fetches: 0<= br>=C2=A0 =C2=A0Buffers: shared hit=3D1173 read=3D1
=C2=A0Planning:
= =C2=A0 =C2=A0Buffers: shared hit=3D4
=C2=A0Planning Time: 0.122 ms
= =C2=A0Execution Time: 6.052 ms
(8 rows)
```

2. It=E2=80=99s no= t an issue when `a=3Dx` becomes `a<x` or `a>x`.

```
postgre= s=3D# explain (analyze, buffers) select * from t where row(a, b) > row(1= 23450, 123450) and a < 100 order by a, b;
=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Q= UERY PLAN
--------------------------------------------------------------= -------------------------------------------------
=C2=A0Index Only Scan = using my_idx on t =C2=A0(cost=3D0.43..4.45 rows=3D1 width=3D8) (actual time= =3D0.006..0.006 rows=3D0 loops=3D1)
=C2=A0 =C2=A0Index Cond: ((ROW(a, b)= > ROW(123450, 123450)) AND (a < 100))
=C2=A0 =C2=A0Heap Fetches: = 0
=C2=A0 =C2=A0Buffers: shared hit=3D3
=C2=A0Planning:
=C2=A0 =C2= =A0Buffers: shared hit=3D8
=C2=A0Planning Time: 0.119 ms
=C2=A0Execut= ion Time: 0.020 ms
(8 rows)

postgres=3D# explain (analyze, buffer= s) select * from t where row(a, b) > row(123450, 123450) and a > 100 = order by a, b;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN
--= ---------------------------------------------------------------------------= ---------------------------------------
=C2=A0Index Only Scan using my_i= dx on t =C2=A0(cost=3D0.43..25.25 rows=3D641 width=3D8) (actual time=3D0.04= 0..0.339 rows=3D542 loops=3D1)
=C2=A0 =C2=A0Index Cond: ((ROW(a, b) >= ROW(123450, 123450)) AND (a > 100))
=C2=A0 =C2=A0Heap Fetches: 0
= =C2=A0 =C2=A0Buffers: shared hit=3D257
=C2=A0Planning:
=C2=A0 =C2=A0B= uffers: shared hit=3D8
=C2=A0Planning Time: 0.233 ms
=C2=A0Execution = Time: 0.443 ms
(8 rows)
```

3. It=E2=80=99s not an issue when = `a=3Dx` becomes `b=3Dx`.

4. The example query is trivial and for dem= o purpose only. Obviously there=E2=80=99s no need to supply `a =3D 0` when = there=E2=80=99s `(a, b) > (123450, 123450)`. However, in practice it can= be a problem when the table is joined to other tables, resulting in a nest= ed loop for a list of `a` values that we have no control of, while `(a, b) = > (x, y)` is used for paging.

5. My current workaround is add `AN= D a >=3D x` to `(a, b) > (x, y)`. However, this makes the planner und= erestimate the number of rows due to the multiplied selectivities.

B= est regards,
Yan
--000000000000a29ed60618253c8a--