Skip to content
Database

索引顾问：查询优化

Index advisor 是一个 Postgres 扩展，用来推荐索引以提高查询性能。

特点：

🌐 Features:

  • 支持泛型参数，例如 $1、$2
  • 支持物化视图
  • 识别被视图隐藏的表/列
  • 跳过重复的索引

index_advisor 可以直接通过 Supabase Studio 访问，方法是导航到 查询性能报告，然后选择一个查询，再点击“索引”标签。

Supabase Studio index_advisor integration.

或者，你可以直接通过 SQL 使用 index_advisor。

🌐 Alternatively, you can use index_advisor directly via SQL.

例如：

🌐 For example:

select
    *
from
  index_advisor('select book.id from book where title = $1');
 startup_cost_before | startup_cost_after | total_cost_before | total_cost_after |                  index_statements                   | errors
---------------------+--------------------+-------------------+------------------+-----------------------------------------------------+--------
 0.00                | 1.17               | 25.88             | 6.40             | {"CREATE INDEX ON public.book USING btree (title)"},| {}
(1 row)

安装 #

🌐 Installation

要开始，请运行命令启用 index_advisor

🌐 To get started, enable index_advisor by running

create extension index_advisor;

应用接口 #

🌐 API

索引顾问提供了一个单一的函数 index_advisor(query text)，它接受一个查询并搜索一组能够提高查询执行时间的 SQL DDL create index 语句。

🌐 Index advisor exposes a single function index_advisor(query text) that accepts a query and searches for a set of SQL DDL create index statements that improve the query's execution time.

这个函数的签名是：

🌐 The function's 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[]
    )

用法 #

🌐 Usage

作为一个最简单的例子，index_advisor 函数可以接收一个对未索引列有过滤条件的单表查询。

🌐 As a minimal example, the index_advisor function can be given a single table query with a filter on an unindexed column.

create extension if not exists index_advisor cascade;
create table book(
  id int primary key,
  title text not null
);
select
  *
from
  index_advisor('select book.id from book where title = $1');
 startup_cost_before | startup_cost_after | total_cost_before | total_cost_after |                  index_statements                   | errors
---------------------+--------------------+-------------------+------------------+-----------------------------------------------------+--------
 0.00                | 1.17               | 25.88             | 6.40             | {"CREATE INDEX ON public.book USING btree (title)"},| {}
(1 row)

并会返回一行，建议在未索引的列上建立索引。

🌐 and will return a row recommending an index on the unindexed column.

更复杂的查询可能会生成额外的推荐索引：

🌐 More complex queries may generate additional suggested indexes:

create extension if not exists index_advisor cascade;
create table author(
    id serial primary key,
    name text not null
);
create table publisher(
    id serial primary key,
    name text not null,
    corporate_address text
);
create table book(
    id serial primary key,
    author_id int not null references author(id),
    publisher_id int not null references publisher(id),
    title text
);
create table review(
    id serial primary key,
    book_id int references book(id),
    body text not null
);
select
    *
from
    index_advisor('
        select
            book.id,
            book.title,
            publisher.name as publisher_name,
            author.name as author_name,
            review.body review_body
        from
            book
            join publisher
                on book.publisher_id = publisher.id
            join author
                on book.author_id = author.id
            join review
                on book.id = review.book_id
        where
            author.id = $1
            and publisher.id = $2
    ');
 startup_cost_before | startup_cost_after | total_cost_before | total_cost_after |                  index_statements                         | errors
---------------------+--------------------+-------------------+------------------+-----------------------------------------------------------+--------
 27.26               | 12.77              | 68.48             | 42.37            | {"CREATE INDEX ON public.book USING btree (author_id)",   | {}
                                                                                    "CREATE INDEX ON public.book USING btree (publisher_id)",
                                                                                    "CREATE INDEX ON public.review USING btree (book_id)"}
(3 rows)

限制 #

🌐 Limitations

  • index_advisor 只会推荐单列 B 树索引。更复杂的索引将在以后的版本中支持。
  • 当泛型参数的类型无法从上下文中判断时，会在 errors 字段返回错误。要解决这些错误，可以对参数进行显式类型转换。例如：$1::int。

资源 #

🌐 Resources