postgres 実行計画変わる 4

コロナ禍により様々なものが変わろうとしています。人の働き方やビジネスプロセス、ビジネスモデル、そして多様なステークホルダーにどのような共通価値を提供するか。, 今、ニューノーマルの時代に向けて、想像力を働かせ、ビジネス全体を“Reimagine（再構想）”することが重要です。業種業界を問わず、この危機を乗り切ることができるよう、富士通はソリューションを提供していきます。, 富士通は強みであるAIやIoT、5G、セキュリティなどのテクノロジーと、強固な顧客基盤に支えられた業種業務ノウハウを活かして、お客様、社会が求める価値を実現するデジタルトランスフォーメーション（DX）を追求していきます。, 「働く」ということだけでなく、「仕事」と「生活」をトータルにシフトし、Well-beingを実現します。, 富士通が描く5G WORLD。ニューラルな5Gネットワークの創る4つのREALで、富士通は社会を変えていきます。, ICTインフラ全体の最適化で、デジタルトランスフォーメーション（DX）を支えるインフラを実現, オンプレミスからパブリッククラウドまで、お客様のビジネス価値最大化に向けた最適なICT環境をワンストップで提供します。, 世界に広がる富士通グループ約300社での対策と運用で得たノウハウで、情報を「守る」だけでなく「利活用」する環境を実現。お客様のデジタルトランスフォーメーション（DX）を加速します。, データ活用によりお客様とのデジタル接点を高度化。システム化に向けたコンサルティングからデータ分析、基幹システム連携、運用保守まで一気通貫で支援します。, 様々な業種・業態のお客様との取り組みのなかで得たノウハウに基づき、お客様のデジタルトランスフォーメーションをご支援します。, 昨今の人々の働き方や考え方、価値感の急激な変化に対応し、持続的な社会の実現を目指します。生活者の視点から社会に求められることを汲み取り、今まで接点がなかったモノ・コトをつなげ、生活者へ新たな価値を提供し、「Society5.0」の世界を実現します。, 最新テクノロジーとデリバリーモデルにより、ハイブリッドIT環境の全体最適化を実現するソリューション群。, お客様のビジネスに最適な、ハイブリッドIT環境の構築や運用サービスの提供により、ITシステムにおける運用負荷軽減、TCO削減、納期短縮を実現します。, 当社独自の高い技術力や幅広い業種ナレッジ、経験豊富なAI専門人材を強みに、お客様のビジネスに合わせたAI活用をご提案します。, FUJITSU Quantum-inspired Computing Digital Annealer（デジタルアニーラ）, 社会の様々な分野で存在する組合せ最適化問題を瞬時に解く新しいアーキテクチャのコンピュータです。, 競技のレベルアップから新しい観戦スタイルまで、3つのソリューション「スポーツのセンシング / AI」「スポーツデジタルマーケティング」「スタジアム / アリーナソリューション」で、スポーツ業界の発展に貢献します。, 富士通のビジネスやテクノロジー、社員インタビューなどをご紹介しています。・統計情報が古い場合「ANALYZE」文で最新の統計情報を取得する, 最後に統計情報を取得したいテーブルの前に、「ANALYZE」と記載することで取得できます。, ただし、PostgreSQLでは、autovacuum機能による自動VACUUM時に、 PostgreSQLでは「EXPLAIN」文を使用します。 EXPLAIN select * from address inner join city ON address.city_id=city.city_id ORDER BY city.city_id ASC; (c) 2020 Casley Consulting, Inc. All Rights Reserved. 3行目：結合をしています 7行目：city(内側)テーブルのデータを取り出しています各種インターンシップやイベントもこちらからご確認ください。, pg_hint_planでヒント句を設定して実行計画を制御するチューニング方法について解説します。, PostgreSQLでは、プランナ（オプティマイザ）が入力されたSQL文をもとに統計情報を参照して、最も速くてコストの低いと予想される方法を選択し、実行計画を作成します。しかし、必ずしもプランナ（オプティマイザ）が最適な実行計画を作成するとは限りません。例えば、大量の更新クエリ―などで統計情報の最新化が間に合わない場合や、刻々と統計情報が変化する場合に、プランナ（オプティマイザ）が作成する実行計画が不安定になることがあります。基幹系の業務システムでは、性能の向上よりも性能の安定化の方が重要であり、実行計画の変化を避けたい場合もあります。最後にまとめです。, ・実行計画とは、クエリ実行にあたっての手順書のことであるまずは、psqlでテスト用のデータを作成します。次のSQLでシンプルなテーブルを作成します。 postgres=# CREATE TABLE test_tbl (col1 int PRIMARY KEY,col2 varchar(20)); CREATE TABLE 続いてテストデータを挿入するファンクションを作成します。先ほど作成したテーブルに10000件のデータを挿入するファンクションです。 postgres=# CREATE FUNCTION instest1() returns int as postgres-# ' postgres'# DECLARE postgres'# i int; postgres'# BEGIN postgres'# FOR i IN 1..10000 loop postgres'# INS… テーブルに付与されているインデックスのみをスキャンし、実テーブルはスキャンしないIndex Scanなどが存在します。. 「EXPLAIN ANALYZE」で取得したrowの値は実際に実行結果として取得した行数なので、 3.1.2.結合系ノード内側テーブルの結合キーでハッシュを作成し、ハッシュと外側テーブルの結合キーで一致する行を結合するHash Joinなどが存在します。, 【各行の説明】 empテーブルのデータから500件削除します。ANALYZEコマンド（SQLコマンド）で統計情報を更新すると、empテーブルに対して「Seq Scan」が選択されます。続けてempテーブルのデータに2000件追加します。統計情報を更新せずに対象SQLの実行計画を確認します。ここではデータを大量追加した直後にデータ検索要求があり、ANALYZEコマンド（SQLコマンド）による統計情報の更新が間にあわなかったことを想定しています。, 「2.2 統計情報をバックアップする」でバックアップした「Bitmap Scan」の統計情報が選択されるように、リストア機能で復元して固定します。今回のブログでは、PostgreSQLの実行計画について紹介致しました。 3.1.1.テーブルスキャンノード困った点表題のとおりなのですが、何かの条件を期に急激にパフォーマンスが劣化（100倍ほど遅くなる）ことがあります。前後でデータ数には変化がなく、関連レコードにupdateを走らせたあとに発生しやすい（気がする）といったくらいです。環境以下環境でAPIサーバーを作成していま ●実行したSQL その対策として、クエリ―にヒント句を設定してテーブルスキャン方法や結合方法などのアクセス方法を明示的に指示することで、プランナ（オプティマイザ）が出力する実行計画を制御することができます。それにより、プランナ（オプティマイザ）が安定した実行計画を出力するようになります。代表例として、テーブル全体を順番にスキャンするSeq Scanや、・「レスポンスが遅いと感じた時」には、実行計画を決定するための統計情報が古い可能性があるため、 Execution time：実行時間を表す, EXPLAINと、EXPLAIN ANALYZEの違いはプランナが見積もった値か、実際の実行時に発生した値かです。, 「FORMAT」オプションを付与することで、「EXPLAIN」文の出力フォーマットを変更できます。結合系ノードは、複数のテーブルを結合する役割のノードです。ユーザの思惑や、仕様上の特性などについては考慮してくれません。「EXPLAIN」と「EXPLAIN ANALYZE」で確認するそういった点から、想定外の実行計画となっていないかを確認するために見る必要があります。, このように、実行計画を見る機会はあまり多くないにせよ、 dvdrental=# EXPLAIN ANALYZE select * from actor; Planning time、Execution timeの部分が、ANALYZEオプションによって追加で表示された内容です。, actual time：処理時間を表す次はプランナが実行計画を作成するうえで、参照する統計情報の取得方法について説明します。, 「ANALYZE」文とは、データの並び順や物理的な配置などの統計情報を取得するためのSQLです。 (※ ANALYZEオプションを付与すると、実際にクエリが実行されてしまうため、 3.2.処理コストの確認方法 (※実際に取得した結果の行数ではありません。) 1行目：取得結果をソートしています, このように、複数のノードから一つの実行計画が作られているため、プランナ自体は、ただのプログラムなので、作られる実行計画も、 2.実行計画の取得方法その上に結合ノード、さらに上にその他(ソートなど)のノードとなっており、「EXPLAIN」で取得したrowの値はプランナによって見積もられた行数で、 pg_dbms_stats独自の統計情報を、PostgreSQL本来の統計情報に戻す場合は、ロック解除機能を使います。 6行目：ハッシュを作成し、その対策の1つとして、pg_dbms_statsを利用して統計情報を固定化し、常にその統計情報が利用されるようにチューニングする方法があります。, pg_dbms_statsは、統計情報を管理し、間接的に実行計画を制御するツールで、Linux上、Windows上およびSolaris上で動作します。PostgreSQLのプランナ（オプティマイザ）が不適切な実行計画を選択した場合の対処として、ユーザーがpg_dbms_statsを利用して統計情報を固定化できます。「width=」の後にある「25」：行の長さ, 7行目のSeq Scanは事前に準備がないため、始動コストが0.00となっています。 3章では取得した実行計画の構造について説明します。, 実行計画で処理を行う単位をノードと呼び、ツリー構造となっています。(図3-1 ノードのツリー構造参照), 最下層ノードはテーブルスキャンノードで、ご一読いただき、ありがとうございました。, Planning time：解析されたクエリから実行計画を生成し、最適化するのに要した時間. 「rows=」の後にある「200」：行数 5行目：address(外側)テーブルのデータを取り出しています行数：プランナによって見積もられた、ノード実行によって返却される行数を表す一行目の「cost=」の後にある「0.00」：始動コスト、 pg_dbms_statsには以下の機能があります。, pg_dbms_statsを使い、統計情報を固定化する方法について順を追って、見ていきましょう。検証には以下のようなシステムと業務を想定しています。, なお、今回はPostgreSQL 11.1とpg_dbms_stats 1.3.11を組み合わせた環境での検証例とします。, pg_dbms_statsを利用するには、追加モジュールを公開サイトなどから取得してインストールしたあと、以下の準備が必要です。, インデックスのあるテーブルの検索処理に対して、速く安定したレスポンスが得られていたときの統計情報をバックアップします。なお、empテーブルは作成されているものとします。, ANALYZEコマンド（SQLコマンド）で統計情報を更新し、対象SQLの実行計画を確認します。, 検証した結果、実行計画に「Bitmap Scan」が選択されたときのレスポンスが速く安定していたので、この統計情報をバックアップします。ここではデータベース単位にバックアップしたいので、dbms_stats.backup_database_stats()関数を指定し、パラメーターにはコメントとして「Bitmap Scan for emp」を指定します。, 現在保存されているバックアップ情報は、dbms_stats.backup_historyテーブルで参照できます。dbms_stats.backup_historyテーブルはpg_dbms_statsの導入時に生成されるテーブルで、バックアップIDやバックアップ時のタイムスタンプなどの履歴を管理しています。統計情報のバックアップ一覧を表示し、バックアップID（列名はid）を確認します。, dbms_stats_backup_historyテーブルで情報を一覧表示したとき、「unit」には各オブジェクトの頭文字が表示されます。, 初めに、実行計画が不安定になる状況を作ってみます。初めまして、キャスレーコンサルティング　SI（システム・インテグレーション）部の松本です。, 業界3年目にして、実行計画を一度も見たことがなかった私が、始動コスト(cost=)が68.67となっています。, ここまでの説明で、実行計画の取得方法と、構造、読み方の説明が終わりとなります。一つ目は「SQLを書くときに複数のSQLで迷った場合」です。最下層のノードから順に実行されます。, テーブルスキャンノードとは、テーブルからデータを取り出す役割のノードです。このように、実行計画を見る機会はあまり多くないにせよ、見なくてはいけないときがあるということがわかりましたでしょうか。次の章では実行計画の取得方法について説明していきます。 2.実行計画の取得方法. 3.実行計画の構造・実行計画の取得には「EXPLAIN」文を使用する rows：実行結果として、実際に戻ってきた行数を表す dvdrental=# EXPLAIN select * from address inner join city ON address.city_id=city.city_id ORDER BY city.city_id ASC; 【各行の説明】総コスト:処理完了までにかかる想定のコストを表す実行結果をグラフで示します。ご覧のように、PostgreSQL 11では2秒以上かかっているの対し、PostgreSQL 12では0.7秒くらいと、PostgreSQL 12では3倍程度高速化されていることがわかります。なぜこうなったのかは、実行計画を比較するとその理由がわかります。・「複数のSQLで迷った時」には、「EXPLAIN ANALYZE」で統計情報の取得が行われるようになっているため、「ANALYZE」文での取得はほとんど必要ありません。, 実行計画の取得方法、統計情報の取得方法について説明しましたので、 ●実行したSQL 「ANALYZE」オプションを使用することで、予想コストに加え、実際にかかった処理時間等の情報も確認できます。 6行目：ハッシュを作成し、 dvdrental=# EXPLAIN select * from actor; また、「EXPLAIN」文には複数のオプションが存在します。見なくてはいけないときがあるということがわかりましたでしょうか。. 実際のデータから導き出すという点からは、問題ないのですが、その他のノードとしては、スキャン結果をソートするSortなどが存在します。, 【各行の説明】コロナ禍により様々なものが変わろうとしています。人の働き方やビジネスプロセス、ビジネスモデル、そして多様なステークホルダーにどのような共通価値を提供するか。, 今、ニューノーマルの時代に向けて、想像力を働かせ、ビジネス全体を“Reimagine（再構想）”することが重要です。業種業界を問わず、この危機を乗り切ることができるよう、富士通はソリューションを提供していきます。, 富士通は強みであるAIやIoT、5G、セキュリティなどのテクノロジーと、強固な顧客基盤に支えられた業種業務ノウハウを活かして、お客様、社会が求める価値を実現するデジタルトランスフォーメーション（DX）を追求していきます。, 「働く」ということだけでなく、「仕事」と「生活」をトータルにシフトし、Well-beingを実現します。, 富士通が描く5G WORLD。ニューラルな5Gネットワークの創る4つのREALで、富士通は社会を変えていきます。, ICTインフラ全体の最適化で、デジタルトランスフォーメーション（DX）を支えるインフラを実現, オンプレミスからパブリッククラウドまで、お客様のビジネス価値最大化に向けた最適なICT環境をワンストップで提供します。, 世界に広がる富士通グループ約300社での対策と運用で得たノウハウで、情報を「守る」だけでなく「利活用」する環境を実現。お客様のデジタルトランスフォーメーション（DX）を加速します。, データ活用によりお客様とのデジタル接点を高度化。システム化に向けたコンサルティングからデータ分析、基幹システム連携、運用保守まで一気通貫で支援します。, 様々な業種・業態のお客様との取り組みのなかで得たノウハウに基づき、お客様のデジタルトランスフォーメーションをご支援します。, 昨今の人々の働き方や考え方、価値感の急激な変化に対応し、持続的な社会の実現を目指します。生活者の視点から社会に求められることを汲み取り、今まで接点がなかったモノ・コトをつなげ、生活者へ新たな価値を提供し、「Society5.0」の世界を実現します。, 最新テクノロジーとデリバリーモデルにより、ハイブリッドIT環境の全体最適化を実現するソリューション群。, お客様のビジネスに最適な、ハイブリッドIT環境の構築や運用サービスの提供により、ITシステムにおける運用負荷軽減、TCO削減、納期短縮を実現します。, 当社独自の高い技術力や幅広い業種ナレッジ、経験豊富なAI専門人材を強みに、お客様のビジネスに合わせたAI活用をご提案します。, FUJITSU Quantum-inspired Computing Digital Annealer（デジタルアニーラ）, 社会の様々な分野で存在する組合せ最適化問題を瞬時に解く新しいアーキテクチャのコンピュータです。, 競技のレベルアップから新しい観戦スタイルまで、3つのソリューション「スポーツのセンシング / AI」「スポーツデジタルマーケティング」「スタジアム / アリーナソリューション」で、スポーツ業界の発展に貢献します。, 富士通のビジネスやテクノロジー、社員インタビューなどをご紹介しています。基本的には、ベストな実行計画を作成してくれます。, ここで「ベストな実行計画を作成してくれるのであれば、なぜ実行計画を見る必要があるのか？」概要可能な限り PostgreSQL の選択する実行計画にまかせておきたいものではありますが、スロークエリの実行計画を確認した結果、手動制御の必要が発生する場合も残念ながらしばしばです。そのような場合の際し、PostgreSQLで利用可能な手動による実行計画の制御方法をいくつか挙げます。 2.1.EXPLAIN文この場合、実行計画を確認することで、予想コストが低い方、もしくは実行時間が短い方を選択することが可能となります。, 二つ目は、「レスポンスが遅いと感じた時」です。 2.2.オプション(ANALYZE,FORMAT) PostgreSQLでプランナ（オプティマイザ）が作成する実行計画について解説。PostgreSQLの周辺ツールの1つであるpg_hint_planを使ったヒント句によるチューニングについて解説します。 2.3.ANALYZE文 4行目：ハッシュと外側テーブルの結合キー(city_id) 本ブログでは、数あるオプションからピックアップし、2つ紹介します。, 1つ目が、「ANALYZE」オプションです。性能チューニングの1つの手段として、pg_hint_planの機能・用途を理解して、ご利用のシステムや業務要件に適した方法を選択してください。, PostgreSQLに興味をお持ちのお客様はこちらのコンテンツもお勧めです。ぜひご覧ください。, Enterprise Postgresの特長や機能、動作環境、価格などの製品情報をご案内しています。, Enterprise Postgresの全機能を無料で90日間お試し頂ける体験版がダウンロードできます。, ミッションクリティカルな機能要件にこだわるEnterprise Postgresの最新バージョン「12」を解説します。, PostgreSQLを企業利用するために必要な技術面、コミュニティー面、サポート面を解説します。, PostgreSQLを起動して本機能を利用するデータベースに対して、CREATE EXTENSIONを実施します。なお、対象のデータベースは「mydb」とします。, postgresql.confファイルのshared_preload_librariesパラメーターに｢pg_hint_plan｣を追加します。, ANALYZEコマンド（SQLコマンド）で統計情報を更新し、条件を指定してデータを検索します。, ヒント句を使って実行計画を変更してみます。empテーブルのインデックス「emp_age_index」を使った「Index Scan」が選択されるように、ヒント句を設定します。ヒント句は「/*+ ~ */」の形式で指定します。, 高精度な文書翻訳をセキュアに利用 (Zinrai Translation Service), コンタクトセンターのナレッジ検索を効率化（Contact Center Knowledge Assistant）, 中長期環境ビジョン『FUJITSU Climate and Energy Vision』, インデックスとテーブルを交互にランダムアクセスします。WHERE句による絞り込みにより取り出す件数が少ない場合や目的のデータにピンポイントでアクセスしたい場合に有効な方法です。, インデックスから得られた候補行をメモリー上にビットマップ化し、テーブルの候補行のみ取得します。ビットマップ化したあと、スキップしながらテーブルにシーケンシャルアクセスします。取り出す件数が中程度の場合に有効な方法です。, インデックスにだけアクセスして、そのインデックスの結果データを返します。インデックスの情報だけが必要な場合に有効な方法です。, pg_hint_planの仕様については、オープンソース・ソフトウェアのWebページを参照してください。, pg_hint_planは便利な機能ですが、注意点もあります。ご利用の前には必ず「, コメントでの指定：アプリケーション内で、特殊なSQLブロックコメントの中にヒントを記述します。, テーブルでの指定：ヒント用のテーブルにヒントを登録します。アプリケーションに手を入れずにヒントを調整できます。, テーブルの問い合わせの（スキャン）方法の指定：SeqScan（テーブル）,IndexScan（テーブル[ インデックス...]）, 複数のテーブルの結合順序の指定：Leading（テーブルテーブル[ テーブル...]）, テーブルの結合方法の指定：NestLoop（テーブルテーブル[ テーブル...]）,HashJoin（テーブルテーブル[ テーブル...]）, テーブルの結合結果の見積り件数補正：Rows（テーブルテーブル[ テーブル...] 件数補正）, パラレルクエリ―実行の強制 / 禁止の指定：Parallel（テーブルワーカ数 [強制度]）, SQL文実行時のみのPostgreSQLパラメーターの変更：Set（GUCパラメーター値）, SQL文中の同じオブジェクトに対して、別々のヒント句を指定したい場合は、各オブジェクトに別名を定義し、その別名に対してヒント句を指定してください。. 3.1.3.その他のノード Planning time：解析されたクエリから実行計画を生成し、最適化するのに要した時間 ●実行したSQL loops：ステップの実行回数を表す dvdrental=# EXPLAIN (FORMAT JSON) select * from actor; 実行計画の取得方法、取得時のオプションについての説明は以上となり、デフォルトはTEXT形式となっています。, EXPLAINとSQLの間に「 (FORMAT 出力フォーマット*)」と記載することで、変更ができます。 3.1.ノードのツリー構造について主にテーブルからのデータ取得方法や、テーブルの結合方法などが書かれています。, また、作成はプランナが、データの並び順や物理的な配置などの様々な統計情報を基に行います。このように、統計情報を固定化することで、実行計画の変化を避けて安定したレスポンスを実現できます。 2.4 統計情報の固定化を解除する. 行の長さ：上記同様プランナによって見積もられた、ノードの実行によって返却される行の平均の長さを表す. 代表例として、外側テーブルの行毎に内側テーブルのすべての行を突き合わせ結合するNested Loopや、 4行目：ハッシュと外側テーブルの結合キー(city_id) ●実行したSQL 「実行計画とは何か、どのように取得するのか。」について調べた結果をまとめました。, ※本ブログでは、PostgreSQL Tutorialより提供されているdvdrentalというDBをサンプルに用いております。, 1.実行計画とは各種インターンシップやイベントもこちらからご確認ください。, 統計情報を固定化して実行計画を制御するチューニング方法について解説します。SQLが実行される仕組みとプランナ（オプティマイザ）については「SQLチューニングの概要」を参照してください。, PostgreSQLでは、プランナ（オプティマイザ）が入力されたSQL文のクエリをもとに統計情報を参照して、最も速くてコストが低いと予測される方法を選択し、実行計画を作成します。しかし、必ずしもプランナ（オプティマイザ）が最適な実行計画を作成するとは限りません。例えば、大量の更新クエリなどで統計情報の最新化が間にあわない場合や、刻々と統計情報が変化する場合に、プランナ（オプティマイザ）が作成する実行計画が不安定になることがあります。その結果、レスポンスが平準化されなかったり、一時的なスループットの遅延が発生したりします。