fix(elasticsearch-plugin): load indexing relations with the query strategy

[TheHypnoo]

Relates to vendurehq/vendure#4480

Summary

• ElasticsearchIndexerController.updateProductsOperationsOnly loads its relations with TypeORM's default join strategy, which resolves every relation in a single query. Because most of the configured relations are to-many, the result set is their cartesian product.
• That method is the body of the full-reindex loop, so the cost is paid once per product.
• Switching those two find() calls to relationLoadStrategy: 'query' makes each relation its own statement, keeping the row count linear.

Changes

File	Change
packages/elasticsearch-plugin/src/indexing/indexer.controller.ts	Add relationLoadStrategy: 'query' to the Product and ProductVariant queries in updateProductsOperationsOnly

Why the row count explodes

The relations loaded for each product are:

defaultProductRelations = ['featuredAsset', 'facetValues', 'facetValues.facet', 'channels', 'channels.defaultTaxZone'];
defaultVariantRelations = ['featuredAsset', 'facetValues', 'facetValues.facet', 'collections', 'taxCategory', 'channels', 'channels.defaultTaxZone'];

facetValues, collections and channels are many-to-many, and translations and productVariantPrices are eager one-to-many, so the joined result for the variant query is roughly:

variants × facetValues × collections × channels × translations × prices

Benchmarks

Measured locally against PostgreSQL 17 and a real Elasticsearch 9.3.4 instance, with a synthetic catalogue seeded at several shapes.

End-to-end full reindex (190 products, ~3,400 variants)

Strategy	Wall time	SQL statements	Documents indexed
join (current)	387.7 s	33,802	24,568
query (this PR)	53.9 s / 45.5 s on a second run	37,306	24,568

Roughly 7–8x faster, and the resulting index is identical.

Isolated — the two find() calls only, median per product

Catalogue shape	Rows returned (join / query)	join	query	Ratio
2 variants, 2 facet values, 1 collection, 1 channel, 1 language	6 / 38	10.5 ms	13.3 ms	0.79x
5 variants, 3 facet values, 3 collections, 1 channel, 2 languages	96 / 111	15.4 ms	10.8 ms	1.43x
20 variants, 5 facet values, 5 collections, 2 channels, 2 languages	4,020 / 635	181.7 ms	15.3 ms	11.9x
60 variants, 8 facet values, 10 collections, 3 channels, 3 languages	129,672 / 3,234	5,637.8 ms	165.2 ms	34.1x
200 variants, 10 facet values, 15 collections, 3 channels, 3 languages	810,090 / 13,462	37,976.2 ms	217.3 ms	174.7x

The trade-off is visible in the first row: for a trivial catalogue the query strategy costs ~3 ms more per product, because it issues 21 statements where join issues 2. From roughly 20 variants upwards the join's cartesian product dominates and the difference becomes orders of magnitude.

Correctness

Both strategies were compared field by field on every shape — variant ids, facet value ids, facet ids, collection ids, channel ids, tax zone ids, translations and prices all match, and the end-to-end runs produced the same 24,568 Elasticsearch documents.

Scope

Only these two queries are affected. The other database access in the plugin is either a createQueryBuilder (where relationLoadStrategy has no effect) or a single many-to-one relation (getProductIdsByVariantIds), where the default join strategy is already the better choice.

Test plan

• Full reindex against a real Elasticsearch 9.3.4 instance, before and after, with identical document counts
• Per-shape comparison of the loaded entity graphs between both strategies
• CI

---

^{Need help on this PR? Tag @codesmith-bot with what you need. Autofix is disabled.}

[vendure-ci-automation-bot]

All contributors have signed the CLA ✍️ ✅
_{Posted by the CLA Assistant Lite bot.}

[biggamesmallworld]

Correcting my earlier comment: both objections I raised were wrong. The numbers in the description hold up.

I claimed the 810k row figure did not reproduce because I measured 3,200 rows. Different catalogue, and I misread yours. Yours is 200 variants x 10 facet values x 15 collections x 3 channels x 3 languages x prices, which lands at ~810,000. Mine was 4 facet values, 4 collections, 1 channel, 1 language, so 200 x 4 x 4 = 3,200. I also left translations and prices out of my row count entirely, which is where two of your multipliers come from. Both numbers are correct for their own shape.

I also misread the crossover claim. "From roughly 20 variants upwards the difference becomes orders of magnitude" is not a claim about where query overtakes join, and your own table already shows it ahead at 5 variants. Your 0.79x row is 2 variants with 2 facet values and 1 collection, thinner than anything I seeded, so that small-catalogue cost is real and I simply never measured that low.

My independent run, for what it is worth as corroboration. SQL layer only, Postgres 16, median of 10 runs, 4 facet values / 4 collections / 1 channel / 1 language per variant:

variants	join ms	query ms	speedup
1	3.86	4.16	0.93x
5	5.73	4.91	1.17x
20	13.50	5.56	2.43x
50	28.09	9.16	3.07x
200	103.23	47.79	2.16x

Same shape as yours, smaller magnitude because the catalogue is thinner. The worst case I could produce is 0.93x, a 0.3ms difference inside the noise, so there is no catalogue shape where this loses meaningfully.

Nothing blocking. Two optional things:

A comment above the Product find would help. TypeORM defaults to join, so the line looks like redundant config and will eventually get deleted by someone tidying up:

// These relations are to-many, so the default 'join' strategy returns their
// cartesian product: N variants x facet values x collections x channels rows
// for a single product. One query per relation keeps the row count linear.

And "that method is the body of the full-reindex loop" undersells it. updateProduct, updateVariants, deleteVariants and the channel assign/remove handlers all reach updateProductsOperationsOnly via updateProductsInternal, so incremental updates get this win too.

Approving.

[biggamesmallworld]

Requesting changes, retracting my earlier approve. CI red is caused by this change. Reproduced locally: main 99/99 pass, this branch 69 fail.

relationLoadStrategy: 'query' is fine alone. It breaks when the same find() also passes order or select and a relation lives inside an embedded entity. Minimal repro, no Vendure:

join  + order   -> OK
query + order   -> THROW TypeError: Cannot read properties of undefined (reading 'tags')
query + select  -> THROW TypeError: Cannot read properties of undefined (reading 'tags')
query (neither) -> OK

Filed upstream with that repro: typeorm/typeorm#12788 (same cause as #10821, closed as not planned, so don't wait on it).

Worse than a red check: the catch swallows the throw, so the indexer writes a synthetic placeholder document for every product. Any store hydrating a customFields.* relation silently ends up with an index of empty documents.

The perf work is sound and I want it merged, and the e2e tests are correct as they are. Fix is inline.

[TheHypnoo]

Thanks for catching this. I removed the query-level ordering and sort the loaded variants by id in memory instead. The code now explains the TypeORM edge case, and a variant-loading failure is rethrown so it cannot silently replace real documents with synthetic ones.