使用'类似'在PostgreSQL中查询性能 [英] Query performance in PostgreSQL using 'similar to'

问题描述

我需要从表中检索某些行，具体取决于特定列中的某些值，在示例中名为 columnX ：

I need to retrieve certain rows from a table depending on certain values in a specific column, named columnX in the example:

select *
from tableName 
where columnX similar to ('%A%|%B%|%C%|%1%|%2%|%3%')

因此，如果 columnX 包含至少一个指定的值（A ，B，C，1,2,3），我会保留这一行。

So if columnX contains at least one of the values specified (A, B, C, 1, 2, 3), I will keep the row.

我找不到比使用类似更好的方法。问题是对于行超过一百万的表，查询花费的时间太长。

I can't find a better approach than using similar to. The problem is that the query takes too long for a table with more than a million rows.

我试过索引它：

create index tableName_columnX_idx on tableName (columnX) 
where columnX similar to ('%A%|%B%|%C%|%1%|%2%|%3%')

但是，如果条件是可变的（值可能是其他的比A，B，C，1,2,3），我需要为每个条件提供不同的索引。

However, if the condition is variable (the values could be other than A, B, C, 1, 2, 3), I would need a different index for each condition.

这个问题有没有更好的解决方案？

Is there any better solution for this problem?

编辑：感谢大家的反馈。看起来我已达到这一点可能是因为设计错误（我在分开的问题。

Thanks everybody for the feedback. Looks like I've achieved to this point maybe because of a design mistake (topic I've posted in a separated question).

推荐答案

我同意@Quassnoi， GIN索引是最快和最简单的 - 除非写入性能或磁盘空间是问题，因为它占用了大量的空间，并为 INSERT ， UPDATE 和 DELETE 。

I agree with @Quassnoi, a GIN index is fastest and simplest - unless write performance or disk space are issues because it occupies a lot of space and eats quite a bit of performance for INSERT, UPDATE and DELETE.

我的额外答案由您的对帐单触发：

My additional answer is triggered by your statement:

I can't find a better approach than using similar to.

如果这是你找到的，那么你的搜索还没有结束。 SIMILAR TO 完全浪费时间。从字面上看。 PostgreSQL只是为了符合（奇怪的）SQL标准。检查 EXPLAIN ANALYZE 的输出，您会发现 SIMILAR TO 已被正则表达式替换。

If that is what you found, then your search isn't over, yet. SIMILAR TO is a complete waste of time. Literally. PostgreSQL only features it to comply to the (weird) SQL standard. Inspect the output of EXPLAIN ANALYZE for your query and you will find that SIMILAR TO has been replaced by a regular expression.

在内部，每个 SIMILAR TO 表达式都会被重写为正则表达式。因此，对于每个 SIMILAR TO 表达式，至少有一个正则表达式匹配更快一些。如果您不确定，让 EXPLAIN ANALYZE 为您翻译。你不会在手册中找到这个，PostgreSQL不承诺这样做，但我还没有看到例外。

Internally every SIMILAR TO expression is rewritten to a regular expression. Consequently, for each and every SIMILAR TO expression there is at least one regular expression match that is a bit faster. Let EXPLAIN ANALYZE translate it for you, if you are not sure. You won't find this in the manual, PostgreSQL does not promise to do it this way, but I have yet to see an exception.

这里的更多细节关于dba.SE的相关答案。

这篇关于使用'类似'在PostgreSQL中查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！