postgres-indexes

# PostgreSQL Indexes Skill

Patterns for creating and optimizing PostgreSQL indexes.

## Index Types

### B-tree Index (Default)

```sql
-- Most common, good for equality and range queries
CREATE INDEX idx_users_email ON users(email);

-- Compound index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
```

### Hash Index

```sql
-- Fast for equality comparisons only
CREATE INDEX idx_users_username_hash ON users USING HASH(username);
```

### GIN Index (Generalized Inverted)

```sql
-- For arrays
CREATE INDEX idx_products_tags ON products USING GIN(tags);

-- For JSONB
CREATE INDEX idx_products_metadata ON products USING GIN(metadata);

-- For full-text search
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);
```

### GiST Index (Generalized Search Tree)

```sql
-- For geometric types
CREATE INDEX idx_locations_point ON locations USING GIST(location);

-- For range types
CREATE INDEX idx_events_duration ON events USING GIST(duration);

-- For full-text search (alternative to GIN)
CREATE INDEX idx_articles_search_gist ON articles USING GIST(search_vector);
```

### BRIN Index (Block Range)

```sql
-- Efficient for large, naturally ordered tables
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);

-- Very compact, good for append-only tables
CREATE INDEX idx_events_timestamp ON events USING BRIN(timestamp);
```

## Partial Indexes

```sql
-- Index only active users
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Index only recent orders
CREATE INDEX idx_recent_orders ON orders(created_at)
WHERE created_at > '2024-01-01';

-- Index only non-null values
CREATE INDEX idx_users_phone ON users(phone) WHERE phone IS NOT NULL;
```

## Expression Indexes

```sql
-- Index on lower case email
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Index on date part
CREATE INDEX idx_orders_date ON orders(DATE(created