$lookup performance

[samuelpares]

I'm using lookup just to return associated data. I'm not perfomring any stage for filtering/matching. So for what I understand, my lookups stages are executed before the pagination. Is there a way to performe pagination before? It would increase a lot the performance.

async findAll(query: QueryDto) {
    const { perPage, page } = query;

    const options = {
      limit: perPage,
      page: page,
      pagination: perPage > 0,
    };

    const pipeline = this.aggregateModel.aggregate();
    pipeline.sort({ createdAt: -1 });
    pipeline.lookup({
      from: 'accounts',
      localField: 'account',
      foreignField: '_id',
      as: 'account',
    });
    pipeline.lookup({
      from: 'businessnumbers',
      localField: 'businessNumber',
      foreignField: '_id',
      as: 'businessNumber',
    });
    pipeline.lookup({
      from: 'templates',
      localField: 'template',
      foreignField: '_id',
      as: 'template',
    });
    pipeline.unwind({ path: '$account' });
    pipeline.unwind({ path: '$businessNumber' });
    pipeline.unwind({ path: '$template' });

    return await this.aggregateModel.aggregatePaginate(pipeline, options);
  }

[lattam]

Having the same problem, I came up with the working solution inspired by this library

    aggregatePaginate: async (pipelineBefore: any[] = [], pipelineAfter: any[] = [], options: any = {}) => {
      function getPaginationPipeline(options, pipelineAfterPagination) {
        const page = options.page ?? 1;
        const limit = options.limit ?? 10;

        return [
          {
            $sort: {
              _id: -1,
              ...options.sort,
            },
          },
          {
            $facet: {
              items: [
                {
                  $match: {},
                },
                {
                  $skip: ((page - 1) * limit)
                },
                {
                  $limit: limit,
                },

                ...pipelineAfterPagination,
              ],
              totalCount: [
                {
                  $count: 'count',
                }
              ],
            },
          },
        ];
      }

      const aggregation = await Model.aggregate([
        ...pipelineBefore,
        ...getPaginationPipeline(options, pipelineAfter),
      ]);

      return {
        items: aggregation?.[0]?.items ?? [],
        totalCount: aggregation?.[0]?.totalCount?.[0]?.count ?? 0,
      };
    },

pipelineBefore - this contains the part of pipeline that only does some basic matching on existing fields pipelineAfter - this pipeline contains all the lookups from other collection, it is done only after the pagination limits the results into very small subset