数据库

index_advisor: 查询优化

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

特性

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

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

Supabase Studio index_advisor integration.

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

例如

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)

安装#

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

1
create extension index_advisor;

API#

索引顾问公开一个函数 index_advisor(query text)，该函数接受一个查询并搜索一组 SQL DDL create index 语句，以提高查询的执行时间。

函数的签名是

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
    )

用法#

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

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)

并将返回一行，推荐在未索引的列上创建一个索引。

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

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)

限制#

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

资源#