索引顾问：查询优化

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:

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

安装 #

🌐 Installation

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

🌐 To get started, enable index_advisor by running

1
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:

1
index_advisor(query text)
2
returns
3
    table  (
4
        startup_cost_before jsonb,
5
        startup_cost_after jsonb,
6
        total_cost_before jsonb,
7
        total_cost_after jsonb,
8
        index_statements text[],
9
        errors text[]
10
    )

用法 #

🌐 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.

1
create extension if not exists index_advisor cascade;
2
3
create table book(
4
  id int primary key,
5
  title text not null
6
);
7
8
select
9
  *
10
from
11
  index_advisor('select book.id from book where title = $1');
12
13
 startup_cost_before | startup_cost_after | total_cost_before | total_cost_after |                  index_statements                   | errors
14
---------------------+--------------------+-------------------+------------------+-----------------------------------------------------+--------
15
 0.00                | 1.17               | 25.88             | 6.40             | {"CREATE INDEX ON public.book USING btree (title)"},| {}
16
(1 row)

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

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

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

🌐 More complex queries may generate additional suggested indexes:

1
create extension if not exists index_advisor cascade;
2
3
create table author(
4
    id serial primary key,
5
    name text not null
6
);
7
8
create table publisher(
9
    id serial primary key,
10
    name text not null,
11
    corporate_address text
12
);
13
14
create table book(
15
    id serial primary key,
16
    author_id int not null references author(id),
17
    publisher_id int not null references publisher(id),
18
    title text
19
);
20
21
create table review(
22
    id serial primary key,
23
    book_id int references book(id),
24
    body text not null
25
);
26
27
select
28
    *
29
from
30
    index_advisor('
31
        select
32
            book.id,
33
            book.title,
34
            publisher.name as publisher_name,
35
            author.name as author_name,
36
            review.body review_body
37
        from
38
            book
39
            join publisher
40
                on book.publisher_id = publisher.id
41
            join author
42
                on book.author_id = author.id
43
            join review
44
                on book.id = review.book_id
45
        where
46
            author.id = $1
47
            and publisher.id = $2
48
    ');
49
50
 startup_cost_before | startup_cost_after | total_cost_before | total_cost_after |                  index_statements                         | errors
51
---------------------+--------------------+-------------------+------------------+-----------------------------------------------------------+--------
52
 27.26               | 12.77              | 68.48             | 42.37            | {"CREATE INDEX ON public.book USING btree (author_id)",   | {}
53
                                                                                    "CREATE INDEX ON public.book USING btree (publisher_id)",
54
                                                                                    "CREATE INDEX ON public.review USING btree (book_id)"}
55
(3 rows)

限制 #

🌐 Limitations

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

资源 #

🌐 Resources

index_advisor 仓库