Postgres Select ILIKE %text% 在大字符串行上运行缓慢

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

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

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

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

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

我应该做什么来加快这一进程？表分区可以解决这个速度问题吗？或者我应该寻找索引？

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

EXAMPLE

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

STEP 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;

STEP 2

从我们的长文本列填充 tsvector 数据：

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

STEP 3

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

CREATE INDEX tsv_index
ON table_1
USING gin(long_text_tsv);

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

STEP 4

使用 tsvector 数据，您的搜索查询应如下所示：

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

ts_vector 数据应按 ts_query 数据类型搜索。在上面的查询中， 表示后面跟着，& 表示“AND”运算符。

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

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