英文:
postgres LIKE % operator vs similarity - pg_trgm.similarity_threshold . different query plans
问题
如果我使用:
SET pg_trgm.similarity_threshold = 0.9;
... where column % 'some string s';
PostgreSQL在列gin_trgm_ops上执行索引扫描。
相比之下,应该是相同的:
... where similarity(column, 'some string s' ) >= 0.9
这将执行顺序扫描而不是使用索引。
从文档中可以看到:
https://www.postgresql.org/docs/11/pgtrgm.html
> <code>text % text</code> boolean
> 如果其参数的相似度大于由 `pg_trgm.similarity_threshold` 设置的当前相似度阈值,则返回true。
为什么呢?
英文:
If I use:
SET pg_trgm.similarity_threshold = 0.9;
... where column % 'some string s';
PostgreSQL does an index scan on: gin (column gin_trgm_ops)
In contrast, which should be the same:
... where similarity(column, 'some string s' ) >= 0.9
That does a seqential scan instead of using the index.
From the documentation:
https://www.postgresql.org/docs/11/pgtrgm.html
> <code>text % text</code> boolean
> Returns true if its arguments have a similarity that is greater than the current similarity threshold set by pg_trgm.similarity_threshold
.
Why?
答案1
得分: 2
基本上,WHERE
条件必须具备以下形式才能允许索引扫描:
<indexed expression> <operator> <constant>
在这里,<indexed expression>
是你在索引上创建的表达式,<operator>
是索引的操作符类(operator class)支持的操作符,而 <constant>
是在索引扫描期间保持不变的表达式(特别地,它只能包含 STABLE
函数)。
在 PostgreSQL 中的一个例外情况是,如果你在 WHERE
条件中使用返回 boolean
的函数,并且该函数有一个优化器支持函数,允许 PostgreSQL 替代索引扫描。但是,similarity
不是这样的函数。
英文:
Fundamentally, a WHERE
condition must look like this to allow an index scan:
<indexed expression> <operator> <constant>
Here, <indexed expression>
is what you created the index on <operator>
is an operator supported by the operator family of the index's operator class, and <constant>
is an expression that is constant for the duration of the index scan (in particular, it can only contain STABLE
functions).
The one exception in PostgreSQL is if you use a function returning boolean
in the WHERE
condition, and that function has an optimizer support function that allows PostgreSQL to substitute an index scan. But similarity
is no such function.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论