sql: LIKE and ILIKE with exact match strangely slow

[michae2]

The LIKE and ILIKE operators are strangely very slow when using an exact match. Even slower than with the same pattern wrapped with wildcards. Here's a demonstration using some different text operators:

SELECT count(i::text = ('%' || i::text || '%')) FROM generate_series(0, 999999) s(i);
SELECT count(i::text < ('%' || i::text || '%')) FROM generate_series(0, 999999) s(i);
SELECT count(i::text || ('%' || i::text || '%')) FROM generate_series(0, 999999) s(i);
SELECT count(position(i::text IN ('%' || i::text || '%'))) FROM generate_series(0, 999999) s(i);
SELECT count(trim(i::text FROM ('%' || i::text || '%'))) FROM generate_series(0, 999999) s(i);
SELECT count(i::text LIKE ('%' || i::text || '%')) FROM generate_series(0, 999999) s(i);
SELECT count(i::text ILIKE ('%' || i::text || '%')) FROM generate_series(0, 999999) s(i);
SELECT count(i::text ~ ('%' || i::text || '%')) FROM generate_series(0, 999999) s(i);
SELECT count(i::text ~* ('%' || i::text || '%')) FROM generate_series(0, 999999) s(i);

-- the strange cases
SELECT count(i::text LIKE i::text) FROM generate_series(0, 999999) s(i);
SELECT count(i::text ILIKE i::text) FROM generate_series(0, 999999) s(i);

Below is an unscientific best-of-five-runs comparison of PostgreSQL 14.6 and single-node CockroachDB v22.2.0 on the same hardware. Everything looks reasonable except for the last two cases:

operator	PG 14.6	CRDB v22.2.0
=	290 ms	277 ms
<	826 ms	275 ms
||	299 ms	319 ms
position	308 ms	374 ms
trim	408 ms	427 ms
LIKE with wildcards	320 ms	516 ms
ILIKE with wildcards	549 ms	535 ms
~	7770 ms	2326 ms
~*	7959 ms	2665 ms
LIKE no wildcards	260 ms	4177 ms ???
ILIKE no wildcards	495 ms	4182 ms ???

Jira issue: CRDB-21469

[michae2]

I wonder if this is the same underlying problem as #89749?

[michae2]

According to profiling we're not using eval.optimizedLikeFunc for some reason. 🤔