SQL: LIKE 'abc%' can use an index, LIKE '%abc' cannot

CREATE INDEX idx_users_email ON users (email);

SELECT * FROM users WHERE email LIKE 'ann%';    -- index range scan
SELECT * FROM users WHERE email LIKE '%@x.com'; -- full scan

-- For suffix search, index the reversed value:
CREATE INDEX idx_email_rev ON users (REVERSE(email));
SELECT * FROM users WHERE REVERSE(email) LIKE REVERSE('%@x.com');

-- For anywhere-search, use a trigram or full-text index:
CREATE INDEX idx_email_trgm ON users USING gin (email gin_trgm_ops);

LIKE 'ann%'    -> Index Scan   cost=0.43..28     0.4 ms
LIKE '%@x.com' -> Seq Scan     cost=0..18,420  184.0 ms

-- Case sensitivity matters too: LIKE is case-sensitive in PostgreSQL,
-- so ILIKE (or a LOWER() index) is what most searches actually want.