PostgreSQL9.3でサポートされたLATERALについての解説と使いどころについて紹介します。はじめにPostgreSQLは幅広くSQL標準(ではないものも含む)の句や構文をサポートしており、それが製品の特徴の一つでもあります。PostgreSQL8.4でサポートされたWindow関数やWITH句はその引き合いに出されることが多いです。PostgreSQL9.3からはLATERALという句がサポートされています。地味で使われることが少ないため、ご存知の方は少ないかもしれません。LATERAL句は、使いどころによっては非常に強力な武器になります。本記事ではテーブル定義や実行計画等を記載している箇所がありますが、幅の表示上、見やすいように改行している箇所があります。そのため、実際の表示とは異なる部分での改行が入っていたりします。LATERALとはLATERALはSQL標準で規定されている句です。PostgreSQL以外のRDBMSでもLATERAL句、もしくはAPPLY句という形でサポートしている製品は多いです。この句は基本的にサブクエリを修飾する形で使われます。PostgreSQLでは、オンラインドキュメントにもあるように、サブクエリだけでなく単体の関数にも適用できます。LATERALの効果を見てみましょう。以下のクエリはそのままではシンタックスエラーとなります。t1.id)ss;理由は、サブクエリssの中でt1.idを参照しているからです。FROM句のサブクエリやリレーションはそれぞれ個別に評価されるため、FROM句にあるサブクエリの中から他のサブクエリやリレーションの列を参照することはできません。そのため、サブクエリの結果をその外側で参照しなければいけません。したがって、前述のクエリは次のように記述します。t1.id;あるいは、サブクエリやリレーションの結果を別のサブクエリで参照するならばWITH句を使うことが多いかもしれません。WITH句を使うと次のように記述することもできます。t2.id;他にもいくつか書き方はあるかもしれません。このようなケースは、LATERALを使うことで、次のようにシンプルに記述することができます。t1.id)ss;LATERALはこのようにサブクエリ(あるいは関数)の先頭に付与します。付与されたサブクエリ等は他のFROM句の評価の後に評価されます。そのため、他のサブクエリの結果をサブクエリの中から参照することができます。内部的な挙動としては、まずLATERAL以外のサブクエリやリレーションが評価され結果が生成されます。その後、その結果の1行ごとにLATERAL内のサブクエリが評価されます。Joinのような処理となります。評価(表スキャンなしいは索引スキャン)され、次にt1から得られた1行ごとに、LATERAL内のサブクエリが順次実行されます。LOOP;のような処理イメージとなります。このLATERALの特性を活かすと、次のようなクエリチューニングにも使えます。LATERALによるチューニングあるノード(あるいは機器など)の管理テーブル"node"と、各ノードの監視情報を蓄積しているテーブル"node_mon"の2つがあるとします。node(id)この"node"テーブルと"node_mon"テーブルから、各ノード(nameを添えて)のusageの最大、最小を求めたいとします。普通に思いつくのは以下の様なSQLでしょう。"id"ごとの集計を行うシンプルなクエリです。このクエリの性能改善はできないものでしょうか?実行計画から、"node"テーブルには100件、"node_mon"テーブルには80万件のレコードが入っていることが分かります。Aggregateでかなりの時間がかかっています。"node_mon"テーブルへの全件スキャンも今後の"node_mon"テーブルの増大を考えるとあまり好ましくありません。ここで、"node_mon"テーブルに付与されているインデックスに着目します。idとusageに付与されているマルチカラムインデックスです。そのため、idごとのusageの最大最小値は、それぞれ1回のインデックススキャンで軽量に取得できます。ms「id=1」の最大値と最小値が(Planningの時間を除いて)0.14msほどで取得できました。"node"が保持しているのは100件ですから、"node"テーブルからidとnameを引きつつ、上記のクエリをループで100行分を回せば、単純に考えると15ms程度までは改善できそうな気がします。プロシージャを用意する、あるいはアプリでループを回す方法などが考えられますが、一つのクエリでこれを手軽に実施する方法はないでしょうか?そこでLATERALの出番です。前述のとおりLATERALを使うと、LATERAL外のサブクエリやリレーション結果を得た後に、1行づつLATERAL内のサブクエリが評価されますので、まさに今回のようなループ的な(あるいはforeach的な)動きを意識したなクエリを記述するにはうってつけです。LATERALを使うと以下のようなクエリと実行計画になり、性能が614msから3.4msへ200倍近く向上しました。BYを使った集計よりもずっと速く、また比較的簡素なクエリで実現できています。LATERALをうまく使うことで、今まで複雑な形でしか記述できなかったクエリがよりシンプルになり、場合によっては非常に高速な処理になります。前述のクエリですが、LATERALを使えない場合は他にどのような書き方ができるでしょうか?PostgreSQLでは、SELECT列に「1行だけ返却することが確約できるクエリ」に限り記述可能です。そのため、例えば以下のようなクエリを書くことでLATERALと同様の実行計画を得ることができます。rows)SELECT列へサブクエリを記述することは、可読性の悪化につながりますし、思わぬエラーの温床になりやすいことからあまり推奨はできません。いざという場合のチューニングに役立つこともありますので、覚えておくと良いかもしれません。終わりにいかがでしたでしょうか?Window関数やWITH句ほどのインパクトはありませんが、おそらくこの句が有効に働く場面は少なからずあると思います。今回のようなクエリを見た場合には、LATERAL句を使ってさらに効率的な記述ができないかを検討してみてください。本記事が何かしらのヒントになれば幸いです。2015、明日Day4の担当はsayamada氏です。Groupこのウェブサイトに掲載されている個々の記事の著作権は、それぞれの著作権者に帰属します。