Skip to content
Database

HypoPG：假设索引

HypoPG 是一个用于创建假想/虚拟索引的 Postgres 扩展。HypoPG 让用户能够快速创建不会消耗任何资源（CPU、磁盘、内存）的假想/虚拟索引，这些索引对 Postgres 查询规划器是可见的。

HypoPG 的动机是让用户可以搜索索引来优化慢查询，而不需要消耗服务器资源或等待它们的创建。

🌐 The motivation for HypoPG is to allow users to search for an index to improve a slow query without consuming server resources or waiting for them to build.

启用扩展 #

🌐 Enable the extension

  1. 在仪表板中转到数据库页面。
  2. 点击侧边栏的 扩展。
  3. 搜索 hypopg 并启用这个扩展。

加快查询速度 #

🌐 Speeding up a query

给定下面的表格和一个基本的查询，通过 id 从表格中选择：

🌐 Given the following table and a basic query to select from the table by id:

1
create table account (
2
  id int,
3
  address text
4
);
5
6
insert into account(id, address)
7
select
8
  id,
9
  id || ' main street'
10
from
11
  generate_series(1, 10000) id;

我们可以生成一个执行计划，来描述 Postgres 查询优化器打算如何执行这个查询。

🌐 We can generate an explain plan for a description of how the Postgres query planner intends to execute the query.

1
explain select * from account where id=1;
2
3
                      QUERY PLAN
4
-------------------------------------------------------
5
 Seq Scan on account  (cost=0.00..180.00 rows=1 width=13)
6
   Filter: (id = 1)
7
(2 rows)

使用 HypoPG，我们可以在 account(id) 列上创建一个假设索引，以检查它是否对查询优化器有用，然后重新运行解释计划。

🌐 Using HypoPG, we can create a hypothetical index on the account(id) column to check if it would be useful to the query planner and then re-run the explain plan.

注意，HypoPG 创建的虚拟索引只在创建它们的 Postgres 连接中可见。Supabase 是通过连接池连接到 Postgres 的，所以 hypopg_create_index 语句和 explain 语句应该在同一个查询中执行。

🌐 Note that the virtual indexes created by HypoPG are only visible in the Postgres connection that they were created in. Supabase connects to Postgres through a connection pooler so the hypopg_create_index statement and the explain statement should be executed in a single query.

1
select * from hypopg_create_index('create index on account(id)');
2
3
explain select * from account where id=1;
4
5
                                     QUERY PLAN
6
------------------------------------------------------------------------------------
7
 Index Scan using <13504>btree_account_id on hypo  (cost=0.29..8.30 rows=1 width=13)
8
   Index Cond: (id = 1)
9
(2 rows)

查询计划已经从 Seq Scan 变为使用新创建的虚拟索引的 Index Scan，所以我们可能会选择创建该索引的真实版本来提高目标查询的性能：

🌐 The query plan has changed from a Seq Scan to an Index Scan using the newly created virtual index, so we may choose to create a real version of the index to improve performance on the target query:

1
create index on account(id);

函数 #

🌐 Functions

资源 #

🌐 Resources