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 users(
ID INT PRIMARY KEY
, username text NOT NULL -- never use char(n)
);
INSERT INTO users VALUES
(1, 'mandela')
, (2, 'delpiero')
, (3, 'gondela')
, (4, 'del')
, (5, 'dell');
CREATE TABLE
INSERT 0 5
SELECT *
FROM users
WHERE username ILIKE '%del%'
ORDER BY username NOT ILIKE 'del%' -- leading match first
, length(username) -- shorter words first
, username; -- finally alphabetical
| id | username |
|---|---|
| 4 | del |
| 5 | dell |
| 2 | delpiero |
| 3 | gondela |
| 1 | mandela |
SELECT 5