موردی که SQL به مبارزه می پیوندد اما اسناد MongoDB می درخشد

CREATE TABLE orders(
  id BIGSERIAL PRIMARY KEY,
  country_id INT,
  created_at TIMESTAMPTZ DEFAULT clock_timestamp()
);

CREATE TABLE order_details (
  id BIGINT REFERENCES orders ON DELETE CASCADE,
  line INT,
  product_id BIGINT,
  quantity INT,
  PRIMARY KEY(id, line)
);

BEGIN TRANSACTION;

INSERT INTO orders(country_id)
 SELECT 10 * random() FROM generate_series(1,1000000);

INSERT INTO order_details (id, line, product_id, quantity)
 SELECT 
  id,
  generate_series(1,10),
  log(2,(1 + id * random())::int),
  100 * random()
  FROM orders;

COMMIT;

CREATE INDEX ON orders (country_id, created_at DESC, id);

CREATE INDEX ON order_details (product_id, id);

PREPARE query(int,int,int) AS
 SELECT id, created_at, product_id, quantity
 FROM orders
 JOIN order_details d USING(id)
 WHERE country_id=$1 AND product_id=$2
 ORDER BY created_at DESC LIMIT $3
;

postgres=# VACUUM ANALYZE orders, order_details;
VACUUM

postgres=# EXPLAIN (analyze, buffers, costs off)
           EXECUTE query(1, 15, 10)
;
                                                                                                                                                                                                 QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=0.031..0.110 rows=10 loops=1)
   Buffers: shared hit=132
   ->  Nested Loop (actual time=0.030..0.108 rows=10 loops=1)
         Buffers: shared hit=132
         ->  Index Only Scan using orders_country_id_created_at_id_idx on orders (actual time=0.011..0.023 rows=39 loops=1)
               Index Cond: (country_id = 1)
               Heap Fetches: 0
               Buffers: shared hit=5
         ->  Index Scan using order_details_product_id_id_idx on order_details d (actual time=0.002..0.002 rows=0 loops=39)
               Index Cond: ((product_id = 15) AND (id = orders.id))
               Buffers: shared hit=127
 Planning:
   Buffers: shared hit=16
 Planning Time: 0.272 ms
 Execution Time: 0.127 ms
(15 rows)

postgres=# EXPLAIN (analyze, buffers, costs off)
           EXECUTE query(1, 8, 10)
;
                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual time=15.614..16.661 rows=10 loops=1)
   Buffers: shared hit=37582
   ->  Gather Merge (actual time=15.613..16.659 rows=10 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=37582
         ->  Nested Loop (actual time=1.396..9.112 rows=7 loops=3)
               Buffers: shared hit=37582
               ->  Parallel Index Only Scan using orders_country_id_created_at_id_idx on orders (actual time=0.015..0.546 rows=4165 loops=3)
                     Index Cond: (country_id = 1)
                     Heap Fetches: 0
                     Buffers: shared hit=70
               ->  Index Scan using order_details_product_id_id_idx on order_details d (actual time=0.002..0.002 rows=0 loops=12494)
                     Index Cond: ((product_id = 8) AND (id = orders.id))
                     Buffers: shared hit=37512
 Planning:
   Buffers: shared hit=16
 Planning Time: 0.272 ms
 Execution Time: 16.684 ms
(19 rows)

postgres=# EXPLAIN (analyze, buffers, costs off)
           EXECUTE query(1, 5, 10)
;
                                                                 QUERY PLAN                                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------                                     
 Limit (actual time=30.370..30.373 rows=10 loops=1)
   Buffers: shared hit=1882
   ->  Sort (actual time=30.369..30.371 rows=10 loops=1)
         Sort Key: orders.created_at DESC
         Sort Method: top-N heapsort  Memory: 26kB
         Buffers: shared hit=1882
         ->  Hash Join (actual time=28.466..30.324 rows=236 loops=1)
               Hash Cond: (d.id = orders.id)
               Buffers: shared hit=1882
               ->  Index Scan using order_details_product_id_id_idx on order_details d (actual time=0.013..1.434 rows=2311 loops=1)                                              
                     Index Cond: (product_id = 5)
                     Buffers: shared hit=1387
               ->  Hash (actual time=28.400..28.401 rows=99672 loops=1)
                     Buckets: 131072  Batches: 1  Memory Usage: 5697kB
                     Buffers: shared hit=495
                     ->  Index Only Scan using orders_country_id_created_at_id_idx on orders (actual time=0.010..13.136 rows=99672 loops=1)                                      
                           Index Cond: (country_id = 1)
                           Heap Fetches: 0
                           Buffers: shared hit=495
 Planning:
   Buffers: shared hit=16
 Planning Time: 0.267 ms
 Execution Time: 30.415 ms
(23 rows)

const bulkOps = [];
for (let i = 0; i < 1000000; i++) {
  const orderDetails = [];
  for (let line = 1; line <= 10; line++) {
    orderDetails.push({
      line: line,
      product_id: Math.floor(Math.log2(1 + i * Math.random())),
      quantity: Math.floor(100 * Math.random()),
    });
  }
  bulkOps.push({
    insertOne: {
      document: {
        country_id: Math.floor(10 * Math.random()),
        created_at: new Date(),
        order_details: orderDetails
      }
    }
  });
}
db.orders.bulkWrite(bulkOps);

test> db.orders.find().sort({created_at: -1}).limit(1);
[
  {
    _id: ObjectId('67f1a477aabaf2dad73f4791'),
    country_id: 3,
    created_at: ISODate('2025-04-05T21:45:21.546Z'),
    order_details: [
      { line: 1, product_id: 19, quantity: 40 },
      { line: 2, product_id: 18, quantity: 10 },
      { line: 3, product_id: 18, quantity: 75 },
      { line: 4, product_id: 18, quantity: 81 },
      { line: 5, product_id: 16, quantity: 66 },
      { line: 6, product_id: 14, quantity: 17 },
      { line: 7, product_id: 19, quantity: 82 },
      { line: 8, product_id: 19, quantity: 81 },
      { line: 9, product_id: 17, quantity: 56 },
      { line: 10, product_id: 19, quantity: 59 }
    ]
  }
]

db.orders.createIndex(
 { "country_id": 1, "order_details.product_id": 1, "created_at": -1 }
);

db.orders.find(
  { country_id: 1, order_details: { $elemMatch: { product_id: 15 } } }
).sort({ created_at: -1 }).limit(10);

mdb> db.orders.find(
   { country_id: 1, order_details: { $elemMatch: { product_id: 15 } } }
 ).sort({ created_at: -1 }).limit(10).explain(`executionStats`).executionStats
;
{
  executionSuccess: true,
  nReturned: 10,
  executionTimeMillis: 0,
  totalKeysExamined: 10,
  totalDocsExamined: 10,
  executionStages: {
    isCached: false,
    stage: 'LIMIT',
    nReturned: 10,
    executionTimeMillisEstimate: 0,
    works: 11,
    advanced: 10,
    limitAmount: 10,
    inputStage: {
      stage: 'FETCH',
      filter: {
        order_details: { '$elemMatch': { product_id: { '$eq': 15 } } }
      },
      nReturned: 10,
      executionTimeMillisEstimate: 0,
      works: 10,
      advanced: 10,
      docsExamined: 10,
      alreadyHasObj: 0,
      inputStage: {
        stage: 'IXSCAN',
        nReturned: 10,
        executionTimeMillisEstimate: 0,
        works: 10,
        advanced: 10,
        keyPattern: {
          country_id: 1,
          'order_details.product_id': 1,
          created_at: -1
        },
        indexName: 'country_id_1_order_details.product_id_1_created_at_-1',
        isMultiKey: true,
        multiKeyPaths: {
          country_id: [],
          'order_details.product_id': [ 'order_details' ],
          created_at: []
        },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          country_id: [ '[1, 1]' ],
          'order_details.product_id': [ '[15, 15]' ],
          created_at: [ '[MaxKey, MinKey]' ]
        },
        keysExamined: 10,
        seeks: 1,
        dupsTested: 10,
        dupsDropped: 0
      }
    }
  }
}

  nReturned: 10,
  totalKeysExamined: 10,
  totalDocsExamined: 10,

        indexBounds: {
          country_id: [ '[1, 1]' ],
          'order_details.product_id': [ '[15, 15]' ],
          created_at: [ '[MaxKey, MinKey]' ]
        },

mdb> db.orders.find(
   { country_id: 1, order_details: { $elemMatch: { product_id: 15 } } }
 ).sort({ created_at: -1 }).limit(10).explain(`allPlansExecution`).queryPlanner
;
{
  namespace: 'test.orders',
  parsedQuery: {
    '$and': [
      {
        order_details: { '$elemMatch': { product_id: { '$eq': 42 } } }
      },
      { country_id: { '$eq': 1 } }
    ]
  },
  indexFilterSet: false,
  queryHash: '0DAE06A4',
  planCacheShapeHash: '0DAE06A4',
  planCacheKey: 'C3D96884',
  optimizationTimeMillis: 0,
  maxIndexedOrSolutionsReached: false,
  maxIndexedAndSolutionsReached: false,
  maxScansToExplodeReached: false,
  prunedSimilarIndexes: false,
  winningPlan: {
    isCached: false,
    stage: 'LIMIT',
    limitAmount: 10,
    inputStage: {
      stage: 'FETCH',
      filter: {
        order_details: { '$elemMatch': { product_id: { '$eq': 42 } } }
      },
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: {
          country_id: 1,
          'order_details.product_id': 1,
          created_at: -1
        },
        indexName: 'country_id_1_order_details.product_id_1_created_at_-1',
        isMultiKey: true,
        multiKeyPaths: {
          country_id: [],
          'order_details.product_id': [ 'order_details' ],
          created_at: []
        },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          country_id: [ '[1, 1]' ],
          'order_details.product_id': [ '[42, 42]' ],
          created_at: [ '[MaxKey, MinKey]' ]
        }
      }
    }
  },
  rejectedPlans: []
}