Code snippets — Database Indexing Strategies

snippet-10.sql

-- After creating the foreign key
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

-- Always add this index
CREATE INDEX idx_orders_user_id ON orders(user_id);

snippet-11.sql

REINDEX INDEX CONCURRENTLY idx_orders_user_id;

snippet-12.sql

-- This CANNOT use an index on created_at
WHERE DATE(created_at) = '2026-01-01'

-- This CAN
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'

snippet-2.sql

CREATE INDEX idx_sessions_token ON sessions USING HASH (token);

snippet-3.sql

-- Full text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', content));

-- JSONB
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);

snippet-4.sql

-- This index serves both queries below efficiently
CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at);

-- Uses the index
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- Also uses the index (partial)
SELECT * FROM orders WHERE user_id = 42;

-- Does NOT use the index efficiently
SELECT * FROM orders WHERE status = 'pending';

snippet-5.sql

-- Query needs user_id, status, and total
SELECT user_id, status, total FROM orders WHERE user_id = 42;

-- Covering index includes all needed columns
CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (status, total);

snippet-6.sql

-- Only index unprocessed jobs (90% are already processed)
CREATE INDEX idx_jobs_pending ON jobs(created_at)
  WHERE status = 'pending';

-- Only index active users
CREATE INDEX idx_users_active_email ON users(email)
  WHERE deleted_at IS NULL;

snippet-7.sql

-- Case-insensitive email lookup
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Now this query is fast
SELECT * FROM users WHERE LOWER(email) = LOWER('User@Example.com');

snippet-8.sql

-- Enable timing
\timing

-- See the query plan
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- Look for:
-- "Seq Scan" — full table scan (bad for large tables)
-- "Index Scan" — good
-- "Index Only Scan" — best
-- actual time vs estimated time

snippet-9.sql

-- Find the most time-consuming queries
SELECT query, mean_exec_time, calls, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

snippet.sql

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created ON orders(created_at);