Postgres在大型字符串行上选择iLike%TEXT%速度较慢 [英] Postgres Select ILIKE %text% is Slow On Large String Rows

问题描述

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

当我使用SELECT查询并且没有使用Text列时，没有问题，查询需要10秒。

但是，如果我像Select * from table_1那样将此长文本列添加到我的查询中，则需要3到4分钟来完成此查询并使用DataAdapter填充DataTable。

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

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

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

推荐答案

在阅读了这篇文章中的所有答案后，我开始了解索引，特别是postgres上的杜松子酒索引。今天我克服了我的问题。在使用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;

第二步

从LONG_TEXT列填充ts_VECTOR数据：

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

第三步

创建新填充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_Query数据类型搜索ts_VECTOR数据。在上面的查询中，<；->；表示后跟，&；表示‘AND’运算符。

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

我不确定我是否以正确的方式完成这一切，但我假设我做得很好，因为我的程序现在看起来一切正常。如果答案有误，请提醒我。

这篇关于Postgres在大型字符串行上选择iLike%TEXT%速度较慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！