結論

行値式を使うと複数カラムの組み合わせに対して一度に条件を指定できる
サブクエリとの組み合わせが便利

課題

テーブル定義とユースケース

以下のようなテーブルがあったとして、

$ cat ddl.sql
drop table if exists test;
drop index if exists test_a_b_index;

create table test (
        id integer primary key,
        a integer not null,
        b integer not null
);
create index test_a_b_index on test (a, b);

次のような状況を考えてみる。

アプリケーションでは id を入力として受け取り、それを元にレコードを検索する
該当レコードと a, b の値が一致するレコードを一斉に操作したい

サンプルデータ

括弧内は左から id, a, b
a, b が (1, 1) となるレコードが 6 件ある
id=1 で検索して前述の 6 件が抽出されるような SQL を考えてみる

$ head -n5 dml.sql && tail -n5 dml.sql
insert into test values
(1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 4, 4),
(50001, 1, 1),
(50002, 1, 1),
(50003, 1, 1),
(50004, 1, 1),
(50005, 1, 1);

解決策

いずれも PostgreSQL 15.3 での確認結果に基づく。

(1) 行値式を使わない場合

まずは行値式を使わない場合。ここではやりたいことを分かりやすく示すためにサブクエリを使ってるが、他にもやりようはありそうです（愚直にクエリ実行を 2 回に分ける、join で頑張る等）

select
    * 
from 
    test 
where
    a = (select a from test where id = 1) 
and
    b = (select b from test where id = 1)

(2) 行値式を使う場合

こちらが今回の主題。in 句の評価式に注目。(a, b) が行値式。

サブクエリが返す列の数と順番は行値式と一致している必要がある
in 句ではなく他の比較演算子、例えば = でも動く。ただしその場合はサブクエリが返す結果が 1 件である必要がある（結果が複数の場合はエラーになる）
a, b の複合インデックスもちゃんと効く

select
    *
from
    test
where
    (a, b) in (
        select
            a, b
        from
            test
        where
            id = 1
    )

in 句の値の指定はサブクエリじゃなくても OK。検索値が明確な場合は以下のように列挙すればよい

select
    *
from
    test
where
    (a, b) in (
        (1, 1),
        (2, 2),
        (3, 3)
    )

ノート

ファイル一式

pg-row-constructor.zip - Google ドライブ

確認用の Docker コンテナ

起動

POSTGRES_HOST_AUTH_METHOD に trust を指定している点に注意
セキュリティが重要な場面では適切な認証を設定してください

$ docker run -d \
  --name postgres-test \
  -p 127.0.0.1:5432:5432 \
  -e POSTGRES_HOST_AUTH_METHOD=trust \
  -v data:/var/lib/postgresql/data \
  postgres:15.3

停止・削除

$ docker stop postgres-test
$ docker rm postgres-test

テーブル作成・データ投入

$ go run dml.go > dml.sql
$ psql -h localhost -U postgres -f ddl.sql -f dml.sql

抽出結果

(1)

$ psql -h localhost -U postgres -f select-1.sql
  id   | a | b
-------+---+---
     1 | 1 | 1
 50001 | 1 | 1
 50002 | 1 | 1
 50003 | 1 | 1
 50004 | 1 | 1
 50005 | 1 | 1
(6 rows)

(2)

$ psql -h localhost -U postgres -f select-2.sql
  id   | a | b
-------+---+---
     1 | 1 | 1
 50001 | 1 | 1
 50002 | 1 | 1
 50003 | 1 | 1
 50004 | 1 | 1
 50005 | 1 | 1
(6 rows)

explain の結果

(1)

$ psql -h localhost -U postgres -f explain-1.sql
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using test_a_b_index on public.test  (cost=16.90..24.92 rows=1 width=12)
   Output: test.id, test.a, test.b
   Index Cond: ((test.a = $0) AND (test.b = $1))
   InitPlan 1 (returns $0)
     ->  Index Scan using test_pkey on public.test test_1  (cost=0.29..8.31 rows=1 width=4)
           Output: test_1.a
           Index Cond: (test_1.id = 1)
   InitPlan 2 (returns $1)
     ->  Index Scan using test_pkey on public.test test_2  (cost=0.29..8.31 rows=1 width=4)
           Output: test_2.b
           Index Cond: (test_2.id = 1)
(11 rows)

(2)

$ psql -h localhost -U postgres -f explain-2.sql
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.58..16.63 rows=1 width=12)
   Output: test.id, test.a, test.b
   ->  Index Scan using test_pkey on public.test test_1  (cost=0.29..8.31 rows=1 width=8)
         Output: test_1.id, test_1.a, test_1.b
         Index Cond: (test_1.id = 1)
   ->  Index Scan using test_a_b_index on public.test  (cost=0.29..8.31 rows=1 width=12)
         Output: test.id, test.a, test.b
         Index Cond: ((test.a = test_1.a) AND (test.b = test_1.b))
(8 rows)

user8’s blog

行値式で複数カラムの組み合わせに対して一度に条件を指定する（PostgreSQL）

結論

課題