(SQL)カラムの内容にキーワードが前方一致するレコードを抽出

概要

カラムの内容にキーワードが前方一致するレコードを抽出するSQLについて考える。

「カラムの内容 が キーワード に 前方一致」ではない。
「カラムの内容 に キーワード が 前方一致」である。

自分で言っていてよく分からなくなってきた。

前者をSQLで表すと下記のとおりである。

SELECT * FROM tbl WHERE col LIKE CONCAT(input, '%');

後者をSQLで表すと下記のとおりである。

SELECT * FROM tbl WHERE input LIKE CONCAT(col , '%');

後者において、下記のテーブルの場合、inputが「123ABC」ならば、No.1からNo.3までのレコードが抽出される。

no	id	something
1	1	a
2	12	b
3	123	c
4	1234	d

表題に関し、どういったクエリを投げるべきか、実際に動かして考えてみる。

環境と前提

環境

• Windows 10 64bit
• PostgreSQL 16.2.1

前提

• 上記構成のテーブルを準備
• テーブル名は「tbl」、カラム「no」をPKとする
• レコードは10万件登録
• idは1から100,000の連番とし、B-treeインデックスを付与する
• キーワードは「123ABC」とする

CREATE TABLE tbl (
    no INT,
    id VARCHAR,
    something INT,
    PRIMARY KEY(no)
);

CREATE INDEX ON tbl(id);

INSERT INTO tbl (no,id,something) 
 SELECT 
     i, i, i
 FROM
     generate_series(1,100000) as i
;

方法1

単純にカラムに'%'を付与してLIKE検索。

SELECT * FROM tbl WHERE '123ABC' LIKE CONCAT(id , '%');

Seq Scan on tbl b  (cost=0.00..2041.00 rows=500 width=13)  Filter: ('123ABC'::text ~~ concat(id, '%'))

フルスキャン。

方法2

キーワードを先頭N文字ごとに切り取った列を作成。
その後、JOIN。

WITH keywords(keyword) AS (
-- substringなどでも可
VALUES 
 ('1'),
 ('12'),
 ('123'),
 ('123A'),
 ('123AB'),
 ('123ABC')
) 
SELECT
    tbl.* 
FROM
    keywords 
    INNER JOIN tbl 
        ON keywords.keyword = tbl.id

Nested Loop  (cost=0.42..50.74 rows=6 width=13)
 +-Values Scan on "*VALUES*"  (cost=0.00..0.08 rows=6 width=32)
 +-Index Scan using tbl_id_idx on tbl b  (cost=0.42..8.44 rows=1 width=13)  Index Cond: ((id)::text = "*VALUES*".column1)

インデックスが使われる。

雑多な感想

全文インデックスを貼る手もあるが、この為だけにインデックスの容量を増大させたり、対象テーブルの登録/更新/削除のコストを高めるというのか。しかし、こういう使い方をするテーブルはおそらくマスタ系で更新も少ないだろうし、件数も少ないことが予想される。なので方法1でもコスト的に問題ないと思われる。件数が少ないならばB木インデックスも不要(使用されない)。

状況と場合によっては方法1でもインデックスが使用されるし、方法2でもインデックスが使われない場合もある。実運用での想定データを登録したうえで、実行計画を取ることが大事。

そもそも「先頭N文字がXだったら～」という判断をすることを強制するテーブル設計がおかしい。
1つのカラムに複数の意味を持たせてはいけない。

個人的な結論

レコード数が少ないならば方法1。インデックスも不要。
レコード数が多いならば方法2。対象カラムにB木インデックスを貼る。