データベースクエリオプティマイザーは、クエリに対して最速の実行プランを自動的に選択することを約束していますが、新しいテストでは、これらのシステムがしばしばその目標を達成できていないことが明らかになりました。これらのシステムは単純なシナリオではうまく機能しますが、実世界のデータパターンでは頻繁に苦戦し、開発者を驚かせるパフォーマンス問題を引き起こしています。
クエリ最適化の現実
異なるデータ分布での最近のパフォーマンステストでは、クエリオプティマイザーが一貫して最適でないプランを選択していることが示されています。テストでは範囲条件を持つ単純な SELECT クエリが使用されましたが、これらの基本的な操作でさえ、オプティマイザーが選択するものと実際に最も高速に実行されるものとの間に大きなギャップがあることが明らかになりました。この問題は、複数のテーブルと結合を含む複雑なクエリでより顕著になります。
核心的な問題は、オプティマイザーがどのように決定を下すかにあります。これらは、パフォーマンスを推定するために、データの統計的要約と簡略化されたコストモデルに依存しています。しかし、これらの統計は必然的に実際のデータパターンの不完全な表現です。実世界のデータベースには、コンパクトな統計要約では完全に捉えることができない相関関係、クラスタリング、分布パターンが含まれています。
クエリプラン性能パターン:
- 均一データ: 1-5%の選択性でインデックススキャンが選択されたが、実際にはビットマップスキャンの方が良好な性能を示した
- 周期的データ: プランナーは低選択性においてもインデックススキャンに固執し、ビットマップが最適となる場面でも変更しなかった
- 線形データ: 完全に線形なデータセットのみが一貫して最適なプラン選択を生成した
- ウォームキャッシュの影響: キャッシュされたデータでは、スキャン手法間の性能差がほぼ消失する
 |
|---|
| クエリオプティマイザが最適な実行プランを選択する頻度の視覚的表現で、クエリ実行におけるパフォーマンスギャップを強調 |
プラン予測可能性に対するコミュニティの不満
データベース専門家は、オプティマイザーの予測不可能性についてますます声を上げています。一部の人々は、現代のクエリプランナーが自分自身にとって賢すぎるようになり、データやクエリ構造の小さな変更がパフォーマンスを劇的に変える状況を作り出していると主張しています。この予測不可能性は、理論的な最適化よりも一貫したパフォーマンスが重要な本番環境で特に問題となります。
PostgreSQL コミュニティは、データベースがユーザーに特定の実行プランやインデックスを強制することを許可しないため、特別な課題に直面しています。この設計哲学は、最適でないプランを修正すべきバグとして扱うことを目的としていますが、オプティマイザーが悪い選択をした場合、開発者には限られた選択肢しか残されません。 pg_hint_plan のような拡張機能はある程度の救済を提供しますが、これらはコアシステムの一部ではありません。
「複雑すぎて、予測不可能すぎる。すべてのクエリと変数が本番環境でサプライズになってしまう。」
PostgreSQL プラン制御の制限事項:
- 特定のインデックスや実行プランを強制する標準機能がない
- セッションスコープのフラグは粗い制御を提供するが、他のクエリに影響を与える可能性がある
- マテリアライズド CTE は最適化の障壁を提供するが、望ましい最適化を妨げることがある
- サードパーティの pg_hint_plan 拡張機能がプラン強制機能を提供する
- RDS Aurora PostgreSQL はマネージドクエリプランをサポートしていると報告されている
ハードウェアと環境の複雑さ
現代のコンピューティング環境は、クエリ最適化にさらなる複雑さの層を追加します。従来のオンプレミスシステム向けに開発されたコストモデルは、クラウドストレージシステム、 NVMe アレイ、またはコンテナ化された環境のパフォーマンス特性を正確に反映していない可能性があります。特定のアクセスパターンでパフォーマンスを劇的に向上させることができるプリフェッチなどの機能は、オプティマイザーのコスト計算で常に適切に考慮されているとは限りません。
テストでは、プリフェッチ機能により、ビットマップスキャンがインデックススキャンを一貫して上回っていることが示されましたが、オプティマイザーはしばしばより遅いインデックススキャンアプローチを選択していました。これらのハードウェアレベルの最適化は、従来のコストモデルでは捉えられない方法でパフォーマンス環境を完全に変えることができます。
テスト環境仕様:
- ハードウェア： AMD Ryzen 9300X プロセッサー、 NVMe RAID ストレージ搭載
- キャッシュ条件：コールドキャッシュテスト（ページキャッシュや共有バッファにデータなし）
- クエリタイプ：単一範囲 WHERE 条件を持つシンプルな SELECT クエリ
- テスト対象データ分布：均等分布、ファジング付き循環分布、様々なファジングレベルを持つ線形分布
代替アプローチと回避策
一部の開発者は、理論的な最適化よりも予測可能性が重要なアプリケーション生成クエリに対して、より決定論的なクエリアプローチを提唱しています。最適化バリアとしての Common Table Expressions （ CTE ）の使用、セッションレベルのプランナー設定の調整、またはクエリの再構築などの技術は、実行プランに対するより多くの制御を提供できます。
高級データベースシステムは、実際のパフォーマンスデータに基づいて実行中にエンジンがそのアプローチを調整できる適応的クエリ処理を提供しています。しかし、これらの機能は普遍的に利用可能ではなく、データベース管理に独自の複雑さを追加します。
根本的な課題は残っています：クエリ最適化は本質的に損失のある圧縮問題です。オプティマイザーは限られた情報に基づいて高速な決定を下さなければならず、すべての可能なシナリオに対する完璧なプランは、合理的な時間とリソースの制約内では数学的に達成不可能かもしれません。