PostgreSQL Advent Calendar 2015 の19日目です。

昨日は、 PostgreSQL 9.5 からの新機能である ROLLUP について記事でした。
私はよくクロス集計表を作ることが多いので、CUBE について書いてみようと思います。

昨日 PostgreSQL 9.5 RC1 がリリースされましたが、
docker が便利すぎるので、9.5 Beta2 で試してみました。

準備

この記事に習って Docker Tools をインストール。その後 Docker Quickstart Terminal.app を実行するだけで、すぐに docker pull, run などできるようになります。yancyaさんの記事にある通り、pull して run して bash を実行。

docker pull postgres:9.5
docker run --name postgres_95beta -d postgres:9.5
docker exec -i -t postgres_95beta bash

su postgres
psql

postgres=# select version() ;

                                           version                                           
---------------------------------------------------------------------------------------------
 PostgreSQL 9.5beta2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 row)

CUBE とは

ROLLUP が列挙された要素を左側から絞り込んだ組み合わせの集計を計算するのに対して、
CUBE は、列挙された要素の全ての組み合わせについて集計を行います。

CUBE を使ってみる

商品の種類ごと、色ごと、およびその組み合わせの在庫の合計を例にしてみます。

with samples(id, product, stock, color) as (
    values (1, 'コート', 150, 'あか'),
           (2, 'コート', 100, 'あお'),
           (3, 'コート', 200, 'しろ'),
           (4, 'シャツ', 100, 'しろ'),
           (5, 'シャツ', 200, 'しろ')
)
select
    product
    , color
    , sum(stock) as stock_sum
from samples
group by cube (1, 2)
;

この場合だと (product, color), (product, null), (null, color), (null, null) の組み合わせの
それぞれの sum(stock) が計算されます。

product | color | stock_sum
---------+-------+-----------
コート  | あお  |       100
コート  | あか  |       150
コート  | しろ  |       200
コート  |       |       450
シャツ  | しろ  |       300
シャツ  |       |       300
        | あお  |       100
        | あか  |       150
        | しろ  |       500
        |       |       750

CUBE を使わずに書いてみる

考えられる組み合わせをすべて書いて、union すれば同じ結果が得られそうです。

with samples(id, product, stock, color) as (
    values (1, 'コート', 150, 'あか'),
           (2, 'コート', 100, 'あお'),
           (3, 'コート', 200, 'しろ'),
           (4, 'シャツ', 100, 'しろ'),
           (5, 'シャツ', 200, 'しろ')
)
select -- (product, color)
      product
      , color
      , sum(stock) as stock_sum
from samples
group by 1, 2

union

select -- (product, null)
      product
      , null as color
      , sum(stock) as stock_sum
from samples
group by 1, 2

union

select -- (null, color)
      null as product
      , color
      , sum(stock) as stock_sum
from samples
group by 1, 2

union

select -- (null, null)
      null as product
      , null as color
      , sum(stock) as stock_sum
from samples
group by 1, 2
;

実行計画を見てみる

explain analyze
CUBE を使った場合、Scan on samples は1回だけ
- UNION の場合、組み合わせごとに走るので4回
実行時間も結構違う（けど、複数回実行した平均を見るべきですね。データ量も変えてみたい）

CUBE

                                              QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
GroupAggregate  (cost=0.22..0.69 rows=16 width=68) (actual time=0.064..0.086 rows=10 loops=1)
  Group Key: samples.product, samples.color
  Group Key: samples.product
  Group Key: ()
  Sort Key: samples.color
    Group Key: samples.color
  CTE samples
    ->  Values Scan on "*VALUES*"  (cost=0.00..0.06 rows=5 width=72) (actual time=0.002..0.007 rows=5 loops=1)
  ->  Sort  (cost=0.16..0.17 rows=5 width=68) (actual time=0.052..0.052 rows=5 loops=1)
        Sort Key: samples.product, samples.color
        Sort Method: quicksort  Memory: 25kB
        ->  CTE Scan on samples  (cost=0.00..0.10 rows=5 width=68) (actual time=0.010..0.020 rows=5 loops=1)
Planning time: 0.142 ms
Execution time: 0.151 ms
(14 rows)

UNION

                                                          QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
HashAggregate  (cost=1.05..1.21 rows=16 width=44) (actual time=0.074..0.077 rows=10 loops=1)
  Group Key: samples.product, samples.color, (sum(samples.stock))
  CTE samples
    ->  Values Scan on "*VALUES*"  (cost=0.00..0.06 rows=5 width=72) (actual time=0.006..0.011 rows=5 loops=1)
  ->  Append  (cost=0.14..0.87 rows=16 width=44) (actual time=0.031..0.063 rows=10 loops=1)
        ->  HashAggregate  (cost=0.14..0.19 rows=5 width=68) (actual time=0.030..0.031 rows=4 loops=1)
              Group Key: samples.product, samples.color
              ->  CTE Scan on samples  (cost=0.00..0.10 rows=5 width=68) (actual time=0.011..0.017 rows=5 loops=1)
        ->  Subquery Scan on "*SELECT* 2"  (cost=0.14..0.24 rows=5 width=36) (actual time=0.009..0.011 rows=2 loops=1)
              ->  HashAggregate  (cost=0.14..0.19 rows=5 width=36) (actual time=0.008..0.010 rows=2 loops=1)
                    Group Key: samples_1.product, NULL::text
                    ->  CTE Scan on samples samples_1  (cost=0.00..0.10 rows=5 width=36) (actual time=0.001..0.003 rows=5 loops=1)
        ->  Subquery Scan on "*SELECT* 3"  (cost=0.14..0.24 rows=5 width=36) (actual time=0.007..0.009 rows=3 loops=1)
              ->  HashAggregate  (cost=0.14..0.19 rows=5 width=36) (actual time=0.006..0.006 rows=3 loops=1)
                    Group Key: NULL::text, samples_2.color
                    ->  CTE Scan on samples samples_2  (cost=0.00..0.10 rows=5 width=36) (actual time=0.001..0.001 rows=5 loops=1)
        ->  Subquery Scan on "*SELECT* 4"  (cost=0.00..0.16 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)
              ->  GroupAggregate  (cost=0.00..0.15 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)
                    Group Key: NULL::text, NULL::text
                    ->  CTE Scan on samples samples_3  (cost=0.00..0.10 rows=5 width=4) (actual time=0.001..0.002 rows=5 loops=1)
Planning time: 0.217 ms
Execution time: 0.239 ms
(22 rows)

まとめ

ROLLUP を使ってみた記事と構成がそっくりそのままで、恐縮です。
クロス集計表を作る時は、値に紐づく属性（軸）が増えれば増えるほど煩雑になるので、
CUBE を使うことで、だいぶ書かないといけないSQLを抑えられそうです。

参考

PostgreSQL 9.5 新機能紹介

言い訳

plmruby を 9.5 RC1 で試してみたかったのですが、
環境作ったりで思いのほか時間がかかってしまい、CUBE の紹介と利用例という内容になってしまいました。

PostgreSQL 9.5 の group by cube を試してみた

準備