GROUP BYの実験
公開日：
:
Ruby on Rails
記事内に広告を含む場合があります。記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。
テストデータを作成
>> 1.upto(1000) do T4.create(:c1=>rand(6), :c2=>rand(6), :c3=>rand(6)) end
=> 1
>> T4.count
=> 1000
GROUP BY c1
SELECT c1,COUNT(*)
FROM t4s
GROUP BY c1
ORDER BY c1;
Sort (cost=44.59..45.09 rows=200 width=4) (actual time=0.586..0.586 rows=6 loops=1)
Sort Key: c1
-> HashAggregate (cost=34.45..36.95 rows=200 width=4) (actual time=0.563..0.565 rows=6 loops=1)
-> Seq Scan on t4s (cost=0.00..26.30 rows=1630 width=4) (actual time=0.010..0.170 rows=1000 loops=1)
Total runtime: 0.641 ms
(5 rows)
HashAggregateがORDER BYの処理にあたるみたい。
c1 | count
----+-------
0 | 167
1 | 165
2 | 155
3 | 168
4 | 172
5 | 173
(6 rows)
GROUP BY c1, c2
SELECT c1,c2,COUNT(*)
FROM t4s
GROUP BY c1,c2
ORDER BY c1, c2;
Sort (cost=48.67..49.17 rows=200 width=8) (actual time=0.801..0.803 rows=36 loops=1)
Sort Key: c1, c2
-> HashAggregate (cost=38.53..41.03 rows=200 width=8) (actual time=0.749..0.763 rows=36 loops=1)
-> Seq Scan on t4s (cost=0.00..26.30 rows=1630 width=8) (actual time=0.009..0.191 rows=1000 loops=1)
Total runtime: 0.859 ms
(5 rows)
やっぱりHashAggregateがORDER BYの処理なのかな?
c1 | c2 | count
----+----+-------
0 | 0 | 28
0 | 1 | 23
0 | 2 | 26
0 | 3 | 26
0 | 4 | 32
0 | 5 | 32
1 | 0 | 37
1 | 1 | 26
1 | 2 | 24
1 | 3 | 31
1 | 4 | 19
1 | 5 | 28
2 | 0 | 25
2 | 1 | 27
2 | 2 | 23
2 | 3 | 26
2 | 4 | 24
2 | 5 | 30
3 | 0 | 25
3 | 1 | 31
3 | 2 | 29
3 | 3 | 33
3 | 4 | 26
3 | 5 | 24
4 | 0 | 29
4 | 1 | 34
4 | 2 | 26
4 | 3 | 27
4 | 4 | 36
4 | 5 | 20
5 | 0 | 31
5 | 1 | 36
5 | 2 | 24
5 | 3 | 27
5 | 4 | 22
5 | 5 | 33
(36 rows)
