PostgreSQL Index Advisor

• クエリ性能を向上させるためのインデックスを推奨してくれる Supabase の PostgreSQL 拡張
• index_advisor() 関数にクエリを渡すと、起動時/全体に対する前後のコストとインデックス作成用の SQL DDL を返す
  • 実行: select * from index_advisor('select book.id from book where title = $1');
  • 返り値: {"CREATE INDEX ON public.book USING btree (title)"}
• 複雑なクエリに対しては、複数のインデックス作成文を返すこともある
• ジェネリックパラメータをサポート ($1, $2, ..)
• Materialized View をサポート
• ビューによって隠されたテーブル/カラムを識別可能

[savvykang]

現行バージョンでは、単一カラムの btree インデックスのみを推奨します。クエリ条件が複雑になったり、full text 検索を行っていたりする場合は利用できません。https://supabase.com/docs/guides/…

[savvykang]

検索条件が複雑な場合、複合インデックスの代わりに複数の単一カラムインデックスが使われるとは言われますが、まったく同じ動作をするわけではないようです。あるいは、複合インデックスと複数の単一カラムインデックスを同時に使うのが最善なケースもあるそうです

https://www.postgresql.org/docs/current/indexes-bitmap-scans.html

[xguru]

Hacker Newsのコメント

• テーブルに実際に保存されているデータに基づいて、より効率的なデータ型を提案してくれる機能があるとよい
• 遅いクエリを自動で検知し、必要なインデックスを作成するデータベースがあるとよい
  • アプリケーションでロードテストを実行すると、データベースを呼び出してクエリを収集し、その後データベースが自動で調整される
• HypoPGがRDSで1年以上利用可能だったことを知らなかった
• 3つ以上のjoinでは、あるrelationでインデックスを使ってほしいのに、CTEにlimitをかけないとPostgresが各joinを並列実行しようとして膨大な行をjoinしようとする
  • 最近はクエリプランナーを相手にしていると、pgと別れることになりそうな気がする
• CockroachDBには類似の機能が組み込まれている
  • 既存の遅いクエリを取り込み、よりよいクエリ計画のために仮想インデックスを分析して提案する
  • コンソールUIからワンクリックで追加できる
• PrestoやSparkのような分散クエリエンジンでは、インデックスの代わりにパーティションとバケットを使って似たようなことをしている
  • これにより計算量、時間、コストを削減できる
• 素のPL/pgSQLで書かれていて便利
  • index_advisor(text) 関数をセッションにコピーして、ハードコーディングやヒューリスティックを始めたくなる誘惑がある
  • たいていの意味のある拡張は、コンパイル、インストール、作成、削除が必要
• TiDBのTiAdvisorに似ており、仮想方式を使っている
• pgheroを使っており、GUIでこの機能を提供している
• 関連するトレードオフについての考慮や洞察は提供していないように見える
  • ベース拡張であるHypoPGは、クエリプランナーに影響するデータの統計を収集していないようだ
• 継承された親テーブルと子テーブルを認識するのか気になる