avoid SELECT * in application code

SELECT * fetches every column, including large ones (a text blob, a jsonb column) the caller may not need — more I/O, more network transfer, and it defeats index-only scans, which need the specific columns used to be present in the index. It's also a schema-change trap: adding a column silently changes every caller's result shape.

the N+1 query problem

The most common ORM-driven performance bug: fetching a list of N parents, then issuing one additional query per parent to fetch its related children — N+1 round trips to the database where one join or one batched query would do.
# N+1: one query per order, inside a loop
orders = db.query("SELECT * FROM orders WHERE customer_id = %s", customer_id)
for order in orders:
    items = db.query("SELECT * FROM order_items WHERE order_id = %s", order.id)  # ← N extra round trips

# fixed: one query, joined
rows = db.query('''
    SELECT o.*, i.* FROM orders o
    JOIN order_items i ON i.order_id = o.id
    WHERE o.customer_id = %s
''', customer_id)
Most ORMs have an explicit mechanism for this (SQLAlchemy's joinedload/selectinload, Django's select_related/prefetch_related) — the fix is almost never "write raw SQL instead," it's "tell the ORM to batch this relationship."

sargable predicates

"Sargable" (Search ARGument ABLE) means a condition can actually use an index. Wrapping the indexed column in a function or arithmetic on the left-hand side usually breaks that, forcing a full scan even with a perfectly good index sitting unused.
-- NOT sargable — the index on created_at can't be used, the function runs on every row first
SELECT * FROM orders WHERE DATE(created_at) = '2026-08-01';

-- sargable — a plain range comparison the index can use directly
SELECT * FROM orders WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02';

-- NOT sargable
SELECT * FROM orders WHERE amount * 1.1 > 100;

-- sargable — move the math to the constant side
SELECT * FROM orders WHERE amount > 100 / 1.1;
An expression index (see Indexing Strategies) is the escape hatch when you genuinely can't rewrite the predicate this way.

LIMIT without ORDER BY is not deterministic

LIMIT restricts row count, but without an ORDER BY, which rows you get back is unspecified and can change between runs of the identical query — a subtle correctness bug that often masquerades as a performance question ("why did pagination skip a row").

materialized views for genuinely expensive, rarely-changing aggregates

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT date_trunc('month', order_date) AS month, SUM(amount) AS total
FROM orders
GROUP BY 1;

REFRESH MATERIALIZED VIEW monthly_sales;   -- run on a schedule, or after a batch load
A regular VIEW is just a saved query, re-run every time it's selected from — no performance benefit on its own. A MATERIALIZED VIEW actually stores the result and must be explicitly refreshed; the right tool when a summary is expensive to compute and doesn't need to be perfectly real-time.

where to go from here

Query Planning & EXPLAIN ANALYZE — confirm a fix actually changed the plan, don't guess.
Indexing Strategies — the other half of most optimization work.