Studio ODIN

積ん読 2012/12/30

• MD5やSHAの代替として利用可能な新たなハッシュ化技術「BLAKE2」登場 - SourceForge.JP Magazine (2012/12/26)
• 構造化『並列』プログラミング - どらの日記 (2012/12/23)
• これは快適！Mac OS X上でWinのIE6/7/8/9/10など、さまざまなブラウザの確認ができるアプリ -Sauce - コリス (2012/12/26)
• DNS関連の便利ツールがこれでもか！と詰まった『ViewDNS.info』 - IDEA*IDEA (2012/12/28)

UNION が含まれるビューとインデックス

会社で聞かれた 「UNION が含まれるビューがあるんだけど、インデックス使ってくれる？」という 質問に対して 「データベースがそっちの方が効率が良いと判断したら使ってくれるし、実行計画を取ってみれば？」という 回答をしたんですが、念のため試してみます。

まず、テスト用のテーブルにデータを登録します。

INSERT INTO table1 (f1)
  SELECT generate_series FROM generate_series(1, 10000);

INSERT INTO table2 (f1)
  SELECT generate_series FROM generate_series(1, 10000);

generate_series 関数は、便利ですね。

まずお試しに単純な SQL で実行計画を取得。 もちろん事前にテーブルを Analyze しておきます。

EXPLAIN
  SELECT * FROM table1
  WHERE f1 = 1

↓結果

Index Scan using table1_pkey on table1  (cost=0.00..8.27 rows=1 width=8)
  Index Cond: (f1 = 1)

インデックス table1_pkey が使用されています。

次に副問い合わせで UNION ALL して実行計画を取得。

EXPLAIN
  SELECT *
  FROM (SELECT * FROM table1
        UNION ALL
        SELECT * FROM table2) AS t1
  WHERE f1 = 1

↓結果

Result  (cost=0.00..16.54 rows=2 width=8)
  ->  Append  (cost=0.00..16.54 rows=2 width=8)
        ->  Index Scan using table1_pkey on table1  (cost=0.00..8.27 rows=1 width=8)
              Index Cond: (f1 = 1)
        ->  Index Scan using table2_pkey on table2  (cost=0.00..8.27 rows=1 width=8)
              Index Cond: (f1 = 1)

これもちゃんと、インデックス table1_pkey と table2_pkey が使用されています。

ちなみに UNION ALL じゃなくて UNION の場合。

EXPLAIN
  SELECT *
  FROM (SELECT * FROM table1
        UNION
        SELECT * FROM table2) AS t1
  WHERE f1 = 1

↓結果

Unique  (cost=16.57..16.58 rows=2 width=8)
  ->  Sort  (cost=16.57..16.57 rows=2 width=8)
        Sort Key: table1.f1, table1.f2
        ->  Append  (cost=0.00..16.56 rows=2 width=8)
              ->  Index Scan using table1_pkey on table1  (cost=0.00..8.27 rows=1 width=8)
                    Index Cond: (f1 = 1)
              ->  Index Scan using table2_pkey on table2  (cost=0.00..8.27 rows=1 width=8)
                    Index Cond: (f1 = 1)

こちらもインデックスは使ってくれます。

念のため、ビューを作成して試してみます。

CREATE VIEW view1 AS
  SELECT *
  FROM (SELECT * FROM table1
        UNION ALL
        SELECT * FROM table2) AS t1

ビューに対して実行計画を取得。

EXPLAIN
  SELECT * FROM view1
  WHERE f1 = 1

↓結果

Result  (cost=0.00..16.54 rows=2 width=8)
  ->  Append  (cost=0.00..16.54 rows=2 width=8)
        ->  Index Scan using table1_pkey on table1  (cost=0.00..8.27 rows=1 width=8)
              Index Cond: (f1 = 1)
        ->  Index Scan using table2_pkey on table2  (cost=0.00..8.27 rows=1 width=8)
              Index Cond: (f1 = 1)

結果は副問い合わせと同じでした。

こういうのは、自分の手で試しておくと安心ですね。