> ## Documentation Index
> Fetch the complete documentation index at: https://sonamu.cartanova.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Query Optimization

> Optimizing database queries

This guide covers effective query optimization methods using the Puri query builder.

## Query Performance Fundamentals

### 1. Select Only Required Columns

```typescript theme={null}
// Bad example - selecting all columns
const users = await UserModel.getPuri("r").table("users").select("*");

// Good example - selecting only required columns
const users = await UserModel.getPuri("r").table("users").select("id", "email", "name");
```

**Performance Benefits:**

* Reduced network transfer
* Reduced memory usage
* Index-only scans possible

### 2. WHERE Clause Optimization

```typescript theme={null}
// Query utilizing indexes
const activeUsers = await UserModel.getPuri("r").table("users").select("id", "email").where({ status: "active" }); // Index needed on status column

// Query that cannot use indexes - avoid
const users = await UserModel.getPuri("r").table("users")
  .select("id", "email")
  .whereRaw("LOWER(email) = ?", ["test@example.com"]); // Function usage
```

### 3. Using LIMIT

```typescript theme={null}
// Limit results with LIMIT
const recentUsers = await UserModel.getPuri("r").table("users")
  .select("id", "name", "created_at")
  .orderBy("created_at", "desc")
  .limit(10); // Top 10 only
```

## Index Utilization

### Single Column Index

```typescript theme={null}
// Add index in Entity definition
{
  "name": "User",
  "properties": [
    {
      "name": "email",
      "type": "varchar",
      "index": true  // Create index
    },
    {
      "name": "status",
      "type": "varchar",
      "index": true
    }
  ]
}
```

```typescript theme={null}
// Query utilizing index
const user = await UserModel.getPuri("r").table("users")
  .select("id", "name")
  .where({ email: "test@example.com" }) // Uses email index
  .first();
```

### Composite Index

```typescript theme={null}
// Entity definition
{
  "name": "Order",
  "properties": [
    {
      "name": "user_id",
      "type": "int"
    },
    {
      "name": "status",
      "type": "varchar"
    }
  ],
  "indexes": [
    {
      "fields": ["user_id", "status"],  // Composite index
      "unique": false
    }
  ]
}
```

```typescript theme={null}
// Utilizing composite index
const userOrders = await OrderModel.getPuri("r").table("orders").select("id", "total").where({
  user_id: 1,
  status: "completed", // Uses user_id + status composite index
});
```

### Verifying Index Effectiveness

```typescript theme={null}
// Check execution plan with PostgreSQL EXPLAIN
const query = UserModel.getPuri("r").table("users").select("id", "email").where({ status: "active" }).toQuery();

console.log(query);

// Execute in psql
// EXPLAIN ANALYZE SELECT id, email FROM users WHERE status = 'active';
```

## JOIN Optimization

### INNER JOIN vs LEFT JOIN

```typescript theme={null}
// INNER JOIN - only data with relationships
const ordersWithUser = await OrderModel.getPuri("r").table("orders")
  .select("orders.id", "orders.total", "users.name")
  .join("users", "users.id", "orders.user_id"); // INNER JOIN

// LEFT JOIN - include even without relationships
const usersWithOrders = await UserModel.getPuri("r").table("users")
  .select("users.id", "users.name", "orders.total")
  .leftJoin("orders", "orders.user_id", "users.id"); // LEFT JOIN
```

### JOIN Condition Optimization

```typescript theme={null}
// Bad example - filtering after join
const result = await OrderModel.getPuri("r").table("orders")
  .select("orders.*", "users.name")
  .join("users", "users.id", "orders.user_id")
  .where({ "users.status": "active" }); // Filtering after join

// Good example - include filter in join condition
const result = await OrderModel.getPuri("r").table("orders")
  .select("orders.*", "users.name")
  .join("users", (join) => {
    join.on("users.id", "orders.user_id").andOnVal("users.status", "active");
  });
```

## Aggregation Query Optimization

### COUNT Optimization

```typescript theme={null}
// Inefficient - fetch all rows then count
const users = await UserModel.getPuri("r").table("users").select("*");
const count = users.length;

// Efficient - count in DB
const result = await UserModel.getPuri("r").table("users")
  .select({ count: Puri.count() })
  .where({ status: "active" });
const count = result[0].count;
```

### GROUP BY Optimization

```typescript theme={null}
// Order statistics
const orderStats = await OrderModel.getPuri("r").table("orders")
  .select({
    user_id: "user_id",
    order_count: Puri.count(),
    total_amount: Puri.sum("total"),
  })
  .where({ status: "completed" })
  .groupBy("user_id")
  .having("order_count", ">", 10); // More than 10 orders
```

## Pagination Optimization

### Offset-based (Small Datasets)

```typescript theme={null}
// Basic pagination
async function getUsers(page: number, limit: number) {
  const offset = (page - 1) * limit;

  return UserModel.getPuri("r").table("users")
    .select("id", "name", "email")
    .orderBy("created_at", "desc")
    .offset(offset)
    .limit(limit);
}
```

### Cursor-based (Large Datasets)

```typescript theme={null}
// Cursor-based pagination (faster)
async function getUsersCursor(lastId?: number, limit: number = 20) {
  const query = UserModel.getPuri("r").table("users")
    .select("id", "name", "email", "created_at")
    .orderBy("id", "desc")
    .limit(limit);

  if (lastId) {
    query.where("id", "<", lastId); // After last ID
  }

  return query;
}

// Usage
const firstPage = await getUsersCursor(undefined, 20);
const secondPage = await getUsersCursor(firstPage[firstPage.length - 1].id, 20);
```

**Advantages:**

* Consistent performance regardless of offset size
* Uses index scan only
* Stable with real-time data changes

## Subquery Optimization

### EXISTS vs IN

```typescript theme={null}
// IN - loads subquery results into memory
const usersWithOrders = await UserModel.getPuri("r").table("users")
  .select("id", "name")
  .whereIn("id", knex.select("user_id").from("orders").where({ status: "completed" }));

// EXISTS - only checks existence (faster)
const usersWithOrders = await UserModel.getPuri("r").table("users")
  .select("id", "name")
  .whereRaw("EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id AND orders.status = ?)", [
    "completed",
  ]);
```

### JOIN Instead of Subquery

```typescript theme={null}
// Subquery - avoid
const users = await UserModel.getPuri("r").table("users").select({
  id: "id",
  name: "name",
  order_count: Puri.rawNumber("(SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id)"),
});

// LEFT JOIN + GROUP BY (faster)
const users = await UserModel.getPuri("r").table("users")
  .select({
    id: "users.id",
    name: "users.name",
    order_count: Puri.count("orders.id"),
  })
  .leftJoin("orders", "orders.user_id", "users.id")
  .groupBy("users.id");
```

## Batch Processing

### Bulk Query Optimization

```typescript theme={null}
// Bad - individual queries in loop
async function getUsersWithDetails(userIds: number[]) {
  const results = [];
  for (const id of userIds) {
    const user = await UserModel.findById(id);
    results.push(user);
  }
  return results;
}

// Good - query all at once with whereIn
async function getUsersWithDetails(userIds: number[]) {
  return UserModel.getPuri("r").table("users").select("*").whereIn("id", userIds);
}
```

### Chunk Processing

```typescript theme={null}
// Processing large data
async function processAllUsers() {
  const chunkSize = 1000;
  let offset = 0;

  while (true) {
    const users = await UserModel.getPuri("r").table("users").select("id", "email").offset(offset).limit(chunkSize);

    if (users.length === 0) break;

    // Processing logic
    await processUsers(users);

    offset += chunkSize;
  }
}
```

## Query Caching

### Caching Frequently Used Queries

```typescript theme={null}
import { cache } from "sonamu";

class UserModelClass extends BaseModelClass {
  @cache({ ttl: "5m" }) // 5 minute cache
  async getActiveUsersCount(): Promise<number> {
    const result = await this.getPuri("r").table("users").select({ count: Puri.count() }).where({ status: "active" });
    return result[0].count;
  }
}
```

### Manual Cache Management

```typescript theme={null}
import { Sonamu } from "sonamu";

async function getCachedUsers() {
  const cacheKey = "users:active";

  // Check cache
  const cached = await Sonamu.cache.get(cacheKey);
  if (cached) return cached;

  // Cache miss - query DB
  const users = await UserModel.getPuri("r").table("users").select("id", "name").where({ status: "active" });

  // Store in cache (5 minutes)
  await Sonamu.cache.set(cacheKey, users, { ttl: "5m" });

  return users;
}
```

## Execution Plan Analysis

### Using EXPLAIN

```typescript theme={null}
// Generate query
const query = UserModel.getPuri("r").table("users")
  .select("users.id", "users.name", "orders.total")
  .join("orders", "orders.user_id", "users.id")
  .where({ "users.status": "active" })
  .toQuery();

console.log("SQL:", query);

// Execute in PostgreSQL
// EXPLAIN ANALYZE {query}
```

**Key Things to Check:**

* Seq Scan vs Index Scan
* Estimated rows vs actual rows
* Execution Time
* Join methods (Nested Loop, Hash Join, Merge Join)

## Performance Monitoring

### Query Logging

```typescript theme={null}
// sonamu.config.ts
export default {
  database: {
    defaultOptions: {
      debug: process.env.NODE_ENV === "development", // Query logging
    },
  },
} satisfies SonamuConfig;
```

### Detecting Slow Queries

```typescript theme={null}
class UserModelClass extends BaseModelClass {
  async findActiveUsers() {
    const start = Date.now();

    const users = await this.getPuri("r").table("users").select("*").where({ status: "active" });

    const duration = Date.now() - start;

    if (duration > 1000) {
      this.logger.warn("Slow query detected", {
        method: "findActiveUsers",
        duration: `${duration}ms`,
      });
    }

    return users;
  }
}
```

## Best Practices

### 1. Index Strategy

```typescript theme={null}
// Index frequently queried columns
// Columns used in WHERE, JOIN, ORDER BY
// Columns with high selectivity (diverse values)
// Avoid: rarely used columns
// Avoid: low selectivity columns (boolean, etc.)
```

### 2. Query Patterns

```typescript theme={null}
// Simple and clear queries
// Query only needed data
// Utilize indexes
// Avoid: complex subqueries
// Avoid: excessive JOINs
// Avoid: SELECT *
```

### 3. Database Configuration

```sql theme={null}
-- PostgreSQL configuration optimization
-- shared_buffers = 4GB  (25% of RAM)
-- effective_cache_size = 12GB  (75% of RAM)
-- work_mem = 50MB
-- maintenance_work_mem = 1GB
```

## Performance Checklist

Check when writing queries:

* [ ] SELECT only required columns
* [ ] Utilize indexes in WHERE clause
* [ ] Limit results with LIMIT
* [ ] Remove unnecessary JOINs
* [ ] Check for N+1 problems
* [ ] Consider JOIN instead of subquery
* [ ] Process aggregations in DB
* [ ] Use batch for bulk processing
* [ ] Cache frequently used queries
* [ ] Check execution plan with EXPLAIN

## Related Documentation

* [N+1 Problems](/en/troubleshooting/performance/n-plus-one-problems)
* [Caching Strategies](/en/troubleshooting/performance/caching-strategies)
* [Puri Query Builder](/en/database/puri/basic-queries)
* [Index Configuration](/en/database/migrations/migration-strategies)