我已成立要旨pg_trgm对指数name的列files表。
准备好的语句的简化查询看起来是这样的：
SELECT * FROM files WHERE name LIKE $1;
在$1 PARAM将组成% +用户查询+ % 。 由于输入也可能是一个空字符串，这可能导致%% 。
是否“空” LIKE （ %% ）导致性能下降？ 我应该建立在这种情况下，一个新的查询，或者它没有关系？
Answer 1:
Postgres 9.2 or later is generally smart enough to realize that the condition
WHERE name LIKE '%%'
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.
In Postgres 9.1 or earlier I would build a separate query for the special case.
Compare the Notes section for the PREPARE statement in the manual for the versions 9.1, 9.2 and 9.3.
Verify yourself
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('%%');
Plans are generally cached for the duration of the session.
Alternative query
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 || '%');And pass the pattern without added wildcards (%), of course. This way, Postgres knows to expect a pattern enclosed in wildcards at planning time.
->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.
文章来源: Performance impact of empty LIKE in a prepared statement
