数据库

index_advisor: 查询优化工具

索引顾问是一个Postgres扩展，用于推荐索引以提升查询性能。

功能特性：

  • 支持通用参数如 $1, $2
  • 支持物化视图
  • 识别被视图混淆的表/列
  • 跳过重复索引

您可以直接通过Supabase Studio访问index_advisor，导航至查询性能报告，选择查询后点击"indexes"标签页。

Supabase Studio中的index_advisor集成

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

例如：

1
2
3
4
5
6
7
8
9
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)

安装指南

要开始使用，请运行以下命令启用index_advisor：

1
create extension index_advisor;

API接口

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

函数签名如下：

1
2
3
4
5
6
7
8
9
10
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[]    )

使用方法

作为最简示例，可以向 index_advisor 函数传入一个在未索引列上带有过滤条件的单表查询：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)

该函数会返回一行结果，建议在未索引列上创建索引。

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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)

限制条件

  • index_advisor 目前仅推荐单列的 B-tree 索引。更复杂的索引将在未来版本中支持。
  • 当通用参数的类型无法从上下文中推断时，会在 errors 字段返回错误。要解决这些错误，请为参数添加显式类型转换，例如 $1::int。

相关资源