A developer wrote this query for a reporting dashboard. It returns correct results. It also takes 47 seconds on a table with 2 million rows.

The query

SELECT *
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
LEFT JOIN products p ON p.id = o.product_id
LEFT JOIN categories c ON c.id = p.category_id
WHERE YEAR(o.created_at) = 2025
  AND LOWER(u.email) LIKE '%@gmail.com'
  AND o.status IN (SELECT status FROM order_statuses WHERE is_active = true)
  AND o.total > 0
ORDER BY o.created_at DESC
LIMIT 50;

Find the performance problems, then check below.

---

The 8 problems

1. SELECT * returns everything

-- ❌ Fetches every column from 4 tables
SELECT *

-- ✅ Only what you need
SELECT o.id, o.total, o.created_at, u.name, u.email, p.name AS product_name

SELECT * across 4 joined tables might return 40+ columns when you need 6. More data = more memory = slower.

2. YEAR() function prevents index usage

-- ❌ Function on column = no index
WHERE YEAR(o.created_at) = 2025

-- ✅ Range comparison = uses index
WHERE o.created_at >= '2025-01-01' AND o.created_at < '2026-01-01'

Wrapping a column in a function means the database can’t use an index on that column. It has to evaluate YEAR() for every single row.

3. LOWER() function prevents index usage

-- ❌ Function on column = full scan
WHERE LOWER(u.email) LIKE '%@gmail.com'

-- ✅ Use a functional index or store normalized
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
-- Or better: filter without function
WHERE u.email ILIKE '%@gmail.com'  -- [PostgreSQL](/blog/what-is-postgresql/) case-insensitive

4. Leading wildcard in LIKE

-- ❌ Leading % = cannot use index, ever
LIKE '%@gmail.com'

-- ✅ If you need domain filtering, add a domain column
WHERE u.email_domain = 'gmail.com'

LIKE '%something' forces a full table scan. No index can help with a leading wildcard.

5. Subquery instead of JOIN

-- ❌ Subquery executes for each row (potentially)
AND o.status IN (SELECT status FROM order_statuses WHERE is_active = true)

-- ✅ JOIN is usually faster and more readable
INNER JOIN order_statuses os ON os.status = o.status AND os.is_active = true

Modern query planners often optimize this, but an explicit JOIN is clearer and more predictable.

6. LEFT JOIN when INNER JOIN is needed

-- ❌ LEFT JOIN keeps orders even if user/product is deleted
LEFT JOIN users u ON u.id = o.user_id
-- Then filters on u.email — which eliminates NULLs anyway

-- ✅ If you're filtering on the joined table, use INNER JOIN
INNER JOIN users u ON u.id = o.user_id

A LEFT JOIN followed by a WHERE on the joined table is logically an INNER JOIN but potentially slower because the planner might not optimize it.

7. No useful composite index

The query filters on created_at, status, and total. A composite index would help:

CREATE INDEX idx_orders_date_status_total
ON orders (created_at, status, total);

8. ORDER BY on a large result set before LIMIT

The database has to sort ALL matching rows before returning 50. With the right index, the sort can be eliminated:

-- Index that covers both the filter and the sort
CREATE INDEX idx_orders_created_desc
ON orders (created_at DESC)
WHERE total > 0;

The optimized query

SELECT o.id, o.total, o.created_at, u.name, u.email, p.name AS product_name
FROM orders o
INNER JOIN users u ON u.id = o.user_id
INNER JOIN products p ON p.id = o.product_id
INNER JOIN categories c ON c.id = p.category_id
INNER JOIN order_statuses os ON os.status = o.status AND os.is_active = true
WHERE o.created_at >= '2025-01-01'
  AND o.created_at < '2026-01-01'
  AND u.email_domain = 'gmail.com'
  AND o.total > 0
ORDER BY o.created_at DESC
LIMIT 50;

With proper indexes, this runs in under 50ms instead of 47 seconds.

How to diagnose slow queries yourself

EXPLAIN ANALYZE <your query>;

Look for:

• Seq Scan on large tables (missing index)
• Sort with high row counts (missing index for ORDER BY)
• Nested Loop with high row counts (missing join index)
• Filter removing most rows (index could pre-filter)

The query planner tells you exactly what’s slow. Read its output before guessing.

Related: PostgreSQL Cheat Sheet