Build1 publisher2 min readPublished
One index scan answers a filtered, sorted page of embedded array data in DocumentDB 0.109
Azure DocumentDB 0.109 answered a filter, sort, limit and project query on embedded arrays with one index scan and no sort stage, in a test posted on dev.to. The test is small, so what carries to other workloads is the plan shape a PostgreSQL extension can produce for document queries.
The Engineer · Build desk
Drafted by a language model from the sources cited here and checked against its claim ledger before publication. How we use AISend a correction
What happened
- DocumentDB runs inside PostgreSQL as an extension and indexes document data with Extended RUM indexes, a native index type it defines through PostgreSQL's extensibility.
- The test indexed category ascending and operations.date descending, then upserted 10,000 operations into embedded arrays across account ids 1 to 10,000.
- The query asked which category 1 account had the most recent activity, projecting operation amounts and dates and returning a single document.
- Sent to the PostgreSQL endpoint through DocumentDB's bson_aggregation_pipeline function, the same four-stage query returned one row in 0.112 milliseconds.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- capability Paged queries over embedded arrays on PostgreSQL can stop after the first matching index entries, so the planner prices the first page near the page size instead of the full set of category 1 accounts.
- decision Anyone choosing a MongoDB-compatible layer on a SQL database now has a concrete test: explain a filtered, sorted, limited query on an embedded field and see whether a sort stage appears above the scan.
- constraint The ordered plan relies on an equality filter on the index's leading field and a sort in the second field's direction, so range filters or other sort keys each need their own explain check.
A normalized schema puts accounts and their operations in separate tables, and each index belongs to one table [4]. Finding the category 1 account with the latest operation then means joining rows before the database can sort and filter them [4]. With the operations embedded as an array inside each account, one compound index can serve a filter on the account's category and on each operation's date [6].
The plan reads from the bottom up. The IXSCAN on category_1_operations.date_-1 is bounded to category 1, covers the full date range in descending order, and reports hasOrderBy: true [8]. The index is multikey, so an account with several operations has an entry for each one [8]. FETCH, PROJECT and LIMIT are the only stages above the scan [8].
The costs show the planner counting on the limit. The scan and the fetch are estimated at 1,055 keys and a total cost of 208.79, and the LIMIT node is costed at 0.2 for one key [9]. That is about 0.1% of the full scan's cost [1].
The post's title calls the pipeline covered by an index scan [13]. The FETCH stage on test.accounts is a mild objection [8]. The index supplies the category filter and the date order, and the documents are still read to build the projection [8].
Putting the fix in an index type the extension defines is good engineering [1]. The post's author wrote that the emulations that fall short are "built on top of RDBMS indexes which were not designed for non-1NF schemas" [3], and that some of them lack performance as a result [2]. The post runs only the DocumentDB side [12]. Its claim that DocumentDB "provides the same performance with similar execution plan" as MongoDB rests on the author's earlier video demo of the same workload [11][12].
The PostgreSQL timing comes with conditions [10]. Every operation date was set by new Date() during one load of ten batches [5]. The account returned has two operations 2.5 seconds apart [3]. A larger page of distinct accounts would have to step past repeat index entries for accounts with several operations, and the post shows only the one-row case [8].
What to watch
- A published plan for the same pipeline with a limit above one, where accounts appear once per operation in the multikey index.
- Explain output from relational-index MongoDB emulations on this exact pipeline, which would test the author's performance claim about them.
- Timings from larger datasets with operation dates spread over months instead of seconds.