sql - Postgres Select ILIKE %text% 在大字符串行上很慢

Question

我有一个只有 7 列的表，其中一列存储每一行​​的长文本数据。该文本列数据的平均字符长度约为 1500 个字符。这个表有 500.000 行。

当我使用选择查询并且不使用该文本列时，没有问题，查询按预期需要 10 秒。

但是，如果我将这个长文本列添加到我的查询中，例如 Select * from table_1，则需要 3 或 4 分钟才能完成此查询并使用 dataadapter 填充数据表。

为什么我需要查找所有长文本列记录？因为我需要对其使用文本过滤器，例如:

SELECT * 
FROM table_1 
WHERE longtextcolumn ILIKE ANY (ARRAY['%texttosearch1%', '%texttosearch2%'])

我应该怎么做才能加快进度？表分区可以解决这个速度问题吗？还是我应该寻找索引？

Answer 1

阅读这篇文章中的所有答案后，我开始了解索引，尤其是 Postgres 上的 gin 索引。今天我克服了我的问题。在使用 gin 索引方法之前，我的查询需要 8 分钟才能完成。现在相同的查询需要 50 毫秒。这是一个巨大的性能差异，我想逐步解释我为社区所做的工作。

示例

假设我们有一个名为 table_1 的表，并且该表有一个名为 long_text 的列。该 long_text 列存储长文本数据，例如长度为 1500 个字符的字符串。而这个 table_1 有 800.000 行。

第 1 步

Postgres 有一个名为 ts_vector 的数据类型。此 ts_vector 数据类型获取您的长文本并计算其中的单个不同单词(基于语言)并索引其中的单词。所以我们需要在 table_1 上创建一个 ts_vector 列并转换我们的 long_text 列并从中填充 ts_vector 数据。这将是这样的:

ALTER table table_1 
ADD COLUMN long_text_tsv TS_VECTOR;

第 2 步

从我们的 long_text 列填充 ts_vector 数据:

UPDATE table_1 t1
SET long_text_tsv = to_tsvector('english', t1.long_text)
FROM table_1 t2;

第 3 步

为新填充的 long_text_tsv 列创建 gin 索引

CREATE INDEX tsv_index
ON table_1
USING gin(long_text_tsv);

之后，您就可以使用新创建和索引的 tsv 数据过滤长文本数据了

第 4 步

使用 tsvector 数据，您的搜索查询应该是这样的:

SELECT long_text FROM table_1
WHERE long_text_tsv @@ to_tsquery('john<->lennon&music');

ts_vector 数据应按 ts_query 数据类型搜索。在上述查询中，<-> 表示后跟，& 表示“与”运算符。

使用我解释的这种方法，我的搜索查询速度比旧方法快 100 倍。

我不确定我是否以正确的方式做这一切，但我认为我做得很好，因为现在我的程序中的一切看起来都很好。如果该答案有问题，请警告我。