A 12-second join plus a wildcard search request

Read the plan first: #primary on orders means a full bucket scan of 22 million documents, then a filter, then a hash join, then a sort of everything that survived, then LIMIT 50. Neither existing index helps: idx_status alone matches 4 million documents and provides no ordering, so the sort is the 12 seconds.

Index:

CREATE INDEX idx_open_created ON orders(status, createdAt DESC)
WHERE status = "OPEN";

Key-order reasoning:

• status is an equality predicate, so it must be the leading key; it collapses the scan to the OPEN partition.
• createdAt is both a range (>= "2025-01-01") and the sort key, so it goes second, and in the same direction as the ORDER BY. A DESC index lets the scan emit rows already in order and removes the sort operator.
• The partial WHERE status = "OPEN" shrinks the index from 22 million to ~4 million entries. It is only usable if the query repeats that predicate - it does, and the optimizer checks for it.

Make it covering: if the plan still shows a fetch, add the projected fields, e.g. (status, createdAt DESC, customerId, orderId). Only items and the customer key lookup remain.

Verify with EXPLAIN: expect IndexScan rather than PrimaryScan, covered where applicable, and no Order operator. Adding an index on createdAt alone still scans every order since January.

The problem: UNNEST sits between the index scan and ORDER BY/LIMIT, so whether the engine can stop after 50 rows depends on preserving index order through the unnest and the key join. In the worst case it materializes every matching order, unnests all items, joins, sorts, then takes 50 - the difference between reading 50 documents and reading 4 million.

Apply the limit before the unnest:

SELECT o.orderId, o.createdAt, c.name, i.sku
FROM (
  SELECT o
  FROM orders o
  WHERE o.status = "OPEN" AND o.createdAt >= "2025-01-01"
  ORDER BY o.createdAt DESC
  LIMIT 50
) AS o
UNNEST o.items AS i
JOIN customers c ON KEYS o.customerId;

The inner block is a pure ordered index range scan with a limit; the outer block processes at most 50 orders and their items.

Verify, do not assume: run EXPLAIN on both forms and compare operator order. You want IndexScan → Limit → Unnest → Join. If an Order operator persists, the index is not supplying the sort - wrong index direction, or the predicate is not sargable.

Semantics matter as much as speed: with the flat form, LIMIT 50 limits order-item rows, not orders, so a report of "50 most recent orders" silently returns fewer when orders have multiple items. The subquery form fixes correctness first and performance second.

Join hints: ON KEYS is already a key lookup, so USE HASH or USE NL hints rarely help here; adding one to compensate for a missing index is a smell.

Why a GSI cannot do it: a GSI is an ordered B-tree. It answers prefix matches (LIKE "ABC%"), but not a leading wildcard - there is no ordered range containing all strings that end in ABC. N1QL falls back to scanning and filtering, which on 22 million documents is not a feature, it is an outage.

Options:

1. Full-Text Search index over customers.name and orders.items.sku, queried via SEARCH() in N1QL or the SDK Search API. Analyzers give tokenized, case-insensitive matching, fuzzy and relevance scoring, and genuine substring behavior. Costs: it is a second index with its own sizing, its own consistency model (a document written a moment ago may not be searchable yet), and an operational component to manage. Keep it off the checkout path.
2. Array or functional index for structured tokens. If SKUs look like ABC-1234, store a normalized skuTokens array and index DISTINCT ARRAY t FOR t IN skuTokens END for equality and prefix. For suffix matching, also store skuReversed and search prefixes on it - effective, but two derived fields to maintain and easy to desynchronize.
3. LOWER(name) functional index for case-insensitive equality or prefix - still no help for %term%.

Recommendation: build the FTS index for search, leave the operational (status, createdAt DESC) index untouched for the list query, and do not contort one query into being both a range scan and a search engine. Budget for index size and reindex time up front; measure FTS index footprint before committing.