SQLのWHERE IN, WHERE NOT INはめちゃくちゃ遅いので、別の書き方を考える

WHERE INは遅い

#

postgresでSQLを勉強していたら、select * from users where users.id in X;のXがでかいと、INはかなり遅いことに気がついた。

どのくらい遅いのか確かめる

#

まず検証環境を確認。

テーブル
id, person_id以外は重要でない。

CREATE TABLE IF NOT EXISTS persons(
    id          bigserial PRIMARY KEY,
    country     varchar(5) REFERENCES  countries (code),
    birth_day   date,
    death_day   date,
    name        text NOT NULL,
    sex         sex_enum,
    important_note text
);

CREATE TABLE IF NOT EXISTS person_hobbies(
    person_id bigserial NOT NULL REFERENCES persons (id),
    hobby varchar(64) NOT NULL
);

データ量については、personsが1000000行。
person_hobbiesが5002686行。

計測

#

postgresの\timingを利用して計測する。

実験している途中に気がついたのだが、初回はめっちゃ時間がかかったクエリでも以降はかなり速くなったりすることがあったので、多分キャッシュが利用されているはず。

キャッシュを簡単に消す方法を検索したが見つからなかったのでキャッシュの影響ありで目安として計測する。

まず、以下のクエリを実行する。

SELECT
    *
FROM
    persons ps
WHERE
    ps.id NOT IN (
        SELECT
            person_id
        FROM
            person_hobbies)
LIMIT 100;

結果Time: 97306.018 ms (01:37.306)となり、INの右側の件数が増えると、かなり遅くなることが分かった。

100件ですらこの遅さだ。

WHERE IN, WHERE NOT INを使わない別の書き方

#

WHERE IN, WHERE NOT INが遅いのは分かったので、WHERE INの代わりになるクエリをメモとして残しておく。

where exists, where not exists

#

SELECT
    *
FROM
    persons ps
WHERE
    NOT EXISTS (
        SELECT
            TRUE
        FROM
            person_hobbies hs
        WHERE
            hs.person_id = ps.id);

全件で 435.401 msぐらいだった。

SELECT TRUEのところはSELECT 1とかでも良い。

left join

#

SELECT
    *
FROM
    persons ps
    LEFT JOIN person_hobbies hs ON ps.id = hs.person_id
WHERE
    hs.person_id IS NULL;

全件でTime: 410.988 msぐらいだった。
めちゃくちゃ改善されている。

left joinでは左側のテーブルの行はマッチに関係なく必ず返ってくるので、それを利用したクエリ。

SELECTに*ではなく、カラムを渡せば完全にpersonsテーブルの行のみを取得できる。

🔗関連記事: SQL 各種join(left join, right join, inner join, cross join)の挙動についてのメモ