Query performance with concatenation and LIKE

Question

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?

Answer 1

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.