結論

CentOSにyumでインストールしたPostgreSQLはinitdbするとロケールがUTF8となります。
# SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc%';
    name     |   setting   |  context
-------------+-------------+-----------
 lc_collate  | ja_JP.UTF-8 | internal
 lc_ctype    | ja_JP.UTF-8 | internal
 lc_messages | ja_JP.UTF-8 | superuser
 lc_monetary | ja_JP.UTF-8 | user
 lc_numeric  | ja_JP.UTF-8 | user
 lc_time     | ja_JP.UTF-8 | user
(6 行)
ロケールがUTF8の場合、日本語を含む列をGROUP BYするような場合、とても遅くなります。

$ /usr/pgsql-9.3/bin/initdb -D /var/lib/pgsql/9.3/data --locale=C
と、ロケールをCに設定することをお勧めします。
# SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc%';
    name     | setting |  context
-------------+---------+-----------
 lc_collate  | C       | internal
 lc_ctype    | C       | internal
 lc_messages | C       | superuser
 lc_monetary | C       | user
 lc_numeric  | C       | user
 lc_time     | C       | user
(6 行)

いきさつ概要

PostgreSQL9.0からPostgreSQL9.3にVerUPする際、今までは
$ /usr/pgsql-9.0/bin/initdb -D /var/lib/pgsql/9.0/data --locale=C
と、ロケールにCを指定して初期化していました。
※確か、ロケールをCにしないとORDER BYしたときの並び方が感覚にあっていない…という理由だったと思います。

しかし、9.3を導入したときは
# service postgresql-9.3 initdb
としていました。
この場合、ロケールはUTF8で設定されます。

理屈は分かりませんが、ロケールをUTF-8とした場合、特に大量の日本語が格納されている列をGROUP BY句にいれると、SQLの実行が異常に遅くなる事象を確認しました。


いきさつ

PostgreSQLを9.0から9.3 にあげたらCPU負荷があがりました。

無題

また微妙にディスクI/Oも増えました。
無題1

9.0と9.3でコストが高いSQLを実行して比較しました。
実行したSQLは以下の通りです。

select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;

まずはEXPLAINの結果を比較します。
9.0のEXPLAINの結果は以下の通りです。
# EXPLAIN select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Sort  (cost=796875.09..799229.12 rows=941611 width=291)
   Sort Key: (count(u.tokuten))
   ->  GroupAggregate  (cost=424784.57..445970.82 rows=941611 width=291)
         ->  Sort  (cost=424784.57..427138.60 rows=941611 width=291)
               Sort Key: u.uid, myuser.hitokoto
               ->  Hash Join  (cost=5755.99..73880.29 rows=941611 width=291)
                     Hash Cond: (u.uid = myuser.uid)
                     ->  Seq Scan on userreview u  (cost=0.00..39965.11 rows=941611 width=12)
                     ->  Hash  (cost=2455.44..2455.44 rows=65244 width=288)
                           ->  Seq Scan on myuser  (cost=0.00..2455.44 rows=65244 width=288)
(10 行)

9.3のEXPLAINの結果は以下の通りです。
# EXPLAIN  select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Sort  (cost=810050.34..812417.85 rows=947004 width=298)
   Sort Key: (count(u.tokuten))
   ->  GroupAggregate  (cost=431679.86..450619.94 rows=947004 width=298)
         ->  Sort  (cost=431679.86..434047.37 rows=947004 width=298)
               Sort Key: u.uid, myuser.hitokoto
               ->  Hash Join  (cost=5805.85..72249.46 rows=947004 width=298)
                     Hash Cond: (u.uid = myuser.uid)
                     ->  Seq Scan on userreview u  (cost=0.00..38070.04 rows=947004 width=12)
                     ->  Hash  (cost=2438.82..2438.82 rows=65282 width=295)
                           ->  Seq Scan on myuser  (cost=0.00..2438.82 rows=65282 width=295)
(10 行)

EXPLAINの実行結果はあまりかわりません。
次に、  EXPLAIN ANALYZEの結果をみます。

まずは9.0です。
# EXPLAIN ANALYZE select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;
                                                                   QUERY PLAN

------------------------------------------------------------------------------------------------------------------------
-------------------------
 Sort  (cost=796875.09..799229.12 rows=941611 width=291) (actual time=7839.773..7842.543 rows=26204 loops=1)
   Sort Key: (count(u.tokuten))
   Sort Method:  quicksort  Memory: 3985kB
   ->  GroupAggregate  (cost=424784.57..445970.82 rows=941611 width=291) (actual time=6589.616..7826.559 rows=26204 loop
s=1)
         ->  Sort  (cost=424784.57..427138.60 rows=941611 width=291) (actual time=6589.603..7410.715 rows=946356 loops=1
)
               Sort Key: u.uid, myuser.hitokoto
               Sort Method:  external merge  Disk: 162392kB
               ->  Hash Join  (cost=5755.99..73880.29 rows=941611 width=291) (actual time=291.349..2941.949 rows=946356
loops=1)
                     Hash Cond: (u.uid = myuser.uid)
                     ->  Seq Scan on userreview u  (cost=0.00..39965.11 rows=941611 width=12) (actual time=14.134..1748.
609 rows=946356 loops=1)
                     ->  Hash  (cost=2455.44..2455.44 rows=65244 width=288) (actual time=276.851..276.851 rows=65244 loo
ps=1)
                           Buckets: 2048  Batches: 8  Memory Usage: 508kB
                           ->  Seq Scan on myuser  (cost=0.00..2455.44 rows=65244 width=288) (actual time=6.345..229.775
 rows=65244 loops=1)
 Total runtime: 7874.282 ms
(14 行)

次に9.3です。
# EXPLAIN  ANALYZE select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;
                                                                  QUERY PLAN

------------------------------------------------------------------------------------------------------------------------
-----------------------
 Sort  (cost=810050.34..812417.85 rows=947004 width=298) (actual time=466359.118..466362.684 rows=26224 loops=1)
   Sort Key: (count(u.tokuten))
   Sort Method: quicksort  Memory: 3986kB
   ->  GroupAggregate  (cost=431679.86..450619.94 rows=947004 width=298) (actual time=388687.232..466347.076 rows=26224
loops=1)
         ->  Sort  (cost=431679.86..434047.37 rows=947004 width=298) (actual time=388678.206..465844.530 rows=947102 loo
ps=1)
               Sort Key: u.uid, myuser.hitokoto
               Sort Method: external merge  Disk: 162656kB
               ->  Hash Join  (cost=5805.85..72249.46 rows=947004 width=298) (actual time=48.662..1106.059 rows=947102 l
oops=1)
                     Hash Cond: (u.uid = myuser.uid)
                     ->  Seq Scan on userreview u  (cost=0.00..38070.04 rows=947004 width=12) (actual time=0.045..291.16
0 rows=947102 loops=1)
                     ->  Hash  (cost=2438.82..2438.82 rows=65282 width=295) (actual time=48.411..48.411 rows=65291 loops
=1)
                           Buckets: 2048  Batches: 8  Memory Usage: 505kB
                           ->  Seq Scan on myuser  (cost=0.00..2438.82 rows=65282 width=295) (actual time=0.036..28.135
rows=65291 loops=1)
 Total runtime: 466395.321 ms
(14 行)

9.3の方がとんでもなく時間がかかっています。
ソートにディスクを使いまくっているので、9.3になったらディスクの使い方がまずくなったのか?と思いましたので、work_memを300MBに増やして再度実行してみました。
※ぐぐると、 external merge  Diskの量だけwork_memに積めばOKなことが書いてあるのですが、実際はexternal merge  Diskより多くのメモリを確保しないとメモリを使ってソートしてくれませんでした。今回のSQLの場合、work_memを276610kB確保する必要がありました。

9.0の実行結果は以下の通りです。
# EXPLAIN ANALYZE select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;
                                                                  QUERY PLAN

------------------------------------------------------------------------------------------------------------------------
-----------------------
 Sort  (cost=272469.15..274823.18 rows=941611 width=291) (actual time=3018.428..3021.111 rows=26204 loops=1)
   Sort Key: (count(u.tokuten))
   Sort Method:  quicksort  Memory: 3985kB
   ->  GroupAggregate  (cost=157852.62..179038.87 rows=941611 width=291) (actual time=2432.474..3006.945 rows=26204 loop
s=1)
         ->  Sort  (cost=157852.62..160206.65 rows=941611 width=291) (actual time=2432.464..2570.835 rows=946356 loops=1
)
               Sort Key: u.uid, myuser.hitokoto
               Sort Method:  quicksort  Memory: 277269kB
               ->  Hash Join  (cost=3270.99..64422.35 rows=941611 width=291) (actual time=47.316..816.311 rows=946356 lo
ops=1)
                     Hash Cond: (u.uid = myuser.uid)
                     ->  Seq Scan on userreview u  (cost=0.00..39965.11 rows=941611 width=12) (actual time=0.009..202.23
3 rows=946356 loops=1)
                     ->  Hash  (cost=2455.44..2455.44 rows=65244 width=288) (actual time=47.250..47.250 rows=65244 loops
=1)
                           Buckets: 8192  Batches: 1  Memory Usage: 3744kB
                           ->  Seq Scan on myuser  (cost=0.00..2455.44 rows=65244 width=288) (actual time=0.020..27.486
rows=65244 loops=1)
 Total runtime: 3030.406 ms

実行時間が半分以下になりました。
続いて9.3の実行結果です。

# EXPLAIN ANALYZE select u.uid,COUNT(u.tokuten),m.hitokoto FROM myuserview m ,userreview u WHERE m.uid = u.uid GROUP BY u.uid,m.hitokoto ORDER BY COUNT(u.tokuten) DESC;
                                                                  QUERY PLAN

------------------------------------------------------------------------------------------------------------------------
-----------------------
 Sort  (cost=269816.36..272183.87 rows=947004 width=290) (actual time=92436.600..92440.232 rows=26224 loops=1)
   Sort Key: (count(u.tokuten))
   Sort Method: quicksort  Memory: 3986kB
   ->  GroupAggregate  (cost=156871.88..175811.96 rows=947004 width=290) (actual time=91778.152..92427.445 rows=26224 lo
ops=1)
         ->  Sort  (cost=156871.88..159239.39 rows=947004 width=290) (actual time=91778.083..91913.285 rows=947105 loops
=1)
               Sort Key: u.uid, myuser.hitokoto
               Sort Method: quicksort  Memory: 276610kB
               ->  Hash Join  (cost=3253.85..62867.48 rows=947004 width=290) (actual time=45.518..925.404 rows=947105 lo
ops=1)
                     Hash Cond: (u.uid = myuser.uid)
                     ->  Seq Scan on userreview u  (cost=0.00..38306.04 rows=947004 width=12) (actual time=0.012..329.67
0 rows=947105 loops=1)
                     ->  Hash  (cost=2437.82..2437.82 rows=65282 width=287) (actual time=45.460..45.460 rows=65291 loops
=1)
                           Buckets: 8192  Batches: 1  Memory Usage: 3746kB
                           ->  Seq Scan on myuser  (cost=0.00..2437.82 rows=65282 width=287) (actual time=0.011..27.265
rows=65291 loops=1)
 Total runtime: 92451.731 ms
(14 行)

実行時間が20%になりましたが、それでも9.0での実行に遠く及びません。

この後
  • 別サーバーにPostgreSQL9.0をインストールしてSQLを実行し、遅いことを確認 → PostgreSQL9.3は悪くない
  • GROUP BY句を使うと遅いのか → 遅くない
  • GROUP BY句にインデックスをはっていない列があると遅いのか → 遅くない
  • GROUP BY句に数字だけの列だと速いか → 速い
等々を試して、
  • 日本語を含む列をGROUP BY句にいれると遅いこと
を発見し
  • GROUP BY句はソートが実行される
ことをドキュメントで発見し、
  • そういえば元々はどうやってPostgreSQLをインストールしてたんだっけ?
と思って手順書を確認し、もともとロケールはCで設定していたことに気がつき(ここまではpostgresql.confしか見ていませんでした…)
  • ロケールをCにしたのは、ソートが関係していたんだったような…あー、もしかして、UTF8だとソートが遅い?
と思って、ロケールをCにしたら、元々のスピードになりました。