Why Is My SQL Query Slow? Common Performance Pitfalls and How to Fix Them

Apr 14, 2026

Database server

Problem

When I ran a simple query on my orders table, it took 15 seconds to return results:

SELECT * FROM orders WHERE DATE(order_date) = '2024-01-01';
-- Execution time: 15.2 seconds
-- Rows returned: 847

I thought I had an index on order_date, so why is it so slow?

Environment

PostgreSQL 14
Orders table: 2 million rows
Index on order_date column

What Happened?

I created an index on the order_date column hoping it would speed up date-based queries:

CREATE INDEX idx_order_date ON orders(order_date);

But when I queried with a function in the WHERE clause, the database ignored my index completely. The database had to evaluate DATE(order_date) for every single row - that’s 2 million function calls.

Here’s what the query plan showed:

EXPLAIN SELECT * FROM orders WHERE DATE(order_date) = '2024-01-01';

-- Result: Seq Scan on orders  (cost=0.00..58432.00 rows=847 width=104)
-- Filter: date(order_date) = '2024-01-01'

“Seq Scan” means full table scan - my index wasn’t used at all.

How to Fix It

Fix #1: Remove the Function from WHERE Clause

I rewrote the query to compare directly:

SELECT * FROM orders WHERE order_date = '2024-01-01';
-- Execution time: 0.03 seconds

Now the database uses my index:

EXPLAIN SELECT * FROM orders WHERE order_date = '2024-01-01';

-- Result: Index Scan using idx_order_date on orders  (cost=0.00..8.47 rows=1 width=104)

“Index Scan” - exactly what I wanted.

Fix #2: Add WHERE Clause to Limit Data

Another common mistake - I was retrieving all customers when I only needed one city:

SELECT customerName FROM Customer;
-- Returns 50,000 rows

I added a WHERE clause:

SELECT customerName FROM Customer WHERE city = 'Delhi';
-- Returns 234 rows - much faster

Fix #3: Index Foreign Key Columns

I noticed my JOIN queries were slow because the foreign key column wasn’t indexed:

CREATE INDEX idx_orders_customer_fk ON orders(customer_id);

This speeds up queries like:

SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

The Reason

The key reason for slow queries:

Functions in WHERE clauses - The database can’t use an index when you apply a function to the indexed column. It must compute the function for every row.
Missing WHERE clause - Retrieving all rows when you only need a subset wastes time and memory.
Missing indexes on foreign keys - JOIN operations need indexes on both sides of the relationship.

Quick Checklist for Slow Queries

┌─────────────────────────────────────────────────────────────┐
│                    Slow Query Checklist                      │
├─────────────────────────────────────────────────────────────┤
│ ✓ Avoid SELECT * - specify columns you need                 │
│ ✓ Remove functions from WHERE on indexed columns            │
│ ✓ Add WHERE clause to filter early                          │
│ ✓ Index columns used in WHERE, JOIN, ORDER BY               │
│ ✓ Index foreign key columns                                 │
│ ✓ Check EXPLAIN to see if index is used                     │
└─────────────────────────────────────────────────────────────┘

Summary

In this post, I showed how to fix three common SQL query performance pitfalls. The key point is: functions in WHERE clauses prevent index usage, so rewrite your queries to let the database use your indexes.

Final Words + More Resources

My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

👨‍💻 GeeksforGeeks: SQL Query Optimization

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!