group by と distinct 速度評価

後輩が複数件あるデータからユニークなデータを作る時に distinct を使っていた。 俺は group by の方が高速だよと何か読んだことがあったのでちょっと驚いた。

とりあえず指摘はしたんだけど、どうにも腑に落ちなかったので、実際にどちらが良いのか自分なりに調べてみた。

共通条件

• 某クラウド、メモリ 2GB
• レコード件数 10万件
• name には 47都道府県名がランダムに入っている ○使用テーブル

CREATE TABLE pgtest
(
    id         serial      PRIMARY KEY,
    name       text,
    data       text,
    created_at timestamp(0) DEFAULT now(),
    updated_at timestamp(0) DEFAULT now()
);

条件1：PostgreSQL 7.4 の時

• EXPLAIN ANALYZE select distinct on (name) name FROM pgtest; Unique ->Sort ->Seq Scan on pgtest

  Total runtime: 3223.304 ms

• EXPLAIN ANALYZE select distinct name FROM pgtest; Unique ->Sort ->Seq Scan on pgtest

  Total runtime: 3206.429 ms

• EXPLAIN ANALYZE select name from pgtest group by name; HashAggregate ->Seq Scan on pgtest

  Total runtime: 272.049 ms

条件2：PostgreSQL 7.4 で name にインデックスをはった時

• EXPLAIN ANALYZE select distinct on (name) name FROM pgtest; Unique ->Sort ->Seq Scan on pgtest

  Total runtime: 3219.604 ms

• EXPLAIN ANALYZE select distinct name FROM pgtest; Unique ->Sort ->Seq Scan on pgtest

  Total runtime: 3217.941 ms

• EXPLAIN ANALYZE select name from pgtest group by name; HashAggregate ->Seq Scan on pgtest

  Total runtime: 267.396 ms

条件3：PostgreSQL 8.4 の時

• EXPLAIN ANALYZE select distinct on (name) name FROM pgtest; Unique ->Sort ->Seq Scan on pgtest

  Total runtime: 2785.410 ms

• EXPLAIN ANALYZE select distinct name FROM pgtest; HashAggregate ->Seq Scan on pgtest

  Total runtime: 233.662 ms

• EXPLAIN ANALYZE select name from pgtest group by name; HashAggregate ->Seq Scan on pgtest

  Total runtime: 230.086 ms

条件4：PostgreSQL 8.4 で name にインデックスをはった時

• EXPLAIN ANALYZE select distinct on (name) name FROM pgtest; Unique ->Index Scan using pgtest_idx1 on pgtest

  Total runtime: 267.068 ms

• EXPLAIN ANALYZE select distinct name FROM pgtest; Unique ->Index Scan using pgtest_idx1 on pgtest

  Total runtime: 267.044 ms

• EXPLAIN ANALYZE select name from pgtest group by name; HashAggregate ->Seq Scan on pgtest

  Total runtime:231.749 ms

条件5：PostgreSQL 9.3 の時

• EXPLAIN ANALYZE select distinct on (name) name FROM pgtest; Unique ->Sort ->Seq Scan on pgtest

  Total runtime: 2902.322 ms

• EXPLAIN ANALYZE select distinct name FROM pgtest; HashAggregate ->Seq Scan on pgtest

  Total runtime:239.562 ms

• EXPLAIN ANALYZE select name from pgtest group by name; HashAggregate ->Seq Scan on pgtest

  Total runtime:235.504 ms

条件6：PostgreSQL 9.3 で name にインデックスをはった時

• EXPLAIN ANALYZE select distinct on (name) name FROM pgtest; Unique ->Index Only Scan using pgtest_idx1 on pgtest

  Total runtime: 222.463 ms

• EXPLAIN ANALYZE select distinct name FROM pgtest; HashAggregate ->Seq Scan on pgtest

  Total runtime: 235.667 ms

• EXPLAIN ANALYZE select name from pgtest group by name; HashAggregate ->Seq Scan on pgtest

  Total runtime: 232.319 ms

俺なりの結論としては、バージョンやインデックスに左右されていない group by の方が良い。