title: MongoDB query optimisation or improve performance
body: <p>I have a collection of products that are around 5.5 Lacks of documents and collection size is 3.3 GB and It's storing data of product details and attributes. All attributes are stored as master keys with datatype array. Now, I want a unique attribute listing by category selected.</p>
  
  <p>I have already query as pr below. already created active as an index. In the first stage, all documents Brand array to one and then fetching a unique value from all the Brand array values. This query is taking 3146 milliseconds which is too much for me. I want to improve my query performance.</p>
  
  <p>In the document, all keys are starting with capital latter which are attributes. and I want unique values of all the attributes which are listed in the document sample.</p>
  
  <p>I am using a local machine which has 256 GB SSD, 8 GB RAM.
  And I also tried on mongoAtlas 4 core, 10 GB space and 16 GB RAM machine. It's also taking the same time. So, I think it's not a hardware issue.</p>
  
  <p>Query</p>
  
  <pre><code>db.products.aggregate([{
      "$match": {
          "active": "1"
      }
  }, {
      "$group": {
          "_id": 0,
          "Brand": {
              "$addToSet": "$Brand"
          }
      }
  }, {
      "$project": {
          "_id": 0,
          "Brand": {
              "$reduce": {
                  "input": "$Brand",
                  "initialValue": [
                      []
                  ],
                  "in": {
                      "$setUnion": ["$$value", "$$this"]
                  }
              }
          }
      }
  }])
  </code></pre>
  
  <p>STAGE 1 OUTPUT</p>
  
  <pre><code>{
      "Brand": [
          ["Design Clinic"],
          ["Laitin Art"],
          ["Matrix International"],
          ["De Majo"],
          ["Verdure Wellness"],
          ["Maxmarko"],
          ["Golden"],
          ["Panseima"],
          ["Akdo"],
          ["Xiangda kitchenware"]
      ]
  }
  </code></pre>
  
  <p>FINAL OUTPUT</p>
  
  <pre><code>{
      "Brand": ["12h", "5th Gallery", "A &amp; X", "A Casa K", "A Lot Of Brasil", "A Lot of Brasil", "ACL", "ADS Lighting", "AL2", "Akdo"]
  }
  </code></pre>
  
  <p>Explain giving me below data</p>
  
  <pre><code>"executionStats" : {
                      "executionSuccess" : true,
                      "nReturned" : 525536,
                      "executionTimeMillis" : 3146,
                      "totalKeysExamined" : 525536,
                      "totalDocsExamined" : 525536,
                      "executionStages" : {
                          "stage" : "FETCH",
                          "nReturned" : 525536,
                          "executionTimeMillisEstimate" : 199,
                          "works" : 525537,
                          "advanced" : 525536,
                          "needTime" : 0,
                          "needYield" : 0,
                          "saveState" : 4143,
                          "restoreState" : 4143,
                          "isEOF" : 1,
                          "docsExamined" : 525536,
                          "alreadyHasObj" : 0,
                          "inputStage" : {
                              "stage" : "IXSCAN",
                              "nReturned" : 525536,
                              "executionTimeMillisEstimate" : 38,
                              "works" : 525537,
                              "advanced" : 525536,
                              "needTime" : 0,
                              "needYield" : 0,
                              "saveState" : 4143,
                              "restoreState" : 4143,
                              "isEOF" : 1,
                              "keyPattern" : {
                                  "active" : 1
                              },
                              "indexName" : "active",
                              "isMultiKey" : false,
                              "multiKeyPaths" : {
                                  "active" : [ ]
                              },
                              "isUnique" : false,
                              "isSparse" : false,
                              "isPartial" : false,
                              "indexVersion" : 2,
                              "direction" : "forward",
                              "indexBounds" : {
                                  "active" : [
                                      "[\"1\", \"1\"]"
                                  ]
                              },
                              "keysExamined" : 525536,
                              "seeks" : 1,
                              "dupsTested" : 0,
                              "dupsDropped" : 0
                          }
                      }
                  }
  </code></pre>
  
  <p>and my document design is as per below.</p>
  
  <pre><code>{
      "_id": "5dedda7a4f015667657654a8",
      "id": "47442",
      "name": "LTZ 19030159 60 61",
      "slug": "ltz-19030159-60-61",
      "model_number": "ltz 19030159 60 61",
      "country_of_mfg": "China",
      "description": "&lt;p&gt;Different elements of colors, shadows, lines and textures, recur the Modern art on the gold foiled hand torn art paper which mounted with the double solid wood frame.&lt;/p&gt;\r\n\r\n&lt;ul&gt;\r\n\t&lt;li&gt;Material: imported American poplar frame+ glass+gold foiled hand torn art paper + MDF backboard&lt;/li&gt;\r\n\t&lt;li&gt;Customization Available for Frame Material, Finish &amp;amp; Size&lt;/li&gt;\r\n\t&lt;li&gt;Sizes\r\n\t&lt;ul&gt;\r\n\t\t&lt;li&gt;L - 600 mm x W - 800 mm&lt;/li&gt;\r\n\t\t&lt;li&gt;L - 700 mm&amp;nbsp;x W - 1000 mm&lt;/li&gt;\r\n\t\t&lt;li&gt;L - 800 mm x W - 1200 mm&lt;/li&gt;\r\n\t&lt;/ul&gt;\r\n\t&lt;/li&gt;\r\n&lt;/ul&gt;",
      "brand_id": "553",
      "price": "0.00",
      "primary_image_url": "https://dt4f7ywfipgvt.cloudfront.net/products_images/47442/optimized/ltz_19030159_60_61-1.jpg",
      "keywords": ["Paintings", "Painting", "picture", "canvas", "sketch", "sketches", "portrait", "drawing", "oil paint", "oil painting", "paint", "paints", "color", "colors", "colour", "depiction", "art", "art work", "decor", "decoration", "decorations", "decorates", "decorative", "indoor", "inside", "in home", "inside home", "for home", "for room", "inside room", "home", "wooden frame painting", "morden art"],
      "new": "0",
      "active": "1",
      "featured": "0",
      "featured_big_flag": "0",
      "uom": "Each",
      "created_datetime": "2019-09-03 09:22:22",
      "category_ids": "14",
      "category_processed": "1",
      "update_date": "2019-09-05 16:35:01",
      "Country": ["China"],
      "Brand": ["Laitin Art"],
      "Frame Material": ["Wood"],
      "Artwork Surface": ["Paper"],
      "Subject": ["Abstract"],
      "Orientation": ["Portrait"],
      "Color": ["Gold", "Multicolor"],
      "brand_data": {
          "id": "553",
          "vendor_id": "520",
          "vendors_brands_id": "0",
          "brand_name": "Laitin Art",
          "active": "1",
          "hide_brand_name": "0",
          "featured": "0",
          "synced": "1",
          "priority": "1"
      },
      "categories_data": [{
          "id": "9337238",
          "top_cat_id": "4",
          "name": "Paintings",
          "breadcrumb": "Rugs &amp; Decor",
          "breadcrumb_full": "Rugs &amp; Decor&gt; Paintings",
          "breadcrumb_html": "&lt;li&gt;&lt;a href='/product-list/rugs-decor/1'&gt;Rugs &amp; Decor&lt;/a&gt; &lt;/li&gt;&lt;li&gt; &lt;a href='/product-list/paintings/1'&gt;Paintings&lt;/a&gt;&lt;/li&gt;",
          "partial_breadcrumb_html": "&lt;li&gt;&lt;a href='/product-list/rugs-decor/1'&gt;Rugs &amp; Decor&lt;/a&gt; &lt;/li&gt;&lt;li&gt; Paintings&lt;/li&gt;",
          "slug": "paintings",
          "type": "category",
          "active": "1",
          "products_count": "1195",
          "category_id": "14",
          "product_id": "47442"
      }, {
          "id": "9337239",
          "name": "Rugs &amp; Decor",
          "breadcrumb": "Rugs &amp; Decor",
          "breadcrumb_full": "Decor&gt; Decor",
          "breadcrumb_html": "&lt;li&gt; Rugs &amp; Decor&lt;/li&gt;",
          "partial_breadcrumb_html": "&lt;li&gt; Rugs &amp; Decor&lt;/li&gt;",
          "slug": "rugs-decor",
          "type": "category",
          "active": "1",
          "products_count": "8379",
          "sub_cat_ids": "8,10,11,13,14,15,839,842,844,845,985,986,1742,1795,1851,1861,1867,1883,1909,1913,1930,1938,1958,1959",
          "category_id": "4",
          "product_id": "47442"
      }],
      "Brand": ["Paintings", "Rugs &amp; Decor"],
      "images": [{
          "id": "81874",
          "product_id": "47442",
          "image_url": "https://dt4f7ywfipgvt.cloudfront.net/products_images/47442/optimized/ltz_19030159_60_61-2.jpg",
          "type": "image",
          "sort_order": "1"
      }],
      "attributes": {
          "Country": {
              "values": ["China"],
              "value_display": ["China"],
              "active": "1",
              "filterable": "1"
          },
          "Brand": {
              "values": ["Laitin Art"],
              "value_display": ["Laitin Art"],
              "active": "1",
              "filterable": "1"
          },
          "Frame Material": {
              "values": ["Wood"],
              "value_display": ["Poplar Wood"],
              "active": "1",
              "filterable": "1"
          },
          "Artwork Surface": {
              "values": ["Paper"],
              "value_display": ["Art Paper"],
              "active": "1",
              "filterable": "1"
          },
          "Style": {
              "values": ["Modern"],
              "value_display": ["Modern"],
              "active": "1",
              "filterable": "1"
          },
          "Frame Type ": {
              "values": ["Framed"],
              "value_display": ["Framed"],
              "active": "1",
              "filterable": "1"
          },
          "Subject": {
              "values": ["Abstract"],
              "value_display": ["Abstract"],
              "active": "1",
              "filterable": "1"
          },
          "Orientation": {
              "values": ["Portrait"],
              "value_display": ["Portrait"],
              "active": "1",
              "filterable": "1"
          },
          "Color": {
              "values": ["Gold", "Multicolor"],
              "value_display": ["Gold", "Multicolor"],
              "active": "1",
              "filterable": "1"
          }
      }
  }
  </code></pre>