PostgreSQL 在3亿个地址中使用pg_trgm进行搜索

原创于 2025-01-16 16:00:40 发布

· 908 阅读

8 ·

版权

文章标签：

#postgresql #数据库

PostgreSQL/PostGIS 专栏收录该内容

23 篇文章

订阅专栏

pg_trgm是PostgreSQL的一个内置模块，用于处理模糊文本搜索。我们将了解pg_trgm的基本原理，并示范如何应用它进行地址搜索。

什么是pg_trgm

pg_trgm（PostgreSQL Trigram）是一个用于模糊文本搜索的扩展模块。它基于 trigram（三元组）的概念，即将字符串分割成三个字符的集合。pg_trgm提供了一组函数和操作符，可以计算两个字符串之间的相似度，并快速找到与目标字符串相似的字符串。

pg_trgm模块包含了几个关键函数，其中最常用的是similarity和 % 操作符。similarity函数可以计算两个字符串之间的相似度，返回一个0到1之间的值，值越接近1表示相似度越高。%操作符用于在一个字符串中搜索与另一个字符串相似的部分。

如何安装pg_trgm

在使用pg_trgm之前，我们需要确保它已经被正确地安装在PostgreSQL数据库中。我们可以通过以下方式来安装：

首先，确保你的PostgreSQL版本在9.1或以上。
打开数据库的终端，执行以下命令以安装pg_trgm扩展：

CREATE EXTENSION pg_trgm;

如果安装成功，将会收到一条“CREATE EXTENSION”命令返回的消息。

使用pg_trgm进行地址搜索

假设我们有一个包含300,000,000个地址的数据库表，我们想要通过地址的一部分或近似地址来搜索特定的地址。使用pg_trgm可以帮助我们实现快速且准确的搜索。

以下是一个使用pg_trgm进行地址搜索的示例：

CREATE TABLE addresses (
    id SERIAL PRIMARY KEY,
    address VARCHAR
);

-- 添加一些示例地址数据
INSERT INTO addresses (address)
VALUES  
    ('123 Main Street'),
    ('456 Oak Avenue'),
    ('789 Elm Street'),
    ('1011 Maple Drive');

-- 在addresses表中创建pg_trgm索引
CREATE INDEX idx_address ON addresses USING gin (address gin_trgm_ops);

-- 搜索与目标地址相似的地址
SELECT id, address
FROM addresses
WHERE address % 'Main St';

在上面的示例中，我们首先创建了一个名为addresses的表，包含了id和address两个列。然后，我们通过INSERT语句插入了一些示例地址数据。接下来，我们使用CREATE INDEX命令在address列上创建了一个pg_trgm索引。这个索引将会加速我们后续的地址搜索。

最后，我们使用SELECT语句在addresses表中进行了一个以’Main St’为关键词的地址搜索。我们使用%操作符来搜索与目标地址相似的地址。运行这个查询将返回address列中包含’Main St’关键词的所有地址。