Q: Leading-wildcard LIKE (PostgreSQL)

Search works and gets slower every month. A B-tree index is ordered by prefix. A leading `%` means any row could match at any position, so there is no range to seek — the only correct plan is to read every row. Fix: -- Trigram index supports leading wildcards. CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops); -- Or use full-text search when the intent is word matching, not substrings: CREATE INDEX idx_products_fts ON products USING gin (to_tsvector('english', name)); SELECT * FROM products WHERE to_tsvector('english', name) @@ plainto_tsquery('wireless');

Q: Type mismatch across a join (PostgreSQL)

Both columns are indexed. The plan shows a hash join with a full scan on one side. Postgres must cast one side to compare them, and the cast is applied per row to the column — which disqualifies that column's index for the same reason a function call does. The mismatch usually arrives via a migration that added a column as text. Fix: -- Fix the schema; a cast in the query only moves the problem. ALTER TABLE orders ALTER COLUMN user_id TYPE uuid USING user_id::uuid;

Q: OR across different columns (PostgreSQL)

Two well-indexed predicates combine into one sequential scan. A single index scan can satisfy one predicate or the other but not their union, and Postgres will often decline a BitmapOr plan when its row estimates are poor — falling back to reading the table once. Fix: -- Give the planner two independent scans to union. SELECT * FROM users WHERE email = $1 UNION SELECT * FROM users WHERE phone = $2;

Question 1

A function applied to the indexed column (PostgreSQL)

Accepted Answer

`created_at` is indexed. EXPLAIN still shows a sequential scan. The index stores `created_at`, not `DATE(created_at)`. Wrapping the column in a function means the planner cannot map the predicate onto the index, so it evaluates the expression for every row in the table. Fix: -- Keep the column bare; make the predicate a range.
SELECT * FROM events
WHERE created_at >= '2026-07-21'
  AND created_at <  '2026-07-22';

-- Or index the expression itself if the query shape is fixed:
CREATE INDEX idx_events_day ON events (DATE(created_at));

Question 2

Leading-wildcard LIKE (PostgreSQL)

Accepted Answer

Search works and gets slower every month. A B-tree index is ordered by prefix. A leading `%` means any row could match at any position, so there is no range to seek — the only correct plan is to read every row. Fix: -- Trigram index supports leading wildcards.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

-- Or use full-text search when the intent is word matching, not substrings:
CREATE INDEX idx_products_fts ON products USING gin (to_tsvector('english', name));
SELECT * FROM products WHERE to_tsvector('english', name) @@ plainto_tsquery('wireless');

Question 3

Type mismatch across a join (PostgreSQL)

Accepted Answer

Both columns are indexed. The plan shows a hash join with a full scan on one side. Postgres must cast one side to compare them, and the cast is applied per row to the column — which disqualifies that column's index for the same reason a function call does. The mismatch usually arrives via a migration that added a column as text. Fix: -- Fix the schema; a cast in the query only moves the problem.
ALTER TABLE orders
  ALTER COLUMN user_id TYPE uuid USING user_id::uuid;

Question 4

OR across different columns (PostgreSQL)

Accepted Answer

Two well-indexed predicates combine into one sequential scan. A single index scan can satisfy one predicate or the other but not their union, and Postgres will often decline a BitmapOr plan when its row estimates are poor — falling back to reading the table once. Fix: -- Give the planner two independent scans to union.
SELECT * FROM users WHERE email = $1
UNION
SELECT * FROM users WHERE phone = $2;

Performance audits for PostgreSQL

Check a file now — free

What we find most often in PostgreSQL

1. A function applied to the indexed column

2. Leading-wildcard LIKE

3. Type mismatch across a join

4. OR across different columns

An automated pass reads one file. An audit reads the system.

Related audits