GroupBy と Distinct

Transcript

1. GroupBy と Distinct Miyazato
2. group by とは • SQL の SELECT 文において、指定した列の 値が等しい行ごとに表をグループ化する
3. group by とは • SQL の SELECT 文において、指定した列の 値が等しい行ごとに表をグループ化する 一意の値が取得できる
4. distinct とは • SQL の SELECT 文において、冗長な重複行 を取り除く
5. distinct とは • SQL の SELECT 文において、冗長な重複行 を取り除く 一意の値が取得できる
6. group by と distinct を比べる • 某クラウド、メモリ2GB • レコード件数 10万件 • Name カラムには 47都道府県名がランダムに入っている
7. distinct
8. group by
9. 同じデータ 取れるなら 同じでしょ
10. Group by は カラム合わせるの 面倒なんだよ
12. group by と distinct を比べる2 • 某クラウド、メモリ2GB • レコード件数 10万件 • Name カラムには 47都道府県名がランダムに入っている
13. 検証する DB • PostgreSQL 7.4
14. 検証する DB • PostgreSQL 7.4 • PostgreSQL 8.4
15. 検証する DB • PostgreSQL 7.4 • PostgreSQL 8.4 • PostgreSQL 9.3
16. 検証する方法 • EXPLAIN で実際の実行時間を計測する • 検証するパターン – Name カラムにインデックスをはらない • EXPLAIN ANALYZE select distinct on (name) name FROM pgtest; • EXPLAIN ANALYZE select distinct name FROM pgtest; • EXPLAIN ANALYZE select name from pgtest group by name; – Name カラムにインデックスをはる • EXPLAIN ANALYZE select distinct on (name) name FROM pgtest; • EXPLAIN ANALYZE select distinct name FROM pgtest; • EXPLAIN ANALYZE select name from pgtest group by name;
17. スピードを計測 • PostgreSQL7.4 – EXPLAIN ANALYZE select distinct on (name) name FROM pgtest; • 実行計画：Unique → Sort → Seq Scan on pgtest • 実行時間：3223.304 ms – EXPLAIN ANALYZE select distinct name FROM pgtest; • 実行計画：Unique → Sort → Seq Scan on pgtest • 実行時間：3206.429 ms – EXPLAIN ANALYZE select name from pgtest group by name; • 実行計画：HashAggregate → Seq Scan on pgtest • 実行時間：272.049 ms
18. スピードを計測 • PostgreSQL7.4(インデックスあり) – EXPLAIN ANALYZE select distinct on (name) name FROM pgtest; • 実行計画：Unique → Sort → Seq Scan on pgtest • 実行時間：3219.604 ms – EXPLAIN ANALYZE select distinct name FROM pgtest; • 実行計画：Unique → Sort → Seq Scan on pgtest • 実行時間：3217.941 ms – EXPLAIN ANALYZE select name from pgtest group by name; • 実行計画：HashAggregate → Seq Scan on pgtest • 実行時間：267.396 ms
19. スピードを計測 • PostgreSQL8.4 – EXPLAIN ANALYZE select distinct on (name) name FROM pgtest; • 実行計画：Unique → Sort → Seq Scan on pgtest • 実行時間：2785.410 ms – EXPLAIN ANALYZE select distinct name FROM pgtest; • 実行計画：HashAggregate → Seq Scan on pgtest • 実行時間：233.662 ms – EXPLAIN ANALYZE select name from pgtest group by name; • 実行計画：HashAggregate → Seq Scan on pgtest • 実行時間：230.086 ms
20. スピードを計測 • PostgreSQL8.4(インデックスあり) – EXPLAIN ANALYZE select distinct on (name) name FROM pgtest; • 実行計画：Unique → Index Scan using pgtest_idx1 on pgtest • 実行時間：267.068 ms – EXPLAIN ANALYZE select distinct name FROM pgtest; • 実行計画：Unique → Index Scan using pgtest_idx1 on pgtest • 実行時間：267.044 ms – EXPLAIN ANALYZE select name from pgtest group by name; • 実行計画：HashAggregate → Seq Scan on pgtest • 実行時間：231.749 ms
21. スピードを計測 • PostgreSQL9.3 – EXPLAIN ANALYZE select distinct on (name) name FROM pgtest; • 実行計画：Unique → Sort → Seq Scan on pgtest • 実行時間：2902.322 ms – EXPLAIN ANALYZE select distinct name FROM pgtest; • 実行計画：HashAggregate → Seq Scan on pgtest • 実行時間：239.562 ms – EXPLAIN ANALYZE select name from pgtest group by name; • 実行計画：HashAggregate → Seq Scan on pgtest • 実行時間：235.504 ms
22. スピードを計測 • PostgreSQL9.3(インデックスあり) – EXPLAIN ANALYZE select distinct on (name) name FROM pgtest; • 実行計画：Unique → Index Only Scan using pgtest_idx1 on pgtest • 実行時間：222.463 ms – EXPLAIN ANALYZE select distinct name FROM pgtest; • 実行計画：HashAggregate → Seq Scan on pgtest • 実行時間：239.562 ms – EXPLAIN ANALYZE select name from pgtest group by name; • 実行計画：HashAggregate → Seq Scan on pgtest • 実行時間：235.504 ms
23. 結論 • PostgreSQL のバージョンによって動作が違う • インデックスのありなしでも動作が変わる • Group by を使ったほうがどのバージョンでも 問題なく安定的に動作する
25. そもそも用途が違う • Distinct は重複レコードを破棄する ◦ 大量データを扱うときにメモリ効率が良い ☓ 一意の値で集計したいときに使えない • Group by は集計関数 ◦ 一意の値で集計が可能 ☓ 大量のデータ件数を貯めこむので メモリ効率が悪い
26. Group by はこうできるだろ？
27. ん？
28. 結論 • PostgreSQL のバージョンによって動作が違う • インデックスのありなしでも動作が変わる • Group by を使ったほうがどのバージョンでも 問題なく安定的に動作する • 用途が違うので、違いを理解してケースバイ ケースで使い分けることが重要
29. 結論 • PostgreSQL のバージョンによって動作が違う • インデックスのありなしでも動作が変わる • Group by を使ったほうがどのバージョンでも 問題なく安定的に動作する • 用途が違うので、違いを理解してケースバイ ケースで使い分けることが重要 • 都道府県は 46 に減ったらしい ←NEW!!
30. Thank you for your attention.