Slow Count Query Using N!QL


#1

Hi,

I have 63k records in my bucket and I am trying to fetch counts based on type. Below is my query

SELECT COUNT(type) AS count, type FROM events USE INDEX(my-contribution USING GSI) WHERE user_id IS NOT MISSING AND type IN[‘added’, ‘edited’] GROUP BY type;

Running this query takes more than 500ms.

Explain for the same is

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexScan3",
        "covers": [
          "cover ((`events`.`user_id`))",
          "cover ((`events`.`type`))",
          "cover ((meta(`events`).`id`))"
        ],
        "index": "my-contribution",
        "index_id": "7e05677ff098f217",
        "index_projection": {
          "entry_keys": [
            0,
            1
          ]
        },
        "keyspace": "events",
        "namespace": "default",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "inclusion": 1,
                "low": "null"
              },
              {
                "high": "\"added\"",
                "inclusion": 3,
                "low": "\"added\""
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "inclusion": 1,
                "low": "null"
              },
              {
                "high": "\"edited\"",
                "inclusion": 3,
                "low": "\"edited\""
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "((cover ((`events`.`user_id`)) is not missing) and (cover ((`events`.`type`)) in [\"added\", \"edited\"]))"
            },
            {
              "#operator": "InitialGroup",
              "aggregates": [
                "count(cover ((`events`.`type`)))"
              ],
              "group_keys": [
                "cover ((`events`.`type`))"
              ]
            }
          ]
        }
      },
      {
        "#operator": "IntermediateGroup",
        "aggregates": [
          "count(cover ((`events`.`type`)))"
        ],
        "group_keys": [
          "cover ((`events`.`type`))"
        ]
      },
      {
        "#operator": "FinalGroup",
        "aggregates": [
          "count(cover ((`events`.`type`)))"
        ],
        "group_keys": [
          "cover ((`events`.`type`))"
        ]
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "as": "count",
                  "expr": "count(cover ((`events`.`type`)))"
                },
                {
                  "expr": "cover ((`events`.`type`))"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT COUNT(type) AS count, type FROM events USE INDEX(`my-contribution` USING GSI) WHERE user_id IS NOT MISSING AND \ntype IN['added', 'edited'] GROUP BY type;"
}

What should I do improve the performance of this query


#2

Use this index

CREATE INDEX ix1 ON events (type, user_id);

Follow https://blog.couchbase.com/understanding-index-grouping-aggregation-couchbase-n1ql-query/


#3

I am using Community Edition 6.0.0 build 1693 on my local machine and on production I am using Community Edition 5.0.1 build 5003.

I created above INDEX on both servers but I do not see any difference in performance. It is still taking around 500ms. I even imported demo travel-sample bucket, created mentioned index and ran mentioned query , The blog claims that it will take somewhere around 13ms but it is taking more than 250ms.

Explain for my query using index suggested by you

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexScan3",
        "covers": [
          "cover ((`events`.`type`))",
          "cover ((`events`.`user_id`))",
          "cover ((meta(`events`).`id`))"
        ],
        "index": "ix1",
        "index_id": "c22d2a2a7cc6fbae",
        "index_projection": {
          "entry_keys": [
            0,
            1
          ]
        },
        "keyspace": "events",
        "namespace": "default",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "high": "\"added\"",
                "inclusion": 3,
                "low": "\"added\""
              },
              {
                "inclusion": 1,
                "low": "null"
              }
            ]
          },
          {
            "exact": true,
            "range": [
              {
                "high": "\"edited\"",
                "inclusion": 3,
                "low": "\"edited\""
              },
              {
                "inclusion": 1,
                "low": "null"
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "((cover ((`events`.`user_id`)) is not missing) and (cover ((`events`.`type`)) in [\"added\", \"edited\"]))"
            },
            {
              "#operator": "InitialGroup",
              "aggregates": [
                "count(cover ((`events`.`type`)))"
              ],
              "group_keys": [
                "cover ((`events`.`type`))"
              ]
            }
          ]
        }
      },
      {
        "#operator": "IntermediateGroup",
        "aggregates": [
          "count(cover ((`events`.`type`)))"
        ],
        "group_keys": [
          "cover ((`events`.`type`))"
        ]
      },
      {
        "#operator": "FinalGroup",
        "aggregates": [
          "count(cover ((`events`.`type`)))"
        ],
        "group_keys": [
          "cover ((`events`.`type`))"
        ]
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "as": "count",
                  "expr": "count(cover ((`events`.`type`)))"
                },
                {
                  "expr": "cover ((`events`.`type`))"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT COUNT(type) AS count, type FROM events USE INDEX(`ix1` USING GSI) WHERE user_id IS NOT MISSING AND \ntype IN['added', 'edited'] GROUP BY type;"
}

Query Which I ran from blog

SELECT t.type, COUNT(type) AS cnt FROM travel-sample AS t WHERE t.type IS NOT NULL GROUP BY t.type;

Index I used from blog

CREATE INDEX idx_ts_type_country_city ON travel-sample (type, country, city);

Explain for the query

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexScan3",
        "as": "t",
        "covers": [
          "cover ((`t`.`type`))",
          "cover ((meta(`t`).`id`))"
        ],
        "index": "def_type",
        "index_id": "e6315299f80e4c8f",
        "index_projection": {
          "entry_keys": [
            0
          ]
        },
        "keyspace": "travel-sample",
        "namespace": "default",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "inclusion": 0,
                "low": "null"
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "(cover ((`t`.`type`)) is not null)"
            },
            {
              "#operator": "InitialGroup",
              "aggregates": [
                "count(cover ((`t`.`type`)))"
              ],
              "group_keys": [
                "cover ((`t`.`type`))"
              ]
            }
          ]
        }
      },
      {
        "#operator": "IntermediateGroup",
        "aggregates": [
          "count(cover ((`t`.`type`)))"
        ],
        "group_keys": [
          "cover ((`t`.`type`))"
        ]
      },
      {
        "#operator": "FinalGroup",
        "aggregates": [
          "count(cover ((`t`.`type`)))"
        ],
        "group_keys": [
          "cover ((`t`.`type`))"
        ]
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "cover ((`t`.`type`))"
                },
                {
                  "as": "cnt",
                  "expr": "count(cover ((`t`.`type`)))"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT t.type, COUNT(type) AS cnt FROM `travel-sample` AS t WHERE t.type IS NOT NULL GROUP BY t.type;"
}

#4

Few line above Performance section mentioned this. Index grouping and aggregation is supported in Enterprise Edition only.


#5

So there is no way to improve performance on community edition. Thanks for your help