Index is not being used when using a not equals where clause

SQL++
,
1

Hi,

I have created the following index:
create index delete_data ON data (_type, source) WHERE _type = "spr" AND source != "grr"
And I would like for it to be used with the following query:
select * from data WHERE _type = "spr" AND source != "grr"

But running an explain on this query shows that it still uses a full-bucket scan.

[
  {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "PrimaryScan",
        "index": "data-full-bucket",
        "keyspace": "data",
        "namespace": "default",
        "using": "gsi"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Fetch",
              "keyspace": "data",
              "namespace": "default"
            },
            {
              "#operator": "Filter",
              "condition": "(((`data`.`_type`) = \"spr\") and (not ((`data`.`source`) = \"grr\")))"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "self",
                  "star": true
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  }
]

My indices seems to also not work if I am trying to use the following index:
create index delete_data ON data (_type, source, job_id) WHERE _type = "spr" AND source != "grr"
With the query:
select * from data WHERE _type = "spr" AND source != "grr" AND job_id NOT IN ["1","2","3","4"]
It will still use the full-bucket scan.

Any ideas what I am doing wrong?
Couchbase Version: Version: 4.1.0-5005 Community Edition (build-5005)
I have no option to use 4.5 at the moment.

Thank you very much for the help.

2

You should at least with 4.5.1. Those improvements have been made and it should use your indexes directly.

For 4,1, try the following query.

SELECT *
FROM data
WHERE _type = "spr" AND source < "grr"
UNION ALL
SELECT *
FROM data
WHERE _type = "spr" AND source > "grr";

You should also create two separate indexes to match the two query WHERE clauses.