Query Performance

Finding and fixing N+1 selects, choosing between eager and lazy relations, and reading an EXPLAIN plan.

Syntaxfind({ relations: { items: true }, relationLoadStrategy: 'join' | 'query' })

Most slow Nest endpoints are not slow code. They are one query repeated in a loop, or one query that joins more than it needs.

N+1 selects

Fetch twenty orders, then read order.items per order, and you have issued twenty-one statements. Each is fast; the round trips are not. Ask for the relation up front: find({ relations: { items: true } }) emits a single statement with a LEFT JOIN. When one row fans out across two collections, the joined result multiplies; relationLoadStrategy: 'query' loads each relation with its own SELECT instead, so you get three statements rather than one wide one.

Eager versus lazy

eager: true on a relation loads it on every find* call. It is honoured only by find*: a QueryBuilder ignores it, and you must add leftJoinAndSelect. Lazy relations are typed Promise<T[]> and query when awaited; TypeORM calls them experimental, and each access is another round trip, usually after your handler has returned.

Reading an EXPLAIN

Prefix the SQL with EXPLAIN ANALYZE and compare the estimate with reality:

Limit  (cost=0.43..8.71 rows=20 width=48)
       (actual time=0.031..0.214 rows=20 loops=1)
  ->  Seq Scan on order  (actual rows=184233 loops=1)
        Filter: ("customerId" = 7)

Three things matter: Seq Scan on a filtered column means no usable index; rows=20 against actual rows=184233 means the planner was misinformed; loops above 1 means that node ran repeatedly.

Example

Example · typescript
// src/orders/orders-report.service.ts
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { Order } from './order.entity.js';
import { OrderItem } from './order-item.entity.js';

@Injectable()
export class OrdersReportService {
  constructor(
    @InjectRepository(Order) private readonly orders: Repository<Order>,
  ) {}

  // N+1: one SELECT for the page, then one more for every order in it.
  async slow(customerId: number): Promise<Order[]> {
    const page = await this.orders.find({ where: { customerId }, take: 20 });

    for (const order of page) {
      order.items = await this.orders.manager.find(OrderItem, {
        where: { orderId: order.id },
      });
    }
    return page; // 21 round trips
  }

  // One statement: the relation is joined in.
  fast(customerId: number): Promise<Order[]> {
    return this.orders.find({
      where: { customerId },
      relations: { items: true, customer: true },
      order: { createdAt: 'DESC' },
      // take/skip rather than limit/offset: with joins, limit/offset would cut
      // the joined rows, not the orders.
      skip: 0,
      take: 20,
    });
  }

  // Three statements (orders, items, tags). Cheaper than one join when a
  // single order has many items AND many tags, because a join would return
  // items x tags rows per order.
  fastWide(customerId: number): Promise<Order[]> {
    return this.orders.find({
      where: { customerId },
      relations: { items: true, tags: true },
      relationLoadStrategy: 'query',
      take: 20,
    });
  }
}

When to use it

  • A list endpoint that got slower as rows were added, with no code change to explain it.
  • Deciding whether a relation should be joined, loaded separately, or not loaded at all.
  • Confirming that an index you just added is actually being used.
  • Catching an accidental N+1 in review by reading the query log for one request.

More examples

Printing the plan for a query the ORM built

The usual obstacle to reading a plan is not knowing what SQL the ORM produced. getQueryAndParameters hands you both halves, so you can run EXPLAIN on exactly the statement production runs. Keep this behind a debug route or a script: ANALYZE executes the query, so it is for reads only.

Example · typescript
// src/orders/orders-explain.service.ts
import { Injectable, Logger } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { Order } from './order.entity.js';

@Injectable()
export class OrdersExplainService {
  private readonly logger = new Logger(OrdersExplainService.name);

  constructor(
    @InjectRepository(Order) private readonly orders: Repository<Order>,
  ) {}

  async explainCustomerOrders(customerId: number): Promise<string> {
    const qb = this.orders
      .createQueryBuilder('order')
      .leftJoinAndSelect('order.items', 'item')
      .where('order.customerId = :customerId', { customerId })
      .orderBy('order.createdAt', 'DESC')
      .take(20);

    // getQueryAndParameters() returns [sql, parameters]; getSql() alone
    // returns the statement with placeholders still in it.
    const [sql, parameters] = qb.getQueryAndParameters();

    // EXPLAIN ANALYZE actually runs the statement. Never do this on a write.
    const rows: Array<{ 'QUERY PLAN': string }> = await this.orders.query(
      `EXPLAIN (ANALYZE, BUFFERS) ${sql}`,
      parameters,
    );

    const plan = rows.map((row) => row['QUERY PLAN']).join('\n');
    this.logger.debug(`\n${plan}`);
    return plan;
  }
}

Eager and lazy, side by side

Eager relations trade a wider query for fewer calls; lazy relations trade more calls for a narrower one. Use eager only for small lookup data that every caller needs, and prefer explicit relations in find options everywhere else, so each endpoint's cost is visible in the endpoint's own code.

Example · typescript
// src/catalog/product.entity.ts
import { Column, Entity, ManyToMany, JoinTable, OneToMany, PrimaryGeneratedColumn } from 'typeorm';
import { Category } from './category.entity.js';
import { Review } from './review.entity.js';

@Entity()
export class Product {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  // EAGER: joined on every find/findOne/findOneBy of Product, whether the
  // caller wanted it or not. Only safe for small, always-needed data.
  // Allowed on one side of the relation only.
  @ManyToMany(() => Category, (category) => category.products, { eager: true })
  @JoinTable()
  categories: Category[];

  // LAZY: the property type is a Promise. The SELECT fires when awaited.
  // TypeORM documents this as experimental.
  @OneToMany(() => Review, (review) => review.product)
  reviews: Promise<Review[]>;
}

// Using them:
//
// const products = await repo.find();            // categories already populated
// const reviews = await products[0].reviews;     // a second query, here
//
// const products = await repo.find({ loadEagerRelations: false }); // opt out
//
// QueryBuilder ignores eager entirely:
// const rows = await repo.createQueryBuilder('p')
//   .leftJoinAndSelect('p.categories', 'category')  // required by hand
//   .getMany();

Making slow queries show up on their own

An N+1 is obvious in a log that shows twenty identical SELECTs with different ids, and invisible otherwise. Turn query logging on locally, set maxQueryExecutionTime in every environment so slow statements announce themselves, and use findAndCount for list endpoints that also need a total.

Example · typescript
// src/app.module.ts (excerpt)
import { Module } from '@nestjs/common';
import { ConfigModule, ConfigService } from '@nestjs/config';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [
    TypeOrmModule.forRootAsync({
      imports: [ConfigModule],
      inject: [ConfigService],
      useFactory: (config: ConfigService) => ({
        type: 'postgres' as const,
        url: config.getOrThrow<string>('DATABASE_URL'),
        autoLoadEntities: true,
        synchronize: false,
        // Log any statement that takes longer than 200ms.
        maxQueryExecutionTime: 200,
        // In development, log every query to see N+1 as it happens;
        // in production keep it to errors and slow queries.
        logging:
          config.get('NODE_ENV') === 'development'
            ? ['query', 'error']
            : ['error'],
        logger: 'advanced-console',
      }),
    }),
  ],
})
export class AppModule {}

// src/orders/orders-page.service.ts (excerpt)
// findAndCount returns [rows, total] from two statements, so a paginated
// endpoint does not need a separate count() call.
const [rows, total] = await this.orders.findAndCount({
  where: { customerId },
  relations: { items: true },
  order: { createdAt: 'DESC' },
  skip: (page - 1) * 20,
  take: 20,
});

Related

nest entities relationsnest repositorynest cache modulenest graphql nplusonenest logging

Discussion

  • Be the first to comment on this lesson.