Increase vector lookup speeds by applying an HSNW index

虽然本指南专门针对 HNSW 索引，但它可以通用于任何类型的索引

在没有使用 CONCURRENTLY 修饰符的情况下创建索引会锁住表，但同时也会增加构建时间。关于索引的一般建议，可以查看这份指南。

为了加快查询速度，最好在你的嵌入列上创建一个 HSNW 索引#

🌐 To speed up queries, it is ideal to create an HSNW index on your embedded column

创建 HNSW 索引的一般结构是这样的：

🌐 The general structure for creating an HNSW index follows this pattern:

1
CREATE INDEX <custom name of index> ON <table name> USING hnsw (<vectorized column> <search type>);

搜索可以分为三种类型：

🌐 Search can be one of three types:

查询只有在索引与使用的搜索类型匹配时才能使用。如果你不确定应该优先使用哪种搜索类型，vector_cosine_ops 是最常用的。你可以查看我们的指南获取更多信息。Crunchy Data 的团队也写了一篇解说文章，你可能会觉得有用。

🌐 Queries can only use the index if it matches the search type used. If you are unsure which search type to prioritize, vector_cosine_ops is the most commonly used. You can checkout our guide for more info. The folks at Crunchy Data also wrote an explainer that you may find useful.

应用索引可能会很慢并且计算开销大，所以在此之前应该做一些准备工作：

🌐 Applying an index can be slow and computationally expensive, so there are a few preparations that should be made beforehand:

1. 确保你的 pgvector 在 Supabase 上是最新版本。

0.6 版本及以后的版本加快了 HNSW 的构建速度。你可以在 仪表板的扩展页面 查看当前版本。如果需要，你可以在 基础设施设置 进行软件升级。

🌐 Versions 0.6 and later have accelerated HNSW build speeds. You can observe your current version in the Dashboard's Extensions Page. You can perform a software upgrade in the Infrastructure Settings if necessary.

2. 设置外部连接

仪表板对查询有大约 2 分钟的内部时间限制。索引一个大表几乎总是会花更多时间，所以有必要通过外部接口执行你的代码，比如 PSQL。

🌐 The Dashboard has an internal time limit of ~2 minutes for queries. Indexing a large table will almost always take more time, so it is necessary to execute your code through an external interface, such as PSQL.

你可以通过以下链接和说明在 macOS 和 Windows 上安装 PSQL。对于 Linux（Debian），你可以运行以下命令：

🌐 You can install PSQL in macOS and Windows by following these links and instructions. For Linux (Debian) you can run the following:

1
sudo apt-get update
2
sudo apt-get install postgresql-client

安装完成后，你可以在仪表板上点击 connect 找到你的 PSQL 字符串。如果你使用的是连接池，确保它是 Session pooler（端口 5432）。

🌐 Once installed, you can find your PSQL string on the dashboard by clicking connect. Make sure if you are using the pooler connection that it is the Session pooler (port 5432).

如果你的网络可以使用 IPv6，可以考虑使用直接连接字符串而不是 Supavisor。这不是强制的，但对于运行时间较长的任务，最好减少网络复杂性。要检查你的网络是否兼容，可以使用这个 cURL 命令来请求你的 IPv6 地址：

🌐 If your network can use IPv6, consider using the direct connection string instead of Supavisor. It's not mandatory, but for tasks that run a long time, it's best to reduce network complexity. To check if your network is compatible, use this cURL command to request your IPv6 address:

1
curl -6 https://ifconfig.co/ip

如果返回了地址，你应该使用在仪表板上点击 连接 找到的直接连接字符串。

🌐 If an address is returned, you should use your direct connection string found on the dashboard by clicking connect.

3. 增加索引创建的内存（可选）

maintenance_work_mem 变量限制了维护操作（如整理、修改和索引表）可以使用的最大内存。在你的会话中，你应该尝试将它设置为一个合理偏高的值：

🌐 The maintenance_work_mem variable limits the maximum amount of memory that can be used by maintenance operations, such as vacuuming, altering, and indexing tables. In your session you should try to set it to a reasonably high value:

1
set maintenance_work_mem to <several Gb's>; -- '#GB'

检查值以确保它已被设置：

🌐 Inspect value to make sure it has been set:

1
show maintenance_work_mem;

4. 增加用于索引创建的核心数（可选）

max_parallel_maintenance_workers 变量限制了维护操作（包括表索引）可以使用的核心数量。在你的会话中，你可以尝试将其设置为大约你计算核心数量的 1/2 到 2/3：

🌐 The max_parallel_maintenance_workers variable limits the amount of cores that can be used by maintenance operations, including indexing tables. In your session, you should try to set it to a value roughly 1/2 to 2/3s of your compute core amount:

1
set max_parallel_maintenance_workers to <integer>;

检查值以确保它已被设置：

🌐 Inspect value to make sure it has been set:

1
show max_parallel_maintenance_workers;

5. 设置自定义超时

禁用你的连接查询超时：

🌐 Disable query timeout for your connection:

1
set statement_timeout = '0';

检查值以确保它已被设置：

🌐 Inspect value to make sure it has been set:

1
show statement_timeout;

6. 考虑暂时升级你的计算资源大小（可选）

如果你的任务特别长，你可以通过暂时提升计算能力来加快速度。计算资源按小时收费，所以你可以增加一两个小时以更快完成任务，然后再恢复原来的设置。这里有一份计算附加功能列表。如果你想临时升级，可以在你的仪表板的附加功能设置中找到适合你项目的附加功能。

🌐 If your task is particularly long, you can speed it up by boosting your computing power temporarily. Compute size is charged by the hour, so you can increase it for an hour or two to finish your task faster, then scale it back afterwards. Here is a list of compute add-ons. If you want to temporarily upgrade, you can find the add-ons for your project in your Dashboard's Add-ons Settings.

7. 考虑增加磁盘容量（可选）

HSNW 索引在构建过程中可能会产生临时文件，这些文件可能会占用几 GB 的磁盘空间。考虑在基础设施设置中增加磁盘大小，以应对短期的磁盘压力。

🌐 HSNW indexes can produce temporary files during their construction that may consume a few GBs worth of disk. Consider increasing the disk size in the Infrastructure settings to accommodate for short-term disk stress.