Performance impact of empty LIKE in a prepared statement

Question

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;

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

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

Then this is a good time to start learning that: http://www.postgresql.org/docs/current/static/using-explain.html and http://www.postgresql.org/docs/current/static/sql-explain.html — , Sep 25 '13 at 17:08
@ErikAigner "Empty" like (`name LIKE '%%'`) will still filter out rows where `name is null`. If you want no filter at all - add `or length(user_query) = 0`. — Ihor Romanchenko, Sep 25 '13 at 18:50

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

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.

Did that already, but the `cost` still seemed marginally higher with `LIKE`. — , Sep 25 '13 at 17:18
So the cost seems to be `0.1` higher with `LIKE`, which I guess is negligible. — , Sep 25 '13 at 17:22
@IgorRomanchenko: You were right about the NULL case. I have completely rewritten the answer with more specific information by now. — Erwin Brandstetter, Sep 25 '13 at 19:29

Performance impact of empty LIKE in a prepared statement

1 Answers1

Verify yourself

Alternative query

Linked