Not all indexes are created equal. A B-tree index is like a phone book - great for finding names alphabetically. A Hash index is like a locker number - instant lookup but can't find "all lockers between 100-200". A GIN index is like a book's index - finds all pages mentioning a word. Choose wrong and your query crawls. Choose right and it flies.

Think of it this way: You wouldn't use a hammer to tighten a screw. Same with indexes - each type is a specialized tool for specific query patterns.

**1. B-tree Indexes (Balanced Tree) - The Swiss Army Knife**

B-tree is the default for good reason. It handles 90% of use cases.

```mermaid

graph TD

A[Root Node<br/>50, 100] --> B[Leaf 1-49]

A --> C[Leaf 50-99]

A --> D[Leaf 100+]

B --> E[Data: 1,5,12,23,45]

C --> F[Data: 52,67,78,89,95]

D --> G[Data: 105,123,156,189]

style A fill:#e3f2fd

style B fill:#fff3e0

style C fill:#fff3e0

style D fill:#fff3e0

style E fill:#e8f5e9

style F fill:#e8f5e9

style G fill:#e8f5e9

```

**How B-tree Works:**

- Tree structure with sorted keys

- Each node has multiple keys and pointers

- Leaf nodes contain actual data pointers

- Height typically 3-4 levels even for millions of rows

- Supports: =, <, >, <=, >=, BETWEEN, LIKE 'prefix%', ORDER BY

**When to Use:**

- Range queries: WHERE age BETWEEN 25 AND 35

- Sorting: ORDER BY created_at DESC

- Prefix matching: WHERE email LIKE 'john%'

- Most common queries (default choice)

**Real Example - E-commerce Orders:**

```sql

-- Perfect for B-tree

CREATE INDEX idx_order_date ON orders(order_date);

SELECT * FROM orders

WHERE order_date >= '2024-01-01'

AND order_date < '2024-02-01'

ORDER BY order_date DESC;

-- Uses index for both filtering AND sorting