By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE Prefixes (prefix VARCHAR(100));
CREATE TABLE
INSERT INTO Prefixes VALUES ('l'), ('m'), ('n');
INSERT 0 3
SELECT prefix FROM Prefixes WHERE 'long string' LIKE prefix || '%'
| prefix |
|---|
| l |
SELECT 1
SELECT prefix FROM Prefixes WHERE 'long string' LIKE '%' || prefix || '%'
| prefix |
|---|
| l |
| n |
SELECT 2
SELECT prefix FROM Prefixes WHERE 'long string' LIKE CONCAT(prefix, '%')
| prefix |
|---|
| l |
SELECT 1
SELECT prefix FROM Prefixes WHERE 'long string' LIKE CONCAT('%', prefix, '%')
| prefix |
|---|
| l |
| n |
SELECT 2