Group by and count takes too much time

SQL++
1

Hi,

I have following query

SELECT COUNT(pin_type) AS count,pin_type FROM pins USE INDEX(user_pins USING GSI) WHERE pin_type IN[“atlas”] AND user_name=“mustanish123” GROUP BY pin_type;

Above query uses following

CREATE INDEX user_pins ON pins(pin_type,user_name,status)

Total document in bucket is 60000 and It takes more than 100ms to run

Corresponding to above user their are more than 6000 entries but when I select any user having less entries It takes 5ms.

What should I do to further optimize it.

2

Hi,

Could you please post the result of :
explain SELECT COUNT(pin_type) AS count,pin_type FROM pins USE INDEX(user_pins USING GSI) WHERE pin_type IN[“atlas”] AND user_name=“mustanish123” GROUP BY pin_type;

3

Which couchbase version are you using? In 5.5, the group by and aggregates are going to be pushed down to index service and will be much more efficient. More details here.

You can try out the developer build from here.

4

@deniswsrosa

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexScan2",
        "covers": [
          "cover ((`pins`.`pin_type`))",
          "cover ((`pins`.`user_name`))",
          "cover ((`pins`.`status`))",
          "cover ((meta(`pins`).`id`))"
        ],
        "index": "user_pins",
        "index_id": "bb72a30d8116b28b",
        "index_projection": {
          "entry_keys": [
            0,
            1
          ]
        },
        "keyspace": "pins",
        "namespace": "default",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "high": "\"atlas\"",
                "inclusion": 3,
                "low": "\"atlas\""
              },
              {
                "high": "\"mustanish123\"",
                "inclusion": 3,
                "low": "\"mustanish123\""
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "((cover ((`pins`.`pin_type`)) in [\"atlas\"]) and (cover ((`pins`.`user_name`)) = \"mustanish123\"))"
            },
            {
              "#operator": "InitialGroup",
              "aggregates": [
                "count(cover ((`pins`.`pin_type`)))"
              ],
              "group_keys": [
                "cover ((`pins`.`pin_type`))"
              ]
            }
          ]
        }
      },
      {
        "#operator": "IntermediateGroup",
        "aggregates": [
          "count(cover ((`pins`.`pin_type`)))"
        ],
        "group_keys": [
          "cover ((`pins`.`pin_type`))"
        ]
      },
      {
        "#operator": "FinalGroup",
        "aggregates": [
          "count(cover ((`pins`.`pin_type`)))"
        ],
        "group_keys": [
          "cover ((`pins`.`pin_type`))"
        ]
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "as": "count",
                  "expr": "count(cover ((`pins`.`pin_type`)))"
                },
                {
                  "expr": "cover ((`pins`.`pin_type`))"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT COUNT(pin_type) AS count,pin_type FROM pins USE INDEX(user_pins USING GSI) WHERE pin_type IN['atlas'] AND user_name='mustanish123' GROUP BY pin_type;"
}
5

@deepkaran.salooja I am using version 5.0.