N1QL query 'like' takes long time to finish

SQL++
1

Hi,

I’m using Couchbase 6.5 and having issue with below query:

select username
from myBucket alias WHERE alias.SRC_TABLE_NAME = ‘TABLENAME’
and alias.username like ‘%KENDEDES HALIM%’
limit 10 offset 0;

Here’s my index:

CREATE INDEX idx-02 ON myBucket(username,num,znpwp) WHERE (SRC_TABLE_NAME = ‘TABLENAME’)

Explain Plan:

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1,
    "execTime": "2.763µs"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "execTime": "24.539µs",
        "servTime": "1.827831ms"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:myBucket",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "3.931µs"
        },
        "~children": [
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 2,
              "execTime": "138.841µs",
              "kernTime": "166ns",
              "state": "running"
            },
            "~children": [
              {
                "#operator": "IndexScan3",
                "#stats": {
                  "#itemsOut": 1507163,
                  "#phaseSwitches": 6028655,
                  "execTime": "3.326656889s",
                  "kernTime": "730.57638ms",
                  "servTime": "815.203351ms"
                },
                "as": "alias",
                "covers": [
                  "cover ((`alias`.`username`))",
                  "cover ((`alias`.`num`))",
                  "cover ((`alias`.`znpwp`))",
                  "cover ((meta(`alias`).`id`))"
                ],
                "filter_covers": {
                  "cover ((`alias`.`SRC_TABLE_NAME`))": "TABLENAME"
                },
                "index": "idx-myBucket-TABLENAME-02",
                "index_id": "ab24b7d56f23cae8",
                "index_projection": {
                  "entry_keys": [
                    0
                  ]
                },
                "keyspace": "myBucket",
                "namespace": "default",
                "spans": [
                  {
                    "range": [
                      {
                        "high": "[]",
                        "inclusion": 1,
                        "low": "\"\""
                      }
                    ]
                  }
                ],
                "using": "gsi",
                "#time_normal": "00:04.141",
                "#time_absolute": 4.14186024
              },
              {
                "#operator": "Sequence",
                "#stats": {
                  "#phaseSwitches": 2,
                  "execTime": "244.248µs",
                  "kernTime": "201ns",
                  "state": "running"
                },
                "~children": [
                  {
                    "#operator": "Filter",
                    "#stats": {
                      "#itemsIn": 1507163,
                      "#itemsOut": 1,
                      "#phaseSwitches": 3014331,
                      "execTime": "3.953779077s",
                      "kernTime": "918.72374ms"
                    },
                    "condition": "((cover ((`alias`.`SRC_TABLE_NAME`)) = \"TABLENAME\") and (cover ((`alias`.`username`)) like \"%KENDEDES HALIM%\"))",
                    "#time_normal": "00:03.953",
                    "#time_absolute": 3.953779077
                  },
                  {
                    "#operator": "InitialProject",
                    "#stats": {
                      "#itemsIn": 1,
                      "#itemsOut": 1,
                      "#phaseSwitches": 8,
                      "execTime": "44.508µs",
                      "kernTime": "4.872472538s"
                    },
                    "result_terms": [
                      {
                        "expr": "cover ((`alias`.`username`))"
                      }
                    ],
                    "#time_normal": "00:00.000",
                    "#time_absolute": 0.000044508
                  },
                  {
                    "#operator": "FinalProject",
                    "#stats": {
                      "#itemsIn": 1,
                      "#itemsOut": 1,
                      "#phaseSwitches": 3,
                      "execTime": "3.205µs"
                    },
                    "#time_normal": "00:00.000",
                    "#time_absolute": 0.0000032050000000000002
                  }
                ],
                "#time_normal": "00:00.000",
                "#time_absolute": 0.00024424799999999997
              }
            ],
            "#time_normal": "00:00.000",
            "#time_absolute": 0.000138841
          },
          {
            "#operator": "Limit",
            "#stats": {
              "#itemsIn": 1,
              "#itemsOut": 1,
              "#phaseSwitches": 4,
              "execTime": "3.701µs",
              "kernTime": "2.651µs"
            },
            "expr": "10",
            "#time_normal": "00:00.000",
            "#time_absolute": 0.0000037010000000000002
          }
        ],
        "#time_normal": "00:00.000",
        "#time_absolute": 0.000003931
      },
      "#time_normal": "00:00.001",
      "#time_absolute": 0.00185237
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 1,
        "#itemsOut": 1,
        "#phaseSwitches": 5,
        "execTime": "36.968µs",
        "kernTime": "4.874377665s"
      },
      "#time_normal": "00:00.000",
      "#time_absolute": 0.000036968
    }
  ],
  "~versions": [
    "6.5.0-N1QL",
    "6.5.1-6299-enterprise"
  ],
  "#time_normal": "00:00.000",
  "#time_absolute": 0.000002763
}

FYI total data in my bucket is 415 mio, and total data for that TABLENAME is 1507163
Query result is around 5s

How to speed up the performance?

2 

                    "#operator": "Filter",
                    "#stats": {
                      "#itemsIn": 1507163,
                      "#itemsOut": 1,
                      "#phaseSwitches": 3014331,
                      "execTime": "3.953779077s",
                      "kernTime": "918.72374ms"
                    }

As you can see IndexScan is (full scan) produced 1.5M and eliminated by filter all of them except 1. This is due to leading wildcard.

You are using LIKE and leading wildcard. The options are limited. You may not able to improve.
Checkout A Couchbase Index Technique for LIKE Predicates With Wildcard - DZone Database . Unfortunately that may not help because you have 450M in bucket, 1.5M of TABLENAME. If use that technique, index can grow 1B items.

Also try Partition index may reduce some time.