You won’t always have a perfect index for every query, but may have have single-field indexes for each filter. In such cases, PostgreSQL can use Bitmap Scan to combine these indexes. MongoDB is also capable of merging multiple index bounds in a single scan or using index intersection to combine separate scans. Yet, the MongoDB query planner rarely selects index intersection. Let’s look at the reasons behind this.
TL;DR: if you think you need index intersection, you probably need better compound indexes.
Test Setup
I create a collection with one hundred thousand documents and two fields, with an index on each:
let bulk = [];
for (let i = 0; i < 100000; i++) {
bulk.push({
a: Math.floor(Math.random()*100),
b: Math.floor(Math.random()*100)
});
}
db.demo.insertMany(bulk);
// separate indexes
db.demo.createIndex({ a: 1 });
db.demo.createIndex({ b: 1 });
In PostgreSQL, we’ll mirror the dataset as follow:
CREATE TABLE demo AS
SELECT id,
(random()*100)::int AS a,
(random()*100)::int AS b
FROM generate_series(1,100000) id;
CREATE INDEX demo_a_idx ON demo(a);
CREATE INDEX demo_b_idx ON demo(b);
In my MongoDB collection of 100,000 documents, only nine documents have both the "a" and "b" fields set to 42:
mongo> db.demo.countDocuments()
100000
mongo> db.demo.find({ a: 42, b: 42 }).showRecordID()
[
{ _id: ObjectId('6928697ae5fd2cdba9d53f54'), a: 42, b: 42, '$recordId': Long('36499') },
{ _id: ObjectId('6928697ae5fd2cdba9d54081'), a: 42, b: 42, '$recordId': Long('36800') },
{ _id: ObjectId('6928697ae5fd2cdba9d54a7c'), a: 42, b: 42, '$recordId': Long('39355') },
{ _id: ObjectId('6928697ae5fd2cdba9d55a3e'), a: 42, b: 42, '$recordId': Long('43389') },
{ _id: ObjectId('6928697ae5fd2cdba9d5a214'), a: 42, b: 42, '$recordId': Long('61779') },
{ _id: ObjectId('6928697ae5fd2cdba9d5e52a'), a: 42, b: 42, '$recordId': Long('78953') },
{ _id: ObjectId('6928697ae5fd2cdba9d5eeea'), a: 42, b: 42, '$recordId': Long('81449') },
{ _id: ObjectId('6928697ae5fd2cdba9d61f48'), a: 42, b: 42, '$recordId': Long('93831') },
{ _id: ObjectId('6928697ae5fd2cdba9d61f97'), a: 42, b: 42, '$recordId': Long('93910') }
]
In my PostgreSQL database, there are 100,000 rows and, among them, nine rows have the value 42 in both the "a" and "b" columns:
postgres=# select count(*) from demo;
count
--------
100000
(1 row)
postgres=# SELECT *, ctid FROM demo WHERE a = 42 AND b = 42;
a | b | id | ctid
----+----+-------+-----------
42 | 42 | 4734 | (25,109)
42 | 42 | 15678 | (84,138)
42 | 42 | 29464 | (159,49)
42 | 42 | 29748 | (160,148)
42 | 42 | 31139 | (168,59)
42 | 42 | 37785 | (204,45)
42 | 42 | 55112 | (297,167)
42 | 42 | 85823 | (463,168)
42 | 42 | 88707 | (479,92)
I displayed the CTID for PostgreSQL and the RecordID for MongoDB, to see the distribution over the heap table (for PostgreSQL) or the WiredTiger B-Tree (for MongoDB).
MongoDB possible execution plans
I have executed db.demo.find({ a: 42, b: 42 }), and multiple plans have been evaluated:
mongo> db.demo.getPlanCache().list();
[
{
version: '1',
queryHash: 'BBC007A6',
planCacheShapeHash: 'BBC007A6',
planCacheKey: '51C56FDD',
isActive: true,
works: Long('968'),
worksType: 'works',
timeOfCreation: ISODate('2025-11-27T15:09:11.069Z'),
createdFromQuery: { query: { a: 42, b: 42 }, sort: {}, projection: {} },
cachedPlan: {
stage: 'FETCH',
filter: { b: { '$eq': 42 } },
inputStage: {
stage: 'IXSCAN',
keyPattern: { a: 1 },
indexName: 'a_1',
isMultiKey: false,
multiKeyPaths: { a: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { a: [ '[42, 42]' ] }
}
},
creationExecStats: [
{
nReturned: 12,
executionTimeMillisEstimate: 0,
totalKeysExamined: 967,
totalDocsExamined: 967,
executionStages: {
stage: 'FETCH',
filter: { b: { '$eq': 42 } },
nReturned: 12,
executionTimeMillisEstimate: 0,
works: 968,
advanced: 12,
needTime: 955,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
docsExamined: 967,
alreadyHasObj: 0,
inputStage: {
stage: 'IXSCAN',
nReturned: 967,
executionTimeMillisEstimate: 0,
works: 968,
advanced: 967,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
keyPattern: { a: 1 },
indexName: 'a_1',
isMultiKey: false,
multiKeyPaths: { a: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { a: [Array] },
keysExamined: 967,
seeks: 1,
dupsTested: 0,
dupsDropped: 0
}
}
},
{
nReturned: 10,
executionTimeMillisEstimate: 0,
totalKeysExamined: 968,
totalDocsExamined: 968,
executionStages: {
stage: 'FETCH',
filter: { a: { '$eq': 42 } },
nReturned: 10,
executionTimeMillisEstimate: 0,
works: 968,
advanced: 10,
needTime: 958,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 0,
docsExamined: 968,
alreadyHasObj: 0,
inputStage: {
stage: 'IXSCAN',
nReturned: 968,
executionTimeMillisEstimate: 0,
works: 968,
advanced: 968,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 0,
keyPattern: { b: 1 },
indexName: 'b_1',
isMultiKey: false,
multiKeyPaths: { b: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { b: [Array] },
keysExamined: 968,
seeks: 1,
dupsTested: 0,
dupsDropped: 0
}
}
},
{
nReturned: 7,
executionTimeMillisEstimate: 0,
totalKeysExamined: 968,
totalDocsExamined: 7,
executionStages: {
stage: 'FETCH',
filter: { '$and': [ [Object], [Object] ] },
nReturned: 7,
executionTimeMillisEstimate: 0,
works: 968,
advanced: 7,
needTime: 961,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 0,
docsExamined: 7,
alreadyHasObj: 0,
inputStage: {
stage: 'AND_SORTED',
nReturned: 7,
executionTimeMillisEstimate: 0,
works: 968,
advanced: 7,
needTime: 961,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 0,
failedAnd_0: 232,
failedAnd_1: 230,
inputStages: [ [Object], [Object] ]
}
}
}
],
candidatePlanScores: [ 2.012596694214876, 1.0105305785123966, 1.0073314049586777 ],
indexFilterSet: false,
estimatedSizeBytes: Long('4826'),
solutionHash: Long('6151768200665613849'),
host: '40ae92e83a12:27017'
}
]
The cached plan uses only one index, on "a", but there are two additional possible plans in the cache: one using the index on "b" and another using a combination of both indexes with AND_SORTED. The scores (candidatePlanScores) are:
- 2.012 for the index on "a"
- 1.010 for the index on "b"
- 1.007 for the AND_SORTED intersection of the indexes on "a" and "b"
This may be surprising, and given how the data was generated, we should expect similar costs for the two indexes. We can see that during the trial period on the query plans, the index on "a" finished the scan (isEOF: 1), and even though the other two had similar performance and were going to end, the trial period ended before they reached the end (isEOF: 0). MongoDB adds an EOF bonus of 1 when one when the trial plan finishes before the others, and that explains why the score is higher. So it's not really that the index on "a" is better than the other plans, but just that all plans are good, and the first one started and finished first, and got the bonus.
In addition to that, there's another small penalty on index intersection. Finally the scores are:
- Index on "a": 1 (base) + 0.012 (productivity) + 1.0 (EOF bonus) = 2.012
- Index on "b": 1 (base) + 0.010 (productivity) + 0 (no EOF) = 1.010
- AND_SORTED: 1 (base) + 0.007 (productivity) + 0 (no EOF) = 1.007
Without the penalties, AND_SORTED would still not have been chosen. The problem is that the score measure productivity in units of work (advanced/work) but do not account for lighter work: one index scan must fetch the document and apply the additional filter in one work unit, where AND_SORTED doesn't and waits for the intersection without additional fetch and filter.
To show the AND_SORTED plan, I'll force it on my lab database for the following examples in this article:
// get the current parametrs (default):
mongo> Object.keys(db.adminCommand({ getParameter: "*" })).filter(k => k.toLowerCase().includes("intersection")) .forEach(k => print(k + " : " + allParams[k]));
internalQueryForceIntersectionPlans : false
internalQueryPlannerEnableHashIntersection : false
internalQueryPlannerEnableIndexIntersection : true
// set all at true:
db.adminCommand({
setParameter: 1,
internalQueryPlannerEnableIndexIntersection: true,
internalQueryPlannerEnableHashIntersection: true,
internalQueryForceIntersectionPlans: true
});
I have set internalQueryForceIntersectionPlans to force index intersection (it still uses the query planner, but with a 3-point boost to the score). Index intersection is possible for AND_SORTED by default, but I also set AND_HASH for another test later that cannot use AND_SORTED.
Index Intersection in MongoDB
Now that I forced index intersection, I can observe it with execution statistics:
db.demo.find({ a: 42, b: 42 }).explain("executionStats").executionStats
Execution plan shows that both indexes were scanned with one range (seeks: 1), and combined with an AND_SORTED before fetching the documents for the result:
{
executionSuccess: true,
nReturned: 9,
executionTimeMillis: 4,
totalKeysExamined: 2009,
totalDocsExamined: 9,
executionStages: {
isCached: false,
stage: 'FETCH',
filter: {
'$and': [ { a: { '$eq': 42 } }, { b: { '$eq': 42 } } ]
},
nReturned: 9,
executionTimeMillisEstimate: 0,
works: 2010,
advanced: 9,
needTime: 2000,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
docsExamined: 9,
alreadyHasObj: 0,
inputStage: {
stage: 'AND_SORTED',
nReturned: 9,
executionTimeMillisEstimate: 0,
works: 2010,
advanced: 9,
needTime: 2000,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
failedAnd_0: 501,
failedAnd_1: 495,
inputStages: [
{
stage: 'IXSCAN',
nReturned: 964,
executionTimeMillisEstimate: 0,
works: 965,
advanced: 964,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 1,
keyPattern: { a: 1 },
indexName: 'a_1',
isMultiKey: false,
multiKeyPaths: { a: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { a: [ '[42, 42]' ] },
keysExamined: 964,
seeks: 1,
dupsTested: 0,
dupsDropped: 0
},
{
stage: 'IXSCAN',
nReturned: 1045,
executionTimeMillisEstimate: 0,
works: 1045,
advanced: 1045,
needTime: 0,
needYield: 0,
saveState: 0,
restoreState: 0,
isEOF: 0,
keyPattern: { b: 1 },
indexName: 'b_1',
isMultiKey: false,
multiKeyPaths: { b: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { b: [ '[42, 42]' ] },
keysExamined: 1045,
seeks: 1,
dupsTested: 0,
by Franck Pachot
AWS Database Blog - Amazon Aurora
Netflix operates a global streaming service that serves hundreds of millions of users through a distributed microservices architecture. In this post, we examine the technical and operational challenges encountered by their Online Data Stores (ODS) team with their current self-managed distributed PostgreSQL-compatible database, the evaluation criteria used to select a database solution, and why they chose to migrate to Amazon Aurora PostgreSQL to meet their current and future performance needs. The migration to Aurora PostgreSQL improved their database infrastructure, achieving up to 75% increase in performance and 28% cost savings across critical applications.
by Jordan West, Ammar Khaku
November 26, 2025
AWS Database Blog - Amazon Aurora
With the Letta Developer Platform, you can create stateful agents with built-in context management (compaction, context rewriting, and context offloading) and persistence. Using the Letta API, you can create agents that are long-lived or achieve complex tasks without worrying about context overflow or model lock-in. In this post, we guide you through setting up Amazon Aurora Serverless as a database repository for storing Letta long-term memory. We show how to create an Aurora cluster in the cloud, configure Letta to connect to it, and deploy agents that persist their memory to Aurora. We also explore how to query the database directly to view agent state.
by Sarah Wooders
Percona Database Performance Blog
Where We Are We can all agree that the MySQL ecosystem isn’t in great shape right now. Take a look at Julia’s blog post [Analyzing the Heartbeat of the MySQL Server: A Look at Repository Statistics], which confirms what many of us have felt: Oracle isn’t as committed to MySQL and its ecosystem as it […]
by Vadim Tkachenko
ParadeDB Blog
Introducing search aggregation, V2 API as default, and performance improvements that eliminate the complexity between search and analytics in a single Postgres-native system.
by Philippe Noël
