Answer

The index isn't used because `LIKE '%@example.com'` has a **leading wildcard**. B-tree indexes only work for prefix matches (`LIKE 'foo%'`), not suffix/infix patterns. **Solutions, from simplest to most powerful:** 1. **Reverse index for suffix search:** ```sql CREATE INDEX idx_email_reverse ON users (reverse(email)); -- Query: SELECT * FROM users WHERE reverse(email) LIKE reverse('%@example.com'); -- Becomes: WHERE reverse(email) LIKE 'moc.elpmaxe@%' ← prefix match, uses index ``` 2. **Functional index on domain:** ```sql CREATE INDEX idx_email_domain ON users (split_part(email, '@', 2)); -- Query: SELECT * FROM users WHERE split_part(email, '@', 2) = 'example.com'; ``` This is the cleanest if you're always searching by domain. 3. **pg_trgm for general LIKE/ILIKE:** ```sql CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_email_trgm ON users USING gin (email gin_trgm_ops); -- Now this uses the index: SELECT * FROM users WHERE email LIKE '%@example.com'; ``` `pg_trgm` GIN indexes support arbitrary substring matches including leading wildcards. This is the most flexible solution. 4. **Full-text search** — overkill for emails, but worth knowing for general text columns. For 2M rows, option 2 or 3 will bring your query from 10+ seconds to single-digit milliseconds.

a4237fa5-3b74-43ac-898e-85c6d5cfbd8a

The index isn't used because LIKE '%@example.com' has a leading wildcard. B-tree indexes only work for prefix matches (LIKE 'foo%'), not suffix/infix patterns.

Solutions, from simplest to most powerful:

  1. Reverse index for suffix search:
CREATE INDEX idx_email_reverse ON users (reverse(email));
-- Query:
SELECT * FROM users WHERE reverse(email) LIKE reverse('%@example.com');
-- Becomes: WHERE reverse(email) LIKE 'moc.elpmaxe@%'  ← prefix match, uses index
  1. Functional index on domain:
CREATE INDEX idx_email_domain ON users (split_part(email, '@', 2));
-- Query:
SELECT * FROM users WHERE split_part(email, '@', 2) = 'example.com';

This is the cleanest if you're always searching by domain.

  1. pg_trgm for general LIKE/ILIKE:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_email_trgm ON users USING gin (email gin_trgm_ops);
-- Now this uses the index:
SELECT * FROM users WHERE email LIKE '%@example.com';

pg_trgm GIN indexes support arbitrary substring matches including leading wildcards. This is the most flexible solution.

  1. Full-text search — overkill for emails, but worth knowing for general text columns.

For 2M rows, option 2 or 3 will bring your query from 10+ seconds to single-digit milliseconds.