Attribute Pattern - Should I limit the number of attributes?

Working with Data
, ,
3

Hi @Callum_Boyd,

The proposed new document is the anti-pattern that leads to the Attribute Pattern.

Better document would be:

{
    "name": "Product Name",
    "average_prices": [
        {"days": 90, "price": 4},
        {"days": 180, "price": 8},
        {"days": 270, "price": 9},
        {"days": 365, "price": 10},
        {"days": 455, "price": 10},
        {"days": 545, "price": 6},
        {"days": 635, "price": 8},
        {"days": 730, "price": 5},
        {"days": 820, "price": 10},
        {"days": 910, "price": 12},
        {"days": 1000, "price": 18},
        {"days": 1095, "price": 15}
    ]
}

Also your query doesn’t work with that document (no “k” and no “v” keys). And $elemMatch only works on an array.

Index on my doc would be {"average_prices.days": 1, "average_prices.price": 1}

This index would be used by this query:

db.coll.find({
  average_prices: {
    $elemMatch: {
      days: 270, 
      price: {$gt: 8, $lt: 10}
    }
  }
})

This is what the winning plan from an explain looks like:

winningPlan: {
      stage: 'FETCH',
      filter: {
        average_prices: {
          '$elemMatch': {
            '$and': [
              { days: { '$eq': 270 } },
              { price: { '$lt': 10 } },
              { price: { '$gt': 8 } }
            ]
          }
        }
      },
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { 'average_prices.days': 1, 'average_prices.price': 1 },
        indexName: 'average_prices.days_1_average_prices.price_1',
        isMultiKey: true,
        multiKeyPaths: {
          'average_prices.days': [ 'average_prices' ],
          'average_prices.price': [ 'average_prices' ]
        },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          'average_prices.days': [ '[270, 270]' ],
          'average_prices.price': [ '(8, 10)' ]
        }
      }
    }

IXSCAN => FETCH is the best we can do here.

About the performances, the index will add one extra entry for each new element in the array (== each new average price). So if you have 1M docs and each have 10 prices => 10M entries in the index.
It’s not really a problem as long as you have enough RAM to support it but it’s going to be a problem if you decide to have 1000 prices in each doc.

This also means that each insert in this collection will now generate 1 write operation in the collection and 10 write ops in the index. Nothing alarming here.

Cheers,
Maxime.

2 Likes