LATERALを使ってみよう
笠原 辰仁
本記事は2015年のPostgreSQL Advent Calendarの 12/3 の記事です。PostgreSQL9.3でサポートされたLATERALについての解説と使いどころについて紹介します。
はじめに
PostgreSQLは幅広くSQL標準(ではないものも含む)の句や構文をサポートしており、それが製品の特徴の一つでもあります。PostgreSQL8.4でサポートされたWindow関数やWITH句はその引き合いに出されることが多いです。
さて、PostgreSQL9.3からはLATERALという句がサポートされています。やや地味で使われることが少ないため、ご存知の方は少ないかもしれません。しかし、LATERAL句は、使いどころによっては非常に強力な武器になります。
注意
本記事ではテーブル定義や実行計画等を記載している箇所がありますが、幅の表示上、見やすいように改行している箇所があります。そのため、実際の表示とは異なる部分での改行が入っていたりします。ご了承ください。
LATERALとは
LATERALはSQL標準で規定されている句です。PostgreSQL以外のRDBMSでもLATERAL句、もしくはAPPLY句という形でサポートしている製品は多いです。この句は基本的にサブクエリを修飾する形で使われます。PostgreSQLでは、オンラインドキュメントにもあるように、サブクエリだけでなく単体の関数にも適用できます。LATERALの効果を見てみましょう。以下のクエリはそのままではシンタックスエラーとなります。(PostgreSQLだけでなく、他のRDBMSでも同様でしょう)
=> SELECT t1.id, t1.name, ss.value FROM t1 , (SELECT value FROM t2 WHERE t2.id = t1.id)ss; ERROR: invalid reference to FROM-clause entry for table "t1" LINE 1: ...alue FROM t1 , (SELECT value FROM t2 WHERE t2.id = t1.id)ss;
理由は、サブクエリssの中でt1.idを参照しているからです。FROM句のサブクエリやリレーションはそれぞれ個別に評価されるため、FROM句にあるサブクエリの中から他のサブクエリやリレーションの列を参照することはできません。そのため、サブクエリの結果をその外側で参照しなければいけません。したがって、前述のクエリは次のように記述します。
=> SELECT t1.id, t1.name, ss.value FROM t1,
(SELECT value FROM t2)ss WHERE ss.id = t1.id;
あるいは、サブクエリやリレーションの結果を別のサブクエリで参照するならばWITH句を使うことが多いかもしれません。WITH句を使うと次のように記述することもできます。
=> WITH s AS (SELECT id, name FROM t1)
SELECT s.id, s.name, t2.value FROM s, t2 WHERE s.id = t2.id;
他にもいくつか書き方はあるかもしれません。このようなケースは、LATERALを使うことで、次のようにシンプルに記述することができます。
=> SELECT t1.id, t1.name, ss.value FROM t1 ,
LATERAL (SELECT value FROM t2 WHERE t2.id = t1.id)ss;
LATERALはこのようにサブクエリ(あるいは関数)の先頭に付与します。付与されたサブクエリ等は他のFROM句の評価の後に評価されます。そのため、他のサブクエリの結果をサブクエリの中から参照することができます。
内部的な挙動としては、まずLATERAL以外のサブクエリやリレーションが評価され結果が生成されます。その後、その結果の1行ごとにLATERAL内のサブクエリが評価されます。いわば、非LATERALをouter、LATERALをinnerとしたNestLoop Joinのような処理となります。 前述のクエリで例えると、まず t1 が評価(表スキャンなしいは索引スキャン)され、次にt1から得られた1行ごとに、LATERAL内のサブクエリが順次実行されます。プロシージャ的に記述すると
FOR r IN SELECT * FROM t1 LOOP
SELECT value FROM t2 WHERE t2.id = r.id
END LOOP;
のような処理イメージとなります。
このLATERALの特性を活かすと、次のようなクエリチューニングにも使えます。
LATERALによるチューニング
あるノード(あるいは機器など)の管理テーブル"node"と、各ノードの監視情報を蓄積しているテーブル"node_mon"の2つがあるとします。(以降の処理は全て開発中のPostgreSQL9.6develで試しています)
=# \d node
Table "public.node"
Column | Type | Modifiers
--------+--------+-----------
id | bigint | not null
name | text |
Indexes:
"node_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "node_mon" CONSTRAINT "node_id_fky"
FOREIGN KEY (id) REFERENCES node(id)
=# \d node_mon
Unlogged table "public.node_mon"
Column | Type | Modifiers
----------+-----------------------------+-----------
id | bigint |
usage | integer |
mon_time | timestamp without time zone |
Indexes:
"idx_id_usage" btree (id, usage)
Foreign-key constraints:
"node_id_fky" FOREIGN KEY (id) REFERENCES node(id)
この"node"テーブルと"node_mon"テーブルから、各ノード(nameを添えて)のusageの最大、最小を求めたいとします。まず普通に思いつくのは以下の様なSQLでしょう。
=# EXPLAIN (COSTS off, ANALYZE on)
SELECT n.id, n.name, max(m.usage), min(m.usage)
FROM node n LEFT JOIN node_mon m ON n.id = m.id GROUP BY n.id;
QUERY PLAN
-------------------------------------------------------------------------
HashAggregate (actual time=614.237..614.258 rows=100 loops=1)
Group Key: n.id
-> Hash Right Join (actual time=0.093..374.362 rows=800000 loops=1)
Hash Cond: (m.id = n.id)
-> Seq Scan on node_mon m
(actual time=0.011..97.393 rows=800000 loops=1)
-> Hash (actual time=0.069..0.069 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 15kB
-> Seq Scan on node n
(actual time=0.015..0.031 rows=100 loops=1)
Planning time: 0.444 ms
Execution time: 614.349 ms
(10 rows)
"node"と"node_mon"を結合し、GROUP BY で"id"ごとの集計を行うシンプルなクエリです。このクエリの性能改善はできないものでしょうか？実行計画から、"node"テーブルには100件、"node_mon"テーブルには80万件のレコードが入っていることが分かります。またこのクエリでは、HashJoinとその後のHash Aggregateでかなりの時間がかかっています。"node_mon"テーブルへの全件スキャンも今後の"node_mon"テーブルの増大を考えるとあまり好ましくありません。
ここで、"node_mon"テーブルに付与されているインデックスに着目します。idとusageに付与されているマルチカラムインデックスです。そのため、idごとのusageの最大最小値は、それぞれ1回のインデックススキャンで軽量に取得できます。以下がその例です。
=# EXPLAIN (COSTS off, ANALYZE on)
SELECT min(usage), max(usage) FROM node_mon WHERE id = 1;
QUERY PLAN
------------------------------------------------------------------
Result (actual time=0.077..0.077 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (actual time=0.047..0.047 rows=1 loops=1)
-> Index Only Scan
using idx_id_usage on node_mon
(actual time=0.045..0.045 rows=1 loops=1)
Index Cond: ((id = 1) AND (usage IS NOT NULL))
Heap Fetches: 0
InitPlan 2 (returns $1)
-> Limit (actual time=0.024..0.024 rows=1 loops=1)
-> Index Only Scan Backward
using idx_id_usage on node_mon node_mon_1
(actual time=0.024..0.024 rows=1 loops=1)
Index Cond: ((id = 1) AND (usage IS NOT NULL))
Heap Fetches: 0
Planning time: 0.288 ms
Execution time: 0.144 ms
「id=1」の最大値と最小値が(Planningの時間を除いて)0.14msほどで取得できました。"node"が保持しているのは100件ですから、"node"テーブルからidとnameを引きつつ、上記のクエリをループで100行分を回せば、単純に考えると15ms程度までは改善できそうな気がします。プロシージャを用意する、あるいはアプリでループを回す方法などが考えられますが、一つのクエリでこれを手軽に実施する方法はないでしょうか？そこでLATERALの出番です。前述のとおりLATERALを使うと、LATERAL外のサブクエリやリレーション結果を得た後に、1行づつLATERAL内のサブクエリが評価されますので、まさに今回のようなループ的な(あるいはforeach的な)動きを意識したなクエリを記述するにはうってつけです。LATERALを使うと以下のようなクエリと実行計画になり、性能が614msから3.4msへ200倍近く向上しました。
=# EXPLAIN (COSTS off, ANALYZE on)
SELECT n.id, n.name, t.max, t.min FROM node n,
LATERAL(SELECT max(usage) as max, min(usage) as min
FROM node_mon m WHERE m.id = n.id)t;
QUERY PLAN
--------------------------------------------------------------------------
Nested Loop (actual time=0.172..3.198 rows=100 loops=1)
-> Seq Scan on node n (actual time=0.017..0.046 rows=100 loops=1)
-> Result (actual time=0.030..0.030 rows=1 loops=100)
InitPlan 1 (returns $1)
-> Limit (actual time=0.014..0.014 rows=1 loops=100)
-> Index Only Scan Backward
using idx_id_usage on node_mon m
(actual time=0.013..0.013 rows=1 loops=100)
Index Cond: ((id = $0) AND (usage IS NOT NULL))
Heap Fetches: 0
InitPlan 2 (returns $2)
-> Limit (actual time=0.014..0.014 rows=1 loops=100)
-> Index Only Scan
using idx_id_usage on node_mon m_1
(actual time=0.013..0.013 rows=1 loops=100)
Index Cond: ((id = $0) AND (usage IS NOT NULL))
Heap Fetches: 0
Planning time: 0.416 ms
Execution time: 3.288 ms
(15 rows)
どうでしょうか？元々のGROUP BYを使った集計よりもずっと速く、また比較的簡素なクエリで実現できています。LATERALをうまく使うことで、今まで複雑な形でしか記述できなかったクエリがよりシンプルになり、場合によっては非常に高速な処理になります。
補足:LATERAL以外の方法
前述のクエリですが、LATERALを使えない場合は他にどのような書き方ができるでしょうか？PostgreSQLでは、SELECT列に「1行だけ返却することが確約できるクエリ」に限り記述可能です。そのため、例えば以下のようなクエリを書くことでLATERALと同様の実行計画を得ることができます。
=# EXPLAIN (COSTS off, ANALYZE on)
SELECT n.id, n.name,
(SELECT max(usage) FROM node_mon WHERE id = n.id) as max,
(SELECT min(usage) FROM node_mon WHERE id = n.id) as min
FROM node n;
QUERY PLAN
--------------------------------------------------------------------------
Seq Scan on node n (actual time=0.095..3.233 rows=100 loops=1)
SubPlan 2
-> Result (actual time=0.014..0.014 rows=1 loops=100)
InitPlan 1 (returns $1)
-> Limit (actual time=0.013..0.013 rows=1 loops=100)
-> Index Only Scan
Backward using idx_id_usage on node_mon
(actual time=0.012..0.012 rows=1 loops=100)
Index Cond: ((id = n.id) AND (usage IS NOT NULL))
Heap Fetches: 0
SubPlan 4
-> Result (actual time=0.015..0.016 rows=1 loops=100)
InitPlan 3 (returns $3)
-> Limit (actual time=0.014..0.014 rows=1 loops=100)
-> Index Only Scan
using idx_id_usage on node_mon node_mon_1
(actual time=0.013..0.013 rows=1 loops=100)
Index Cond: ((id = n.id) AND (usage IS NOT NULL))
Heap Fetches: 0
Planning time: 0.370 ms
Execution time: 3.350 ms
(17 rows)
SELECT列へサブクエリを記述することは、可読性の悪化につながりますし、思わぬエラーの温床になりやすいことからあまり推奨はできません。しかし、いざという場合のチューニングに役立つこともありますので、覚えておくと良いかもしれません。
終わりに
いかがでしたでしょうか？Window関数やWITH句ほどのインパクトはありませんが、おそらくこの句が有効に働く場面は少なからずあると思います。ぜひ、今回のようなクエリを見た場合には、LATERAL句を使ってさらに効率的な記述ができないかを検討してみてください。本記事が何かしらのヒントになれば幸いです。
PostgreSQL Advent Calendar 2015、明日Day4の担当はsayamada氏です。