7 PostgreSQL Query Mistakes That Kill Performance (And How to Fix Each)

Apr 20, 2026

The Problem: Query Mistakes Slow Down Your App

I’ve spent 15 years optimizing PostgreSQL queries. The most common issues stem from query design, not hardware. Here are 7 mistakes I see repeatedly.

Mistake #1: SELECT * Pulls Unnecessary Columns

Fetching all columns when you need 3-4 columns wastes:

Network bandwidth
Memory for result set
Index covering opportunities (can’t use covering index)

-- Wrong
SELECT * FROM orders WHERE customer_id = 123;

-- Right
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 123;

Bonus: With covering index, no table lookup needed:

CREATE INDEX idx_orders_customer_covering
ON orders (customer_id)
INCLUDE (order_id, order_date, total_amount);

Mistake #2: N+1 Query Pattern

The application loops, executing one query per iteration:

# Wrong: N+1 pattern
for order_id in order_ids:
    result = db.execute("SELECT * FROM order_items WHERE order_id = ?", order_id)

100 orders = 101 queries (1 for order_ids + 100 for items).

Fix: Single JOIN query:

-- Right: Single query
SELECT o.order_id, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_id IN (SELECT id FROM order_ids);

100 orders = 1 query.

Mistake #3: Function-Wrapped Columns Break Indexes

Wrapping column in function prevents index usage:

-- Wrong: Index can't match DATE_TRUNC result
SELECT * FROM orders
WHERE DATE_TRUNC('month', order_date) = '2023-01-01';

EXPLAIN shows Seq Scan even if order_date has index.

Fix: Rewrite to range comparison:

-- Right: Range uses index
SELECT * FROM orders
WHERE order_date >= '2023-01-01'
  AND order_date &lt; '2023-02-01';

Rule: Never wrap indexed column in function in WHERE clause.

Mistake #4: OFFSET Pagination Scans All Skipped Rows

OFFSET 10000 makes PostgreSQL read and discard 10000 rows:

-- Wrong: OFFSET reads all skipped rows
SELECT * FROM orders
ORDER BY order_date DESC
OFFSET 10000 LIMIT 100;

Page 100 = PostgreSQL reads 10,000 + 100 = 10,100 rows.

Fix: Keyset (cursor) pagination:

-- Right: Keyset pagination - only reads 100 rows
SELECT * FROM orders
WHERE order_date &lt; '2023-06-15'  -- Last row of previous page
  AND order_id &gt; 123456          -- Tiebreaker for same dates
ORDER BY order_date DESC, order_id DESC
LIMIT 100;

Each page reads exactly LIMIT rows.

Alternative: Use cursor:

DECLARE orders_cursor SCROLL CURSOR FOR
SELECT * FROM orders ORDER BY order_date DESC;

FETCH 100 FROM orders_cursor;  -- Page 1
FETCH 100 FROM orders_cursor;  -- Page 2 (no re-scanning)

Mistake #5: CTE as Optimization Barrier

PostgreSQL treats CTE as optimization fence (before v12):

-- Potentially wrong: CTE materializes separately
WITH shipped_orders AS (
  SELECT * FROM orders WHERE status = 'shipped'
)
SELECT * FROM shipped_orders JOIN order_items ...

Planner can’t push conditions into CTE or optimize join order.

Fix: Inline subquery (for PostgreSQL < 12):

-- Right: Inline allows full optimization
SELECT * FROM
(SELECT * FROM orders WHERE status = 'shipped') AS shipped_orders
JOIN order_items ...

Note: PostgreSQL 12+ has WITH ... AS MATERIALIZED (barrier) or WITH ... AS NOT MATERIALIZED (inline). Default behavior changed.

Mistake #6: Missing LIMIT on Aggregation

Aggregation without LIMIT scans entire dataset:

-- Wrong: Scans all 200M rows
SELECT category, COUNT(*)
FROM orders
GROUP BY category;

Fix: Add LIMIT if you need top N only:

-- Right: Stops early
SELECT category, COUNT(*)
FROM orders
GROUP BY category
ORDER BY COUNT(*) DESC
LIMIT 10;

Mistake #7: Implicit Cross Join from Missing WHERE

Missing WHERE condition on join creates cross product:

-- Wrong: All products x all categories
SELECT p.name, c.category_name
FROM products p
JOIN categories c ON 1=1;  -- Missing real condition

Fix: Always verify join conditions:

-- Right: Proper join condition
SELECT p.name, c.category_name
FROM products p
JOIN categories c ON p.category_id = c.id;

Complete Rewrite Example

I had a 3.5s query with multiple mistakes:

-- Original: 3.5s with multiple mistakes
SELECT * FROM orders
WHERE DATE_TRUNC('month', order_date) = '2023-01-01'
ORDER BY order_date DESC;

Rewritten to 45ms:

-- Rewritten: 45ms
SELECT order_id, customer_id, total_amount
FROM orders
WHERE order_date >= '2023-01-01'
  AND order_date &lt; '2023-02-01'
ORDER BY order_date DESC
LIMIT 1000;

Mistake Detection Checklist

-- Check for function-wrapped columns
SELECT query FROM pg_stat_statements
WHERE query LIKE '%DATE_TRUNC%' OR query LIKE '%LOWER%';

-- Check for SELECT *
SELECT query FROM pg_stat_statements
WHERE query LIKE 'SELECT * FROM%';

-- Check for high OFFSET
SELECT query FROM pg_stat_statements
WHERE query LIKE '%OFFSET%';

Summary

In this post, I showed 7 PostgreSQL query mistakes: eliminate SELECT *, consolidate N+1 to JOINs, unwrap function-wrapped columns, replace OFFSET with keyset pagination, inline CTEs (or use NOT MATERIALIZED), add LIMIT to aggregations, verify all join conditions. The key point is each fix follows a specific rewrite pattern that PostgreSQL’s planner can optimize.

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:

👨‍💻 PostgreSQL Documentation: Query Performance
👨‍💻 PostgreSQL Performance Tips

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