🤖 Query Optimization

PostgreSQL ma query performance improve karva mate different techniques che. Let's explore kari ae.

EXPLAIN samajvo

EXPLAIN command query execution plan dekhade che - kevi rite PostgreSQL query execute karse ae.

Basic EXPLAIN

-- Simple execution plan
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- Actual execution time sathe
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- Detailed output - buffers, timing, everything
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) 
SELECT * FROM users WHERE email = 'user@example.com';

Output samajvo

Seq Scan on users  (cost=0.00..18334.00 rows=1 width=264)
  Filter: (email = 'user@example.com'::text)

• Seq Scan: Sequential scan (full table scan - slow for large tables)
• cost: Startup cost .. Total cost (lower is better)
• rows: Kitna rows return thashe (estimated)
• width: Average row size in bytes

Common Query Issues

N+1 Query Problem

Aa problem ORM use karta vadhare aave che.

❌ Bad Approach:

-- Pehla badha users fetch karo
SELECT * FROM users;

-- Tyar baad darek user mate orders (N queries!)
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
-- ... N vadhare queries

✅ Good Approach - Single JOIN query:

-- Ek j query ma badhu
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

SELECT * Avoid Karo

❌ Bad:

-- Badha columns fetch kare che (waste)
SELECT * FROM large_table WHERE id = 100;

✅ Good:

-- Faqat jaruri columns
SELECT id, name, email FROM large_table WHERE id = 100;

Index Optimization

Indexes query performance dramatically improve kare che, but sahi jagya pe use karva joiye.

Kada Index Create Karvu

-- WHERE clause ma use thata columns
CREATE INDEX idx_users_email ON users(email);

-- JOIN conditions mate
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- ORDER BY mate
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Multiple columns mate composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

Index Types

PostgreSQL ma different types of indexes che:

-- B-tree (default, most common)
CREATE INDEX idx_btree ON table_name(column_name);

-- Hash (equality checks mate j)
CREATE INDEX idx_hash ON table_name USING HASH(column_name);

-- GiST (geometric, full-text)
CREATE INDEX idx_gist ON table_name USING GIST(column_name);

-- GIN (arrays, JSONB, full-text)
CREATE INDEX idx_gin ON table_name USING GIN(jsonb_column);

-- BRIN (very large tables with natural ordering)
CREATE INDEX idx_brin ON logs USING BRIN(timestamp);

Partial Indexes

Faqat specific rows mate index - space bachay:

-- Faqat active users mate index
CREATE INDEX idx_active_users ON users(email) 
WHERE status = 'active';

-- Recent records j index karo
CREATE INDEX idx_recent_orders ON orders(created_at) 
WHERE created_at > '2024-01-01';

Covering Indexes

Index ma extra columns include karo - Index Only Scan possible bane:

-- email search ma name ane created_at pan joiye to
CREATE INDEX idx_users_email_covering ON users(email) 
INCLUDE (name, created_at);

-- Have aa query index thi j answer thai shake (table access nai joitu)
SELECT name, created_at 
FROM users 
WHERE email = 'user@example.com';

Query Writing Best Practices

CTEs (Common Table Expressions)

Complex queries ne readable banavo:

-- Step by step breakdown
WITH active_users AS (
  SELECT id, name FROM users WHERE status = 'active'
),
recent_orders AS (
  SELECT user_id, COUNT(*) as order_count 
  FROM orders 
  WHERE created_at > CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT 
  u.name, 
  COALESCE(o.order_count, 0) as orders
FROM active_users u
LEFT JOIN recent_orders o ON u.id = o.user_id;

EXISTS vs IN

Large datasets mate EXISTS often better performance ape che:

✅ EXISTS (Recommended):

-- EXISTS use karo - better performance
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

❌ IN (Can be slower):

-- IN slow thai shake large subqueries sathe
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

Pagination - LIMIT with ORDER BY

-- Basic pagination
SELECT id, name FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Large offsets slow che, keyset pagination better:
SELECT id, name FROM users
WHERE created_at < '2024-01-01 00:00:00'
ORDER BY created_at DESC
LIMIT 20;

Slow Queries Find Karo

Log Slow Queries

postgresql.conf ma configure karo:

# 1 second thi vadhare wala queries log karo
log_min_duration_statement = 1000

# Useful log format
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

pg_stat_statements Extension

Aa extension query statistics track kare che:

-- Extension enable karo
CREATE EXTENSION pg_stat_statements;

-- Slowest queries joyo
SELECT 
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Most frequently executed queries
SELECT query, calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- Reset statistics
SELECT pg_stat_statements_reset();

Useful Optimization Tips

1. EXPLAIN ANALYZE Always - Optimize karta pehla actual plan joyo
2. Index Smartly - Har column pe index na banavo, maintenance cost che
3. VACUUM Regularly - Statistics update mate ane dead tuples clean karva mate
4. Batch Operations - Bulk inserts/updates use karo where possible
5. Connection Pooling - PgBouncer use karo connection overhead reduce karva
6. Prepared Statements - Repeated queries mate planning overhead bachay
7. Monitor Statistics - pg_stat tables regular check karo

Example: Index vs No Index

-- Without index
EXPLAIN ANALYZE 
SELECT * FROM users WHERE email = 'test@example.com';
-- Seq Scan: 1234.56 ms

-- Index create karo
CREATE INDEX idx_users_email ON users(email);

-- With index
EXPLAIN ANALYZE 
SELECT * FROM users WHERE email = 'test@example.com';
-- Index Scan: 0.12 ms  (10000x faster!)

---

Next Steps: Configuration tuning shikhva mate (coming soon)