GROUP BYを使用したチューニング

GROUP BYによるチューニング

   前回から引き続き、オプティマイザに関する注意点を解説します。ここではDISTINCTをGROUP BYに置き換えることによって処理を効率化できる例を紹介しましょう。

SELECT DISTINCTをSELECT…GROUP BYに置き換える

   DISTINCTは一般にソートが発生する負荷の高い処理です。しかも、重複が多ければ多いほどPostgreSQLでは極端に効率が落ちてしまいます。

   図17を見てください。bid列に対してインデックスを作成すればソートが発生しないので高速化できるはずですが、この例のように極端に重複が多いケースではあまり効果がありません。
 

test=# EXPLAIN ANALYZE SELECT DISTINCT ON (bid) bid, aid FROM accounts;
QUERY PLAN
-------------------------------------------------------------------------
Unique (cost=11786.60..12286.60 rows=1 width=8) (actual time=355.145..533.460 rows=1 loops=1)
->Sort (cost=11786.60..12036.60 rows=100000 width=8) (actualstime=355.141..461.437 rows=100000 loops=1)
->Sort (cost=11786.60..12036.60 rows=100000 width=8) (actual time=794.275..900.418 rows=100000 loops=1)
Sort Key:bid
->Seq Scan on accounts (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.049..182.493 rows=100000 loops=1)
Total runtime: 535.437 ms
(5 rows)


図17：SELECT DISTINCTを使った場合

   PostgreSQLは非常に効率の良いGROUP BYの処理ルーチンを備えているので、DISTINCTをGROUP BYで書き換えればかなり速度が向上します。

   この例では、図18のように2倍以上高速になりました。しかもインデックスは不要です。

test=# EXPLAIN ANALYZE SELECT bid, min(aid) FROM accounts GROUP BY bid;
QUERY PLAN
-------------------------------------------------------------------------
HashAggregate (cost=3140.00..3140.00 rows=1 width=8) (actualtime=244.240..244.241 rows=1 loops=1)
->Seq Scan on accounts (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.025..129.517 rows=100000 loops=1)
Total runtime: 244.726 ms
(3 rows)


図18：SELECT… GROUP BYを使った場合

   図18に登場するHashAggregateはGROUP BYの高速処理ルーチンです。ただし、HashAggregateは比較的多くのメモリを必要とします。もし問い合わせプランが HashAggregateではなくてSortになってしまう場合は、postgresql.confのsort_memを増やしてください