空的LIKE对准备好的语句的性能影响 [英] Performance impact of empty LIKE in a prepared statement

问题描述

我已经设置了GiST pg_trgm 文件表的名称列上的$ c> 索引。

I have set a GiST pg_trgm index on the name column of the files table.

对准备好的语句的简化查询如下：

The simplified query of the prepared statement looks like this:

SELECT * FROM files WHERE name LIKE $1;

$ 1 参数将由％ +用户查询+ ％。由于输入也可能是空字符串，因此可能会导致 %% 。

The $1 param will be composed of % + user query + %. Since the input might also be an empty string this might result in %%.

执行空 Like （ %% ）是否会导致性能下降？在这种情况下，我应该建立一个新查询吗？

Does an "empty" LIKE (%%) result in performance degradation? Should I build a new query in that case, or does it not matter?

推荐答案

Postgres 9.2或更高版本通常足够聪明，可以意识到条件

Postgres 9.2 or later is generally smart enough to realize that the condition

WHERE name LIKE '%%'

不是选择性的，而是采用顺序扫描而忽略GiST索引-即使使用准备好的语句。不过，您愿意付出很小的代价。

is not selective and resorts to a sequential scan ignoring the GiST index - even with prepared statements. You do pay a small price for the useless condition, though.

在Postgres 9.1或更早的版本中，我将为特殊情况构建一个单独的查询。

In Postgres 9.1 or earlier I would build a separate query for the special case.

比较版本手册中 PREPARE 语句的注释部分< a href = http://www.postgresql.org/docs/9.1/interactive/sql-prepare.html#AEN73409 rel = nofollow> 9.1 ， 9.2 和 9.3 。

Compare the Notes section for the PREPARE statement in the manual for the versions 9.1, 9.2 and 9.3.

准备语句并运行 EXPLAIN ANALYZE 进行测试：

Prepare the statement and run EXPLAIN ANALYZE to test:

PREPARE plan1 (text) AS
SELECT  * FROM file
WHERE   name LIKE $1;

EXPLAIN ANALYZE EXECUTE plan1('%123%');

EXPLAIN ANALYZE EXECUTE plan1('%%');

在会话期间通常会缓存计划。

无论运行的版本是什么，如果始终执行全文搜索（左右通配符），则对于准备好的语句，此查询应该更快：

Regardless of the version you are running, if you always perform a full text search (wildcards left and right), this query should be faster for a prepared statement:

SELECT * FROM files WHERE name LIKE ('%' || $1 || '%');

当然可以传递不添加通配符（％）的模式。这样，Postgres知道可以在计划时将通配符括起来的模式。

And pass the pattern without added wildcards (%), of course. This way, Postgres knows to expect a pattern enclosed in wildcards at planning time.

-> SQLfiddle演示。

请注意对空LIKE的顺序扫描以及两个计划之间的性能差异。

SQLfiddle随负载而变化很大单次运行可能不可靠。更好地在您的环境中进行测试，并多次运行每个语句以使缓存饱和并消除噪音。

->SQLfiddle demo.
Note the sequential scan for the empty LIKE and the performance difference between the two plans.
SQLfiddle varies a lot, depending on load etc. A single run may not be reliable. Better test in your environment and run each statement a couple of times to saturate cache and eliminate noise.

这篇关于空的LIKE对准备好的语句的性能影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！