Effective effect of an empty LIKE in a prepared statement

Question

I set the GiST index pg_trgm in the name column of the files table.

A simplified request for a prepared statement is as follows:

 SELECT * FROM files WHERE name LIKE $1; 

Parameter $1 will consist of % + user request + % . Since input can also be an empty string, this can lead to %% .

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

Answer 1

Postgres 9.2 or later is usually smart enough to understand that the condition

 WHERE name LIKE '%%' 

It is not selective and resortes to sequential verification, ignoring the GiST index - even with prepared statements. However, you pay a small price for a useless condition.

In Postgres 9.1 or earlier, I would build a separate request for a special case.

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

Confirm yourself

Prepare the instruction and run EXPLAIN ANALYZE to check:

 PREPARE plan1 (text) AS SELECT * FROM file WHERE name LIKE $1; EXPLAIN ANALYZE EXECUTE plan1('%123%'); EXPLAIN ANALYZE EXECUTE plan1('%%'); 

Plans are usually cached throughout the session.

Alternative request

Regardless of the version you are using, if you always do 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 skip the pattern without the added wildcards ( % ), of course. This way, Postgres knows how to expect a pattern enclosed in wildcards during planning.

-> SQLfiddle daemon.
Note the sequential scan for an empty LIKE and the performance difference between the two plans.
SQLfiddle is highly load dependent etc. One start may be unreliable. Better test in your environment and run each statement several times to saturate the cache and eliminate noise.