Supabase Index Advisor for PostgreSQL Query Indexing

What Supabase Index Advisor Solves

Supabase Index Advisor addresses the gap between recognizing that a PostgreSQL query is expensive and identifying indexes that may reduce its estimated cost. Its public function accepts a query as text and searches for SQL DDL statements intended to improve the query's execution time. This makes it relevant to developers working with PostgreSQL 13 or later, particularly PostgreSQL environments where extension installation is available.

The function is designed around a particular query rather than a complete application workload. That distinction matters. An index recommendation is useful only when it matches the predicates, joins, and access paths represented by the query supplied to the advisor. The README's examples use a filter on an unindexed `title` column and a query joining `author`, `publisher`, `book`, and `review`. They show that the function can return recommendations for both a simple filter and a multi-table query.

The output includes cost estimates before and after the proposed changes. It also returns an `index_statements` array and an `errors` array. A caller receives more information than a bare index name: the exact SQL, the planner cost values reported by PostgreSQL, and a separate diagnostic channel.

It is not a general query profiler, capacity planner, or index monitoring service. The README does not document workload aggregation, production telemetry, index usage statistics, automatic index creation, or rollback. Its documented scope is narrower: analyze one supplied query and propose index DDL. Database teams seeking those broader capabilities would need other PostgreSQL tooling or their own procedures.

The index_advisor Function and Its Result Contract

The documented signature is:

index_advisor(query text)
returns
    table (
        startup_cost_before jsonb,
        startup_cost_after jsonb,
        total_cost_before jsonb,
        total_cost_after jsonb,
        index_statements text[],
        errors text[]
    )

This result contract is the main integration point. The input is query text, while the output separates startup costs, total costs, generated SQL statements, and errors. The JSONB fields preserve cost values as structured PostgreSQL values. The `index_statements` field contains candidate DDL as text, while `errors` contains diagnostics.

A caller can invoke the function without applying any index. An analysis script could select the result, inspect `index_statements`, and compare `total_cost_before` with `total_cost_after`. The README does not describe an automated apply step. Applying the recommendations should therefore remain an explicit database change.

The examples show that the generated statements are ordinary PostgreSQL index DDL. A simple query produces a btree index on `public.book(title)`. The larger join example produces separate btree index statements for `public.book(author_id)`, `public.book(publisher_id)`, and `public.review(book_id)`. The returned SQL can be inspected as a concrete proposal rather than interpreted only as a score.

The function supports generic parameters such as `$1` and `$2`. This lets callers represent placeholders in the query text, as shown in the README. The documentation does not explain parameter value binding, query normalization, or duplicate recommendation handling. Those behaviors should be checked against the database version and representative queries.

Installing HypoPG and Supabase Index Advisor

The README requires PostgreSQL with HypoPG installed. It then gives a source installation procedure that clones the repository and invokes the installation target:

git clone https://github.com/supabase/index_advisor.git
cd index_advisor
sudo make install

The clone command downloads the project source. The `cd index_advisor` command enters the repository directory, and `sudo make install` runs the extension installation target through the Makefile.

The project Makefile names `index_advisor` as the extension and collects files matching `index_advisor--*.sql` as extension data. It derives the PostgreSQL build path through `pg_config` and includes PostgreSQL's `PGXS` build system.

The source procedure is the installation method documented by this repository. The README does not provide package-manager commands or a Supabase dashboard procedure. It also does not explain how to install HypoPG itself. The HypoPG requirement must be handled through its own project documentation or the PostgreSQL provider's extension setup.

After installation, create the extension in the target database. The usage examples use:

create extension if not exists index_advisor cascade;

This asks PostgreSQL to create the extension when it is not already present. Run it in the database where the advisor will operate. Once it succeeds, the database session can invoke `index_advisor` with a query string.

Running the First Query-Specific Recommendation

The smallest documented example creates a table with a primary key on `id` and a non-indexed `title` column. It then asks the advisor to analyze a parameterized filter:

create table book(
  id int primary key,
  title text not null
);

select
    *
from
  index_advisor('select book.id from book where title = $1');

The query asks for `book.id` where `title = $1`. According to the README, the displayed result includes `CREATE INDEX ON public.book USING btree (title)` in `index_statements` and an empty `errors` array. The example also displays a lower `total_cost_after` value than `total_cost_before`.

This example is a useful first check because the schema and query are small. A reader can compare the generated SQL with the table definition and verify that the proposed column matches the filter predicate. The expected result is one row containing the six documented fields. It is not confirmation that PostgreSQL created an index automatically.

The second README example creates `author`, `publisher`, `book`, and `review`, then analyzes a query with join predicates and parameters. Its displayed result contains three btree index statements, one for each selected foreign-key column. The example demonstrates that the advisor can produce multiple candidates for one query. It does not state that every statement is required, globally optimal, or suitable for concurrent deployment.

Users should check whether their schema, statistics, PostgreSQL version, and database settings produce comparable cost changes. The README does not document the internal candidate-selection method, the number of alternatives considered, or the conditions that produce the `errors` values.

Views, Materialized Views, and Upgrade State

The README lists support for materialized views and for identifying tables and columns obscured by views. Those features matter when the SQL supplied to the advisor does not expose the same relation names that a user sees through a view. The documentation does not include a materialized-view example, so it does not establish whether suggestions target the view, its base tables, or both.

The repository's listed SQL files include upgrade scripts from version 0.1.0 through `index_advisor--0.2.0--0.2.1.sql`. The release information lists `v0.2.0` with a release date of 2024-04-03. The repository is not archived, and its last push was on 2026-10-08. That records repository activity, but it does not say how often deployed installations are upgraded or whether every installation already uses the newest listed script.

PostgreSQL extension upgrades remain part of database administration. A deployment must account for its installed version, existing objects, and compatibility with its PostgreSQL environment. The README does not provide an upgrade command, a rollback procedure, or instructions for moving from one SQL script version to another. An administrator must not infer those steps from the filenames alone.

The view-related support should be evaluated against the actual query shape. If a production query passes through views, the first check is whether the returned SQL identifies the expected underlying tables and columns. Without a documented view example or diagnostic rules, that behavior remains something to verify directly.

Limits of Query-Specific Index Suggestions

The main limitation is scope. `index_advisor` analyzes a query string, but the README does not document automatic discovery of all queries in an application, ranking against production traffic, or evaluation of write costs. Every added index can affect storage requirements and the work performed during inserts, updates, and deletes. The advisor's documented output does not include those operational effects.

There is also no documented policy for choosing among multiple candidate indexes. The API returns statements, but it does not say whether an index is essential, optional, or preferable to a composite index. The simple example recommends a btree index on a filtered column. Queries that filter on several columns, sort results, or use particular text-matching conditions may require a different design, and the README does not define those cases.

A real alternative is PostgreSQL's native query analysis and index design workflow: inspect execution plans, read table and column statistics, examine existing indexes, and design indexes for the predicates and join conditions of a known workload. This requires more manual work, but it exposes planner and workload context that the advisor API does not document.

Another alternative is a database change review that checks proposed DDL against production schemas, existing constraints, and workload patterns. Index Advisor is a better fit when the immediate need is a query-specific candidate list. Native plan analysis is the better fit when index size, write throughput, constraints, or several competing workloads determine the decision. Before adopting either approach, compare the proposed columns with existing indexes and inspect the planner's treatment of the target query.

Editorial conclusion

Developers should consider this extension when they need query-specific index suggestions and already have HypoPG installed. Database teams should not apply the statements without checking existing indexes, index size, write costs, and compatibility with other workloads. First, run it against a representative query and confirm that the returned statements match the schema and that `errors` is empty.

Frequently asked questions

Official sources

1. License: PostgreSQL
2. Project website
3. README
4. Releases
5. supabase/index_advisor on GitHub