manus-skills/.kortix/opencode/skills/coding/postgres/references/query-patterns.md

title	description	tags
SQL Query Patterns	Common SQL anti-patterns and optimized alternatives	postgres, sql, query-optimization, n-plus-one, pagination

SQL Query Patterns

Query Structure

SELECT specific columns — avoids fetching unnecessary data and enables covering indexes:

-- Bad:
SELECT * FROM user WHERE status = 'active';
-- Good:
SELECT id, name, email FROM user WHERE status = 'active';

Subqueries → JOINs — correlated subqueries re-execute per row:

-- Bad
SELECT id, (SELECT COUNT(*) FROM order WHERE order.user_id = user.id) FROM user;
-- Good
SELECT u.id, COUNT(o.id) FROM user u LEFT JOIN order o ON o.user_id = u.id GROUP BY u.id;

Always LIMIT unbounded queries — prevent runaway result sets:

SELECT id, message FROM log WHERE level = 'error' ORDER BY created_at DESC LIMIT 100;

Avoid functions on indexed columns (SARGable) — functions prevent index usage unless a functional index exists:

-- Bad: Full table scan
SELECT * FROM user WHERE date_trunc('day', created_at) = '2023-01-01';
-- Good: Index scan
SELECT * FROM user WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02';

N+1 Detection

Queries inside loops → batch with ANY/IN:

# Bad
for uid in user_ids:
    cursor.execute("SELECT name FROM user WHERE id = %s", (uid,))
# Good (Postgres specific)
cursor.execute("SELECT id, name FROM user WHERE id = ANY(%s)", (list(user_ids),))
# Good (Standard SQL)
# cursor.execute("SELECT id, name FROM user WHERE id IN %s", (tuple(user_ids),))

ORM lazy loading → eager loading:

# Bad: N+1 — each iteration fires a query
for user in User.query.all():
    print(user.posts)
# Good
users = User.query.options(joinedload(User.posts)).all()

Query Rewrites

UNION → UNION ALL — skip deduplication when duplicates are impossible or acceptable.

IN subquery → EXISTS — EXISTS short-circuits on first match:

SELECT id, name FROM user u
WHERE EXISTS (SELECT 1 FROM order o WHERE o.user_id = u.id AND o.total > 100);

OFFSET → cursor pagination — OFFSET scans and discards rows, degrading at depth:

-- Bad: OFFSET 10000 scans 10020 rows
SELECT id, title FROM article ORDER BY created_at DESC LIMIT 20 OFFSET 10000;
-- Good: cursor-based (requires index on (created_at DESC, id DESC))
SELECT id, title FROM article
WHERE (created_at, id) < ('2025-06-15T12:00:00Z', 987654)
ORDER BY created_at DESC, id DESC LIMIT 20;