歴史: PostgreSQLは、I/O操作に抽象的な金銭的単位を割り当てるコストベースのオプティマイザを採用しています。初期のデータベースシステムは主に回転ディスクをターゲットにしており、シークペナルティによりランダムI/Oは順次読み込みの約40倍のコストがかかっていました。この非対称性を軽減するために、Bitmap Index Scansが導入されて、マッチするタプルの位置のインメモリビットマップを構築し、ヒープにほぼ物理的順序でアクセスしました。
問題: 中程度に選択的な述語をフィルタリングする際、何千もの行が多くのデータページに散在する場合、コアのジレンマが発生します。Index Scanは、マッチするタプルポインタごとに1回のランダムI/Oを行うため、機械的なディスクのスラッシングやSSDにおける過剰なI/O要求を引き起こします。対照的に、Bitmap Index Scanはビットマップ構造を構築するオーバーヘッドを伴い、work_memの制約によりビットマップがロスになると無関係な行を処理する可能性があります。
解決策: 決定的な閾値はcost_index()とcost_bitmap_heap_scan()の関数内に存在します。プランナーはクエリを満たすために必要な異なるヒープページの数（pages_fetched）を見積もります。pages_fetchedがrandom_page_cost / seq_page_costの比率を超えると、オプティマイザはビットマップアプローチを支持します。なぜなら、ソートされたページ取得のコストがランダムアクセスのペナルティを上回るからです。random_page_costを減らす（たとえば、SSDストレージの場合は4.0から1.1に設定）と、ランダムI/Oの認識されるペナルティが低下し、プランナーは以前にビットマップ作成を引き起こした選択性に対して、標準のIndex Scansに戻ることになります。
ある金融報告プラットフォームが、現在の会計四半期のaccount_idごとにtransactionsを集約するダッシュボードクエリで深刻なレイテンシに悩まされていました。このテーブルには回転ディスクを搭載したレガシーSAN上に5億行が存在しました。述語account_id = 12345は、ヒープ全体にランダムに散らばる約12%の行にマッチしました。実行計画は、ランダムI/Oの嵐により、標準のIndex Scanが14秒を消費していることを明らかにしました。
random_page_costを4.0から8.0に引き上げることで、オプティマイザにランダムディスクシークが非常に高価であることを明示的に伝達しました。この即時の変更により、プランナーはBitmap Index Scanを選択せざるを得なくなり、ページ要求をソートされた範囲にまとめることで実行時間を1.8秒に短縮しました。ただし、この全体的な設定は、アプリケーションの他のOLTPポイントルックアップクエリにペナルティを課し、ピークトレーディング時間におけるロック競合を増加させるより効率の悪い順次スキャンに切り替わる原因となりました。
(account_id, transaction_date, amount)のカバリングインデックスを作成することで、ヒープを完全にスキップするIndex Only Scanが可能になり、80msの応答時間を実現しました。読み込みに最適でしたが、コンポジットインデックスはテーブルサイズを35%増加させ、すべての挿入が2つの大きなBツリー構造を維持する必要があったため、取り入れスループットを22%減少させ、リアルタイムトレード記録の厳しいSLAを違反しました。
created_atで範囲によるテーブルパーティショニングを実装し、random_page_costを6.0に設定することを選択しました。このハイブリッドアプローチにより、クエリは現在の四半期のパーティションに制限され、ビットマップ閾値を下回る絶対ページ数が減少しました。一方で、コストパラメータの上昇は、クロスパーティションの履歴クエリが引き続きビットマップを活用してランダムI/Oの飽和を防ぐことを保証しました。このソリューションは、取引システムの書き込みパフォーマンス制約を尊重し、読み込みが重い報告パスを最適化しました。
結果: ダッシュボードクエリは400msで安定し、OLTPの挿入パフォーマンスを劣化させることなく、報告ノードのディスクI/O利用率はビジネス時間中に95%から30%に低下しました。
effective_cache_sizeがプランナーのコストモデルにおける random_page_cost とどのように相互作用し、大きなキャッシュを持つシステムで random_page_cost を下げると特定の結合タイプのパフォーマンスが低下する可能性があるのはなぜか？
effective_cache_sizeはディスクキャッシングのために利用可能なメモリを定量化します。高く設定すると、プランナーは多くのページがRAMに存在すると想定し、random_page_costの設定にかかわらずI/Oコストを実質的に割引くことになります。random_page_costを1.1（NVMe SSDで典型的）に攻撃的に下げつつ大きなeffective_cache_sizeを維持すると、オプティマイザはIndex Scansを使用したNested Loop結合をHash Joinsよりも不合理に好むかもしれません。このモデルは、ランダムI/Oが安価でキャッシュされているため、内部関係のインデックスプローブがほぼ無料であると仮定しているため、巨大な内部ループがタプル処理によってCPUを飽和させ、キャッシュの奪回を引き起こすことを無視して、単一のバルクハッシュ操作よりも悪化する壁時計時間をもたらします。
PostgreSQLにおけるBitmap Index ScanとBitmap Heap Scanの違いは何か、そしてなぜプランナーは単一のコンポジットインデックスを使用するのではなく、複数のインデックスでBitmapOr操作を選択するのか？
Bitmap Index Scanは、マッチするタプルポインタ（またはロスがある場合のページ範囲）のビットマップを構築するためにインデックス構造を横断します。次に、Bitmap Heap Scanはそのビットマップを使用してページに順次アクセスし、実際の行データをテーブルから取得します。BitmapOr（またはBitmapAnd）は、WHERE status = 'active' OR priority = 'high'のような条件でクエリをフィルタリングする場合に発生し、別々のインデックスと一致します。PostgreSQLは、単一のパスで2つのBツリーを同時に効率的に横断できないため、各インデックスからビットマップを独立して生成し、ビット演算でこれらを組み合わせます。この手法は、「status」や「priority」のいずれか、またはどちらかの変動的フィルタリングが行われるクエリに対して、コンポジットインデックス(status, priority)よりも好まれます。一方で、2つの別々のインデックスを維持することは、複数のカバリングコンポジットの変種に比べて著しく低い書き込み増幅を引き起こします。
クエリが LIMIT 句を使用する場合、なぜ PostgreSQLは通常の Index Scanに対する早期終了が有利であるにもかかわらず、Bitmap Index Scanを選択する可能性があるのか、そして古い統計がこの誤算にどのように影響を与えるのか？**
標準のIndex Scanは、インデックスが必要な順序をサポートする場合、LIMIT N行を取得した後に即座に終了できますが、I/Oを最小限に抑えられます。しかし、プランナーが述語を満たす行の数を過小評価する場合（古いANALYZE統計や相関する列のせいで）、Index Scanがマッチを見つける前に過剰な数のリーフページを横断する必要があると仮定します。そのため、I/OコストをアモタイズするためにBitmap Index Scanを選択します。ビットマップはヒープにアクセスする前に完全にマテリアライズされなければならないため、実行者は早期に停止できず、最初の10行を除いて何千行も含むビットマップを構築しなければならず、プランナーの楽観的な見積もりに比べて壊滅的なレイテンシをもたらす結果となります。