Query Optimization - 🌲 Sonamu

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

Query Performance Fundamentals

1. Select Only Required Columns

// 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

// 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

// 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

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

// 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

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

// 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

// 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

// 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

// 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

// 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

// 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("COUNT(*)", ">", 10); // More than 10 orders

Pagination Optimization

Offset-based (Small Datasets)

// 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)

// 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

// 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

// 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

// 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

// 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

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

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

// 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

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

Detecting Slow Queries

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

// 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

// Simple and clear queries
// Query only needed data
// Utilize indexes
// Avoid: complex subqueries
// Avoid: excessive JOINs
// Avoid: SELECT *

3. Database Configuration

-- 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:

​Query Performance Fundamentals

​1. Select Only Required Columns

​2. WHERE Clause Optimization

​3. Using LIMIT

​Index Utilization

​Single Column Index

​Composite Index

​Verifying Index Effectiveness

​JOIN Optimization

​INNER JOIN vs LEFT JOIN

​JOIN Condition Optimization

​Aggregation Query Optimization

​COUNT Optimization

​GROUP BY Optimization

​Pagination Optimization

​Offset-based (Small Datasets)

​Cursor-based (Large Datasets)

​Subquery Optimization

​EXISTS vs IN

​JOIN Instead of Subquery

​Batch Processing

​Bulk Query Optimization

​Chunk Processing

​Query Caching

​Caching Frequently Used Queries

​Manual Cache Management

​Execution Plan Analysis

​Using EXPLAIN

​Performance Monitoring

​Query Logging

​Detecting Slow Queries

​Best Practices

​1. Index Strategy

​2. Query Patterns

​3. Database Configuration

​Performance Checklist

​Related Documentation

Query Performance Fundamentals

1. Select Only Required Columns

2. WHERE Clause Optimization

3. Using LIMIT

Index Utilization

Single Column Index

Composite Index

Verifying Index Effectiveness

JOIN Optimization

INNER JOIN vs LEFT JOIN

JOIN Condition Optimization

Aggregation Query Optimization

COUNT Optimization

GROUP BY Optimization

Pagination Optimization

Offset-based (Small Datasets)

Cursor-based (Large Datasets)

Subquery Optimization

EXISTS vs IN

JOIN Instead of Subquery

Batch Processing

Bulk Query Optimization

Chunk Processing

Query Caching

Caching Frequently Used Queries

Manual Cache Management

Execution Plan Analysis

Using EXPLAIN

Performance Monitoring

Query Logging

Detecting Slow Queries

Best Practices

1. Index Strategy

2. Query Patterns

3. Database Configuration

Performance Checklist

Related Documentation