SQL-Abfrageoptimierung: von langsam zu schnell

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2020-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;

-- ❌ Seq Scan on a million-row table — every row is read
-- Seq Scan on orders (cost=0.00..35421.00 rows=1000000)
--   Filter: (status = 'pending')
--   Rows Removed by Filter: 990000
-- Planning Time: 0.2ms
-- Execution Time: 2340ms
 
-- ✅ After adding an index — only relevant rows are read
-- Index Scan using idx_orders_status on orders (cost=0.42..825.00 rows=10000)
--   Index Cond: (status = 'pending')
-- Planning Time: 0.3ms
-- Execution Time: 12ms

-- Single column index — for exact match and range queries
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_created ON orders (created_at);
 
-- Composite index — column order matters
-- This index serves: WHERE status = X AND created_at > Y
-- It does NOT efficiently serve: WHERE created_at > Y (alone)
CREATE INDEX idx_orders_status_created
  ON orders (status, created_at);
 
-- Partial index — indexes only rows matching a condition
-- Much smaller than a full index when most data is filtered out
CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

-- ❌ Index on a low-cardinality column — not useful
-- A boolean column has 2 values. The index doesn't help.
CREATE INDEX idx_users_active ON users (is_active);
 
-- ✅ Partial index on the rare case — small and effective
CREATE INDEX idx_users_inactive
  ON users (email)
  WHERE is_active = false;
-- Only indexes the 2% of users who are inactive

-- Query: WHERE status = 'shipped' AND created_at > '2020-06-01'
-- ❌ Range column first — index partially used
CREATE INDEX idx_wrong ON orders (created_at, status);
 
-- ✅ Equality column first — index fully utilized
CREATE INDEX idx_right ON orders (status, created_at);

-- ❌ Fetches all 30 columns — most are unused
SELECT * FROM users WHERE department = 'engineering';
 
-- ✅ Only fetch what you need — enables index-only scans
SELECT id, name, email FROM users WHERE department = 'engineering';

-- ❌ Correlated subquery — runs once per row in the outer query
SELECT u.name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;
 
-- ✅ JOIN with aggregation — single pass
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- ❌ IN with subquery — materializes entire result set
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);
 
-- ✅ EXISTS — stops at first match per row
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 1000
);

// ❌ N+1 — 1 query for users + N queries for orders
const users = await db.query('SELECT * FROM users LIMIT 100');
for (const user of users) {
  user.orders = await db.query(
    'SELECT * FROM orders WHERE user_id = $1',
    [user.id]
  );
}
// Total: 101 queries
 
// ✅ Single query with JOIN
const usersWithOrders = await db.query(`
  SELECT u.*, json_agg(o.*) as orders
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id
  GROUP BY u.id
  LIMIT 100
`);
// Total: 1 query

// ❌ Prisma — lazy loading triggers N+1
const users = await prisma.user.findMany();
// Accessing users[0].orders triggers another query
 
// ✅ Prisma — include prevents N+1
const users = await prisma.user.findMany({
  include: { orders: true },
});

-- ❌ Offset pagination — gets slower with higher pages
-- Page 1000 scans 100,000 rows to return 100
SELECT * FROM orders ORDER BY created_at DESC
OFFSET 99900 LIMIT 100;
 
-- ✅ Cursor-based pagination — constant performance
-- Uses the last seen value as the starting point
SELECT * FROM orders
WHERE created_at < '2020-12-15T10:30:00Z'
ORDER BY created_at DESC
LIMIT 100;

-- Cursor for non-unique sort column
SELECT * FROM orders
WHERE (created_at, id) < ('2020-12-15T10:30:00Z', 'abc-123')
ORDER BY created_at DESC, id DESC
LIMIT 100;

-- PostgreSQL: enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = '200';
-- Logs every query taking longer than 200ms
 
-- Find the heaviest queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;