SQL Indexing and Performance

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 42;

# Inspect the execution plan for a SELECT statement.

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 42;

# Run a query and collect timing or actual row information where supported.

ANALYZE orders;

# Update table statistics for better planning.

CREATE INDEX idx_users_email
ON users (email);

# Speed up frequent equality or range predicates.

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);

# Create an index that supports filtering and ordering efficiently.

-- Less index-friendly
SELECT *
FROM users
WHERE LOWER(email) = 'a@example.com';

-- Prefer normalized storage or computed/indexed support where available
SELECT *
FROM users
WHERE email = 'a@example.com';

# Preserve index usability in predicates.

SELECT *
FROM posts
WHERE (published_at, post_id) < (TIMESTAMP "2025-03-01 10:00:00", 12345)
ORDER BY published_at DESC, post_id DESC
FETCH FIRST 20 ROWS ONLY;

# Paginate by a stable cursor instead of a large OFFSET.

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
  FROM users
)
SELECT *
FROM ranked
WHERE rn = 1;

# Keep one row per business key and remove duplicates logically.

SELECT *
FROM customers
WHERE first_name LIKE '%john%'
   OR last_name LIKE '%john%'
   OR email LIKE '%john%';

# Apply a simple wildcard search over several text fields.

SELECT email, COUNT(*) AS duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

# Detect repeated values that should be unique.

SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

# Detect child rows missing a valid parent.

WITH ranked AS (
  SELECT user_id, email,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) AS rn
  FROM users
)
DELETE FROM users
WHERE user_id IN (
  SELECT user_id
  FROM ranked
  WHERE rn > 1
);

# Keep one row per key and delete the extras.