nw-query-optimization

Query Optimization

Cost-Based Optimization

Modern relational DBs use cost-based optimizers (CBO): generate plan candidates -> estimate cost via statistics (row counts, distributions, selectivity) -> select lowest I/O/CPU/memory plan. Stale statistics lead to suboptimal plans.

Execution Plan Analysis

Validate optimization with EXPLAIN before and after changes.

-- PostgreSQL (add ANALYZE for actual runtime stats)
EXPLAIN ANALYZE SELECT order_id, total FROM orders WHERE customer_id = 12345;
-- MySQL: EXPLAIN FORMAT=JSON ... | SQL Server: SET STATISTICS IO ON

Key indicators: Seq Scan/Table Scan = missing index | Index Scan/Seek = efficient | Hash Join = large equality joins | Nested Loop = small/indexed inner | Merge Join = pre-sorted inputs | Sort = watch disk spills

Indexing Strategies

B-Tree (Default)

Supports: equality, range, sorting, prefix matching | O(log n) lookup | General-purpose, all major DBs default

Hash

Equality only | O(1) lookup | High-cardinality exact-match | No range/sorting/pattern support

Covering Indexes

Include all query columns in index -> eliminates table access (index-only scan) | Trade-off: larger index, slower writes

-- Covering index for: SELECT name, email FROM users WHERE status = 'active'
CREATE INDEX idx_users_status_covering ON users(status) INCLUDE (name, email);

PostgreSQL Specialized

• GiST: Geometric data, full-text search, nearest-neighbor
• GIN: Arrays, full-text search, JSONB queries
• BRIN: Large tables with physically correlated data (timestamps), minimal storage
• SP-GiST: Non-balanced structures, point-based geometric queries

Compound Index Design

Order by: 1. Equality conditions first (highest selectivity) | 2. Sort columns second | 3. Range conditions last

MongoDB ESR Rule

Equality-Sort-Range ordering for compound indexes:

// Query: status = "A", qty > 20, sorted by item
// Optimal index:
db.collection.createIndex({ status: 1, item: 1, qty: 1 })
//                          E(quality)  S(ort)   R(ange)