group by と distinct 速度評価

2020/04/19

後輩が複数件あるデータからユニークなデータを作る時に 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 の方が良い。

DatabasePostgreSQL

Posted by みやみや