CODEWITHSUNDEEP
sqlpostgresqlpattern-matchingconcatenationpostgresql-performance
Yegor Koldov
Yegor Koldov

Reputation: 292

Query performance with concatenation and LIKE

Can someone explain the performance difference between these 3 queries?

concat() function:

explain analyze 
select * from person 
where (concat(last_name, ' ', first_name, ' ', middle_name) like '%Ива%');

Seq Scan on person  (cost=0.00..4.86 rows=1 width=15293) (actual time=0.032..0.140 rows=6 loops=1)
  Filter: (pg_catalog.concat(last_name, ' ', first_name, ' ', middle_name) ~~ '%Ива%'::text)
Total runtime: 0.178 ms

SQL standard concatenation with ||:

explain analyze 
select * from person 
where ((last_name || ' ' || first_name || ' ' || middle_name) like '%Ива%');

Seq Scan on person  (cost=0.00..5.28 rows=1 width=15293) (actual time=0.023..0.080 rows=6 loops=1)
  Filter: ((((((last_name)::text || ' '::text) || (first_name)::text) || ' '::text) || (middle_name)::text) ~~ '%Ива%'::text)
Total runtime: 0.121 ms

Search fields separately:

explain analyze 
select * from person 
where (last_name like '%Ива%') or (first_name like '%Ива%') or (middle_name like '%Ива%');

Seq Scan on person  (cost=0.00..5.00 rows=1 width=15293) (actual time=0.018..0.060 rows=6 loops=1)
  Filter: (((last_name)::text ~~ '%Ива%'::text) OR ((first_name)::text ~~ '%Ива%'::text) OR ((middle_name)::text ~~ '%Ива%'::text))
Total runtime: 0.097 ms

Why is concat() slowest one and why are several like conditions faster?

Upvotes: 11

Views: 16270

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

What you have observed so far is interesting, but only a minor cost overhead for concatenating strings.

The much more important difference between these expressions does not show in your minimal test case without indexes.

The first two examples are not sargable (unless you build a tailored expression index):

where concat(last_name, ' ', first_name, ' ', middle_name) like '%Ива%'
where (last_name || ' ' || first_name || ' ' || middle_name) like '%Ива%'

While this one is:

where last_name like '%Ива%' or first_name like '%Ива%' or middle_name like '%Ива%'

I.e., it can use a plain trigram index to great effect (order of columns is unimportant in a GIN index):

CREATE INDEX some_idx ON person USING gin (first_name  gin_trgm_ops
                                         , middle_name gin_trgm_ops
                                         , last_name   gin_trgm_ops);

Instructions:

Incorrect test if null is possible

concat() is generally slightly more expensive than simple string concatenation with ||. It is also different: If any of the input strings is null, the concatenated result is also null in your second case, but not in your first, since concat() just ignores null input. But you'd still get a useless space character in the result.

Detailed explanation:

If you are looking for a clean, elegant expression (about the same cost), use concat_ws() instead:

concat_ws( ' ', last_name, first_name, middle_name)

Upvotes: 5

harmic
harmic

Reputation: 30587

While not a concrete answer, the following might help you to reach some conclusions:

  1. Calling concat to concatenate the three strings, or using the || operator, results in postgres having to allocate a new buffer to hold the concatenated string, then copy the characters into it. This has to be done for each row. Then the buffer has to be deallocated at the end.

  2. In the case where you are ORing together three conditions, postgres may only have to evaluate only one or maybe two of them to decide if it has to include the row.

  3. It is possible that expression evaluation using the || operator might be more efficient, or perhaps more easily optimizable, compared with a function call to concat. I would not be surprised to find that there is some special case handling for internal operators.

  4. As mentioned in the comments, your sample is too small to make proper conclusions anyway. At the level of a fraction of a millisecond, other noise factors can distort the result.

Upvotes: 8

Yegor Koldov
Yegor Koldov

Reputation: 292

This query has overhead for calling function on each row

explain analyze 
select * from person 
where (concat(last_name, ' ', first_name, ' ', middle_name) like '%Ива%');

this query is faster cause no additional operation executed

explain analyze 
select * from person 
where (last_name like '%Ива%') or (first_name like '%Ива%') or (middle_name like '%Ива%');

Upvotes: 2

Related Questions