Multiple Array Search In Single Query Does Not Support Indexing on `OR` Operator

SQL++
, ,
1

Its kind of strange, when I use a OR operator in N1QL Query then The Query does not seems to pick up the Index !! However If i use AND operator then The Query picks up the both Array Index Perfectly … Am i missing something ?

Here is the result of Explain , while using AND Operator

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "IntersectScan",
          "scans": [
            {
              "#operator": "DistinctScan",
              "scan": {
                "#operator": "IndexScan",
                "index": "name_given",
                "index_id": "20ae17f517dbd7fc",
                "keyspace": "neuron",
                "namespace": "default",
                "spans": [
                  {
                    "Range": {
                      "High": [
                        "[]"
                      ],
                      "Inclusion": 1,
                      "Low": [
                        "\"\""
                      ]
                    }
                  }
                ],
                "using": "gsi"
              }
            },
            {
              "#operator": "DistinctScan",
              "scan": {
                "#operator": "IndexScan",
                "index": "family",
                "index_id": "a0c2ca3fbf0846cf",
                "keyspace": "neuron",
                "namespace": "default",
                "spans": [
                  {
                    "Range": {
                      "High": [
                        "[]"
                      ],
                      "Inclusion": 1,
                      "Low": [
                        "\"\""
                      ]
                    }
                  }
                ],
                "using": "gsi"
              }
            },
            {
              "#operator": "DistinctScan",
              "scan": {
                "#operator": "IndexScan",
                "index": "name_family",
                "index_id": "c5ef7d993e223af2",
                "keyspace": "neuron",
                "namespace": "default",
                "spans": [
                  {
                    "Range": {
                      "High": [
                        "[]"
                      ],
                      "Inclusion": 1,
                      "Low": [
                        "\"\""
                      ]
                    }
                  }
                ],
                "using": "gsi"
              }
            }
          ]
        },
        {
          "#operator": "Fetch",
          "as": "r",
          "keyspace": "neuron",
          "namespace": "default"
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Filter",
                "condition": "((((`r`.`resourceType`) = \"Patient\") and any `a0` in (`r`.`name`) satisfies ((`a0`.`family`) like \"%M\") end) and any `a0` in (`r`.`name`) satisfies any `a1` in (`a0`.`given`) satisfies (`a1` like \"%M\") end end)"
              },
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "expr": "self",
                    "star": true
                  }
                ]
              },
              {
                "#operator": "FinalProject"
              }
            ]
          }
        }
      ]
    },
    "text": "SELECT * FROM `neuron` as r WHERE  r.resourceType = 'Patient' and ANY a0 IN r.name SATISFIES a0.`family` like '%M' END AND ANY a0 IN r.name SATISFIES ANY a1 IN a0.`given` SATISFIES a1 like '%M' END END"
  }
]

And Here is the Same Query with OR Operator . Not picking the Index even if i explicitly use USE INDEX()

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "PrimaryScan",
          "index": "#primary",
          "keyspace": "neuron",
          "namespace": "default",
          "using": "gsi"
        },
        {
          "#operator": "Fetch",
          "as": "r",
          "keyspace": "neuron",
          "namespace": "default"
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Filter",
                "condition": "((((`r`.`resourceType`) = \"Patient\") and any `a0` in (`r`.`name`) satisfies ((`a0`.`family`) like \"%M\") end) or any `a0` in (`r`.`name`) satisfies any `a1` in (`a0`.`given`) satisfies (`a1` like \"%M\") end end)"
              },
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "expr": "self",
                    "star": true
                  }
                ]
              },
              {
                "#operator": "FinalProject"
              }
            ]
          }
        }
      ]
    },
    "text": "SELECT * FROM `neuron` as r WHERE  r.resourceType = 'Patient' and ANY a0 IN r.name SATISFIES a0.`family` like '%M' END OR ANY a0 IN r.name SATISFIES ANY a1 IN a0.`given` SATISFIES a1 like '%M' END END"
  }
]

The Condition is I need to search Multiple array at once with OR , The result could be on any array .
N1QL is not letting me indexing multiple array on single Create Index Query . So I have created index for each array that i am about to search ? Am i Wrong ?

Then I am using OR operator to query the Indexed Result .

2

Couchbase Index uses B-tree, indexer doesn’t maintain MISSING values on leading key and each OR clause needs to have leading key is predicate. Without that query cannot choose the index because it needs to produce MISSING values.

Before 4.6.2 In your case you can create two array indexes and Use UNION ALL if duplicates okay if not UNION predicate represent each OR clause.

SELECT * FROM `neuron` as r WHERE  (r.resourceType = 'Patient' and ANY a0 IN r.name SATISFIES a0.`family` like '%M' END)
UNION 
SELECT * FROM `neuron` as r WHERE  ( r.resourceType = 'Patient' and  ANY a0 IN r.name SATISFIES ANY a1 IN a0.`given` SATISFIES a1 like '%M' END END);

After 4.6.2 your original query picks different indexes and does UnionScan

SELECT * FROMneuronas r WHERE (r.resourceType = 'Patient' and ANY a0 IN r.name SATISFIES a0.familylike '%M' END) OR ( r.resourceType = 'Patient' and ANY a0 IN r.name SATISFIES ANY a1 IN a0.givenSATISFIES a1 like '%M' END END);

3

@vsr1 Thank You . That works Perfectly !!
I am developing a N1QL Query Generator . It helps a Lot

I hope the community could better optimize it , I am still writing query like old SQL, I am New to N1QL , Learning a Lot …

1 Like
4

May be you want to take look Prerna Manaktala - DZone Member cc @Prerna.Manaktala, @keshav_m