EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- Seq Scan on users (cost=0.00..1234.56)
SELECT * FROM users WHERE email = 'user@example.com';

CREATE INDEX idx_users_email ON users(email);
-- Now: Index Scan using idx_users_email

SELECT * FROM posts;  -- Fetches all columns

SELECT id, title, created_at FROM posts;  -- Only needed columns

-- Fetches posts
SELECT * FROM posts;
-- Then for each post:
SELECT * FROM users WHERE id = ?;

-- Single query with JOIN
SELECT posts.*, users.name 
FROM posts 
JOIN users ON posts.user_id = users.id;

SELECT * FROM posts ORDER BY created_at DESC;  -- Returns all rows

SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;

CREATE INDEX idx_users_email ON users(email);

-- For: WHERE user_id = ? AND created_at > ?
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

-- For: SELECT id, title FROM posts WHERE user_id = ?
CREATE INDEX idx_posts_user_id_title ON posts(user_id) INCLUDE (title);

CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- For: WHERE LOWER(email) = 'user@example.com'

-- Slow
SELECT * FROM users WHERE id IN (SELECT user_id FROM posts);

-- Faster
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM posts p WHERE p.user_id = u.id
);

-- Slow
SELECT * FROM posts WHERE user_id IN (
    SELECT id FROM users WHERE is_active = true
);

-- Faster
SELECT p.* FROM posts p
JOIN users u ON p.user_id = u.id
WHERE u.is_active = true;

-- Bad: Can't use index
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- Good: Can use index
SELECT * FROM users 
WHERE created_at >= '2024-01-01' 
AND created_at < '2025-01-01';

-- Slow: Removes duplicates
SELECT id FROM posts UNION SELECT id FROM drafts;

-- Fast: No duplicate removal
SELECT id FROM posts UNION ALL SELECT id FROM drafts;

-- Bad: Large intermediate result
SELECT * FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.created_at > '2024-01-01';

-- Good: Filter first
SELECT * FROM posts p
WHERE p.created_at > '2024-01-01'
JOIN users u ON p.user_id = u.id;

-- INNER JOIN: Only matching rows
SELECT * FROM posts p
INNER JOIN users u ON p.user_id = u.id;

-- LEFT JOIN: All posts, even without user
SELECT * FROM posts p
LEFT JOIN users u ON p.user_id = u.id;

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_users_id ON users(id);  -- Usually PK already indexed

-- Slow for page 1000
SELECT * FROM posts 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 20000;

-- First page
SELECT * FROM posts 
ORDER BY created_at DESC, id DESC 
LIMIT 20;

-- Next page (using last created_at and id)
SELECT * FROM posts 
WHERE (created_at, id) < ('2024-01-01 12:00:00', 12345)
ORDER BY created_at DESC, id DESC 
LIMIT 20;

CREATE INDEX idx_posts_user_id ON posts(user_id);

SELECT user_id, COUNT(*) 
FROM posts 
GROUP BY user_id;

-- Good
SELECT user_id, COUNT(*) 
FROM posts 
WHERE created_at > '2024-01-01'
GROUP BY user_id;

SELECT user_id, COUNT(*) as post_count
FROM posts 
GROUP BY user_id
HAVING COUNT(*) > 10;

-- Bad: Runs subquery for each row
SELECT * FROM users u
WHERE (SELECT COUNT(*) FROM posts WHERE user_id = u.id) > 10;

-- Good: Single query
SELECT u.* FROM users u
JOIN (
    SELECT user_id, COUNT(*) as post_count
    FROM posts
    GROUP BY user_id
    HAVING COUNT(*) > 10
) p ON u.id = p.user_id;

CREATE MATERIALIZED VIEW user_post_counts AS
SELECT user_id, COUNT(*) as post_count
FROM posts
GROUP BY user_id;

-- Refresh periodically
REFRESH MATERIALIZED VIEW user_post_counts;

# Cache expensive queries
@cache(ttl=300)
def get_popular_posts():
    return db.query("SELECT * FROM posts ORDER BY views DESC LIMIT 10")

-- Add tsvector column
ALTER TABLE posts ADD COLUMN search_vector tsvector;

-- Update with trigger
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);

-- Search
SELECT * FROM posts 
WHERE search_vector @@ to_tsquery('postgresql & optimization');

-- Bad: Multiple inserts
INSERT INTO users (name) VALUES ('User 1');
INSERT INTO users (name) VALUES ('User 2');

-- Good: Single insert
INSERT INTO users (name) VALUES 
('User 1'),
('User 2'),
('User 3');

-- Use CASE for conditional updates
UPDATE posts 
SET status = CASE 
    WHEN views > 1000 THEN 'popular'
    WHEN views > 100 THEN 'normal'
    ELSE 'new'
END;

# Use connection pool
from sqlalchemy import create_engine

engine = create_engine(
    'postgresql://user:pass@localhost/db',
    pool_size=20,
    max_overflow=10
)

-- PostgreSQL: Enable slow query log
ALTER DATABASE mydb SET log_min_duration_statement = 1000;  -- 1 second

-- View pg_stat_statements
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- Unused indexes
ORDER BY pg_relation_size(indexrelid) DESC;

SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;