Multiple array indexes on OR N1QL query

SQL++
,
1

Hi,

Query:
select * from books WHERE _class = ‘com.org.One’
AND (ANY t IN field1 SATISFIES t IN [‘6577’, ‘ugugug’] END
OR ANY g IN field2 SATISFIES g IN [‘6577’, ‘ugugug’] END)

Data structure:
{
“_class” : “com.org.One”,
“field1” : [“a”, “b”],
“field2” : [“a”, “b”],
“field3” : [“a”, “b”],
}

Indexes defined:
Index1 → CREATE INDEX adv_DISTINCT_field1_class ON books(DISTINCT ARRAY t FOR t in field1 END) WHERE _class = ‘com.org.One’ using GSI with {‘num_replica’:1,‘num_partition’:6};

I am expecting above query to pick above index.

Above index is getting picked on CB version 6.0.2 but not getting picked in 6.6.0 and 6.6.1. (please note the query is having OR condition. With AND condition, index gets always be picked on all versions mentioned above.)

This is a kind of blocker for us to upgrade to 6.6.0 or 6.6.1 from 6.0.2

Thanks
Nitesh

2

In case of OR index doesn’t qualify second part of OR. The behavior in 6.6.0,6.6.1 is right. It should not even worked for 6.0.2 please check explain.

CREATE INDEX adv_DISTINCT_field1_class ON `books` (DISTINCT ARRAY_CONCAT(IFMISSINGORNULL(field1,[]), IFMISSINGORNULL(field2,[])) WHERE `_class` = ‘com.org.One’ using GSI with {‘num_replica’:1,‘num_partition’:6};
select * from books WHERE _class = ‘com.org.One’
AND ANY t IN  ARRAY_CONCAT(IFMISSINGORNULL(field1,[]), IFMISSINGORNULL(field2,[]) SATISFIES t IN [‘6577’, ‘ugugug’] END
3

Hi,
I checked the explain plan. Then only i posted my doubt. Index was neither picked for first part of OR nor picked for second part of OR in 6.6.0 onwards but it was picked in 6.0.2 (which is the current version for my cluster). Please check this behaviour on 6.0.2.

And as you suggested, for OR condition, i have to concat both fields in index and query. I see an issue there.

Let’s say in our data, field1 is having values “a”, “b” and field2 is having values “c”, “d”.
I want to do this query → field1 with [“c”, “d”] OR field2 with [“a”, “b”]… it should ideally give empty results but after concat, data will be fetched.

Please check it.

Thanks
Nitesh

4

Post your explain in 6.0.2, If picked in 6.0.2 , which is wrong in 6.0.2, Is it using UnionScan on two indexes. If you have support agreement, contact support.
When OR clause is there each part of the OR must pick at least one index. all part of the OR’s uses same index uses IndexScan, more than one index does Union Scan.

5

The use case changed here. In original post you are looking [‘6577’, ‘ugugug’] in field1, field2. In that case ARRAY_CONCAT() works. Need to be independent then create two independent ARRAY indexes query does UNION SCAN by using both the indexes.

6

Yes… in case of 6.0.2, i created 2 different indexes and used OR clause as given above. Both indexes were picked with union scan. For 6.6.1, not a single index get picked.

Below Two options are not okay on 6.6.1:

  1. array concat - because i dont want to concat
  2. two independent array index - indexes not picked in OR clause.

Can u please suggest any other way to get this worked?

Thanks
Nitesh

7

Your use case is independent predicate on array, ARRAY_CONCAT() is not option. I already mentioned previous post.
Independent indexes works in couchbase-server-6.6.1-9213.x86_64. Unless you share the EXPLAIN it is hard to know what causing the issue.

CREATE INDEX ix20 ON default(DISTINCT field1 ) WHERE _class = "com.org.One";
CREATE INDEX ix21 ON default(DISTINCT field2 ) WHERE _class = "com.org.One";
EXPLAIN SELECT *
FROM default
WHERE _class = "com.org.One"
     AND (ANY t IN field1 SATISFIES t IN ["f1", "f2"] END
          OR ANY g IN field2 SATISFIES g IN ["f3", "f4"] END);

EXPLAIN SELECT * FROM default WHERE _class = "com.org.One" AND (ANY t IN field1 SATISFIES t IN ["f1", "f2"] END OR ANY g IN field2 SATISFIES g IN ["f3", "f4"] END);
{
    "requestID": "6358ec0c-a56d-4925-be5b-fc4e1925292c",
    "signature": "json",
    "results": [
    {
        "plan": {
            "#operator": "Sequence",
            "~children": [
                {
                    "#operator": "UnionScan",
                    "scans": [
                        {
                            "#operator": "DistinctScan",
                            "scan": {
                                "#operator": "IndexScan3",
                                "index": "ix20",
                                "index_id": "9cd7b639d7603d6d",
                                "index_projection": {
                                    "primary_key": true
                                },
                                "keyspace": "default",
                                "namespace": "default",
                                "spans": [
                                    {
                                        "exact": true,
                                        "range": [
                                            {
                                                "high": "\"f1\"",
                                                "inclusion": 3,
                                                "low": "\"f1\""
                                            }
                                        ]
                                    },
                                    {
                                        "exact": true,
                                        "range": [
                                            {
                                                "high": "\"f2\"",
                                                "inclusion": 3,
                                                "low": "\"f2\""
                                            }
                                        ]
                                    }
                                ],
                                "using": "gsi"
                            }
                        },
                        {
                            "#operator": "DistinctScan",
                            "scan": {
                                "#operator": "IndexScan3",
                                "index": "ix21",
                                "index_id": "a6663895d37c8aa3",
                                "index_projection": {
                                    "primary_key": true
                                },
                                "keyspace": "default",
                                "namespace": "default",
                                "spans": [
                                    {
                                        "exact": true,
                                        "range": [
                                            {
                                                "high": "\"f3\"",
                                                "inclusion": 3,
                                                "low": "\"f3\""
                                            }
                                        ]
                                    },
                                    {
                                        "exact": true,
                                        "range": [
                                            {
                                                "high": "\"f4\"",
                                                "inclusion": 3,
                                                "low": "\"f4\""
                                            }
                                        ]
                                    }
                                ],
                                "using": "gsi"
                            }
                        }
                    ]
                },
                {
                    "#operator": "Fetch",
                    "keyspace": "default",
                    "namespace": "default"
                },
                {
                    "#operator": "Parallel",
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "Filter",
                                "condition": "(((`default`.`_class`) = \"com.org.One\") and (any `t` in (`default`.`field1`) satisfies (`t` in [\"f1\", \"f2\"]) end or any `g` in (`default`.`field2`) satisfies (`g` in [\"f3\", \"f4\"]) end))"
                            },
                            {
                                "#operator": "InitialProject",
                                "result_terms": [
                                    {
                                        "expr": "self",
                                        "star": true
                                    }
                                ]
                            },
                            {
                                "#operator": "FinalProject"
                            }
                        ]
                    }
                }
            ]
        },
        "text": "SELECT * FROM default WHERE _class = \"com.org.One\" AND (ANY t IN field1 SATISFIES t IN [\"f1\", \"f2\"] END OR ANY g IN field2 SATISFIES g IN [\"f3\", \"f4\"] END);"
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "4.091057ms",
        "executionTime": "4.00652ms",
        "resultCount": 1,
        "resultSize": 5224
    }
}
8

Yes. You are right. OR clause worked by tweaking the indexes little bit. But now, i got another problem.

Query:
select * from books
USE INDEX (adv_DISTINCT_field1_class,
adv_DISTINCT_field2_class,
adv_DISTINCT_field3_field4_class USING GSI)
WHERE _class = ‘com.org.One’
AND (ANY t IN field1 SATISFIES t IN [“a”, “b”] END
OR ANY t IN field2 SATISFIES t IN [“c”, “d”] END)
AND ANY t IN ARRAY_CONCAT(IFMISSINGORNULL(field3, ),
IFMISSINGORNULL(field4, )) SATISFIES t IN [‘hvhv’,‘hvh’] END

Data structure:

{
“_class” : “com.org.One”,
“field1” : [“a”, “b”],
“field2” : [“a”, “b”],
“field3” : [“a”, “b”],
“field4” : [“a”, “b”],
}

Indexes:
Index1 → CREATE INDEX adv_DISTINCT_field1_class ON books(DISTINCT ARRAYtFOR t infield1END) WHERE_class = ‘com.org.One’ using GSI with {‘num_replica’:1,‘num_partition’:6};
Index2 → CREATE INDEX adv_DISTINCT_field2_class ON books(DISTINCT ARRAYtFOR t infield2END) WHERE_class = ‘com.org.One’ using GSI with {‘num_replica’:1,‘num_partition’:6};
Index3 → CREATE INDEX adv_DISTINCT_field3_field4_class
ON books (DISTINCT ARRAY t FOR t IN ARRAY_CONCAT( IFMISSINGORNULL(field3, ),
IFMISSINGORNULL(field4,)) END)
PARTITION BY HASH(aggregateId)
WHERE _class = ‘com.org.One’
using GSI with {‘num_replica’:1,‘num_partition’:6};

In above query, all 3 indexes picked in 6.0.2 but only index3 picked in 6.6.1. Explain plans are given below:

Cluster version - Enterprise Edition 6.0.2 build 2413

    {
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "UnionScan",
        "scans": [
          {
            "#operator": "IntersectScan",
            "scans": [
              {
                "#operator": "DistinctScan",
                "scan": {
                  "#operator": "IndexScan3",
                  "index": "adv_DISTINCT_field1_class",
                  "index_id": "ea340b5aa8a678e9",
                  "index_projection": {
                    "primary_key": true
                  },
                  "keyspace": "books",
                  "namespace": "default",
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"a\"",
                          "inclusion": 3,
                          "low": "\"a\""
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"b\"",
                          "inclusion": 3,
                          "low": "\"b\""
                        }
                      ]
                    }
                  ],
                  "using": "gsi"
                }
              },
              {
                "#operator": "DistinctScan",
                "scan": {
                  "#operator": "IndexScan3",
                  "index": "adv_DISTINCT_field3_field4_class",
                  "index_id": "cf135d11c8d1fac1",
                  "index_projection": {
                    "primary_key": true
                  },
                  "keyspace": "books",
                  "namespace": "default",
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"hvh\"",
                          "inclusion": 3,
                          "low": "\"hvh\""
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"hvhv\"",
                          "inclusion": 3,
                          "low": "\"hvhv\""
                        }
                      ]
                    }
                  ],
                  "using": "gsi"
                }
              }
            ]
          },
          {
            "#operator": "IntersectScan",
            "scans": [
              {
                "#operator": "DistinctScan",
                "scan": {
                  "#operator": "IndexScan3",
                  "index": "adv_DISTINCT_field2_class",
                  "index_id": "4651370cfb1ee060",
                  "index_projection": {
                    "primary_key": true
                  },
                  "keyspace": "books",
                  "namespace": "default",
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"c\"",
                          "inclusion": 3,
                          "low": "\"c\""
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"d\"",
                          "inclusion": 3,
                          "low": "\"d\""
                        }
                      ]
                    }
                  ],
                  "using": "gsi"
                }
              },
              {
                "#operator": "DistinctScan",
                "scan": {
                  "#operator": "IndexScan3",
                  "index": "adv_DISTINCT_field3_field4_class",
                  "index_id": "cf135d11c8d1fac1",
                  "index_projection": {
                    "primary_key": true
                  },
                  "keyspace": "books",
                  "namespace": "default",
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"hvh\"",
                          "inclusion": 3,
                          "low": "\"hvh\""
                        }
                      ]
                    },
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "\"hvhv\"",
                          "inclusion": 3,
                          "low": "\"hvhv\""
                        }
                      ]
                    }
                  ],
                  "using": "gsi"
                }
              }
            ]
          }
        ]
      },
      {
        "#operator": "Fetch",
        "keyspace": "books",
        "namespace": "default"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "((((`books`.`_class`) = \"com.org.One.\") and (any `t` in (`books`.`field1`) satisfies (`t` in [\"a\", \"b\"]) end or any `t` in (`books`.`field2`) satisfies (`t` in [\"c\", \"d\"]) end)) and any `t` in array_concat(ifmissingornull(books.field3, []), ifmissingornull(books.field4, [])) satisfies (`t` in [\"hvhv\", \"hvh\"]) end)"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "self",
                  "star": true
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "select * from books
USE INDEX (adv_DISTINCT_field1_class, 
adv_DISTINCT_field2_class,
adv_DISTINCT_field3_field4_class USING GSI)
WHERE _class = 'com.org.One' 
AND (ANY t IN field1 SATISFIES t IN ["a", "b"] END 
 OR ANY t IN field2 SATISFIES t IN ["c", "d"] END)
AND ANY t IN ARRAY_CONCAT(IFMISSINGORNULL(field3, []),
IFMISSINGORNULL(field4, [])) SATISFIES t IN ['hvhv','hvh'] END"
}

Cluster version - Enterprise Edition 6.6.1 build 9213

{
"#operator": "Sequence",
"~children": [
    {
        "#operator": "DistinctScan",
        "scan": {
            "#operator": "IndexScan3",
            "index": "adv_DISTINCT_field3_field4_class",
            "index_id": "7b7044120ce10ac1",
            "index_projection": {
                "primary_key": true
            },
            "keyspace": "books",
            "namespace": "default",
            "spans": [
                {
                    "exact": true,
                    "range": [
                        {
                            "high": "\"hvh\"",
                            "inclusion": 3,
                            "low": "\"hvh\""
                        }
                    ]
                },
                {
                    "exact": true,
                    "range": [
                        {
                            "high": "\"hvhv\"",
                            "inclusion": 3,
                            "low": "\"hvhv\""
                        }
                    ]
                }
            ],
            "using": "gsi"
        }
    },
    {
        "#operator": "Fetch",
        "keyspace": "books",
        "namespace": "default"
    },
    {
        "#operator": "Parallel",
        "~child": {
            "#operator": "Sequence",
            "~children": [
                {
                    "#operator": "Filter",
                    "condition": "((((`books`.`_class`) = \"com.org.One.\") and (any `t` in (`books`.`field1`) satisfies (`t` in [\"a\", \"b\"]) end or any `t` in (`books`.`field2`) satisfies (`t` in [\"c\", \"d\"]) end)) and any `t` in array_concat(ifmissingornull(books.field3, []), ifmissingornull(books.field4, [])) satisfies (`t` in [\"hvhv\", \"hvh\"]) end)"
                },
                {
                    "#operator": "InitialProject",
                    "result_terms": [
                        {
                            "expr": "self",
                            "star": true
                        }
                    ]
                },
                {
                    "#operator": "FinalProject"
                }
            ]
        }
    }
]
}
9

6.0.2 it using IntersectScan on top of UnionScan
6.6.1 It avoided IntersectScan . Some times IntersectScan performs poorly. As much as possible it avoids.

10
  1. I can see that first intersect scans are happening and then union scan in 6.0.2 which is reverse of what you mentioned.
  2. If 6.6.1 avoids intersect scan then how to achieve below query using correct indexes.

select * from books
USE INDEX (adv_DISTINCT_field1_class,
adv_DISTINCT_field2_class,
adv_DISTINCT_field3_field4_class USING GSI)
WHERE _class = ‘com.org.One’
AND (ANY t IN field1 SATISFIES t IN [“a”, “b”] END
OR ANY t IN field2 SATISFIES t IN [“c”, “d”] END)
AND ANY t IN ARRAY_CONCAT(IFMISSINGORNULL(field3, ),
IFMISSINGORNULL(field4, )) SATISFIES t IN [‘hvhv’,‘hvh’] END

11

Query not need to use every index.

12

I agree that all indexes not to be picked but if i dont have primary index, then desired indexes need to be picked. So, can you suggest any workaround to be get them picked?

And you were saying that IntersectScan performs poorly. It means is there any fixes went in 6.6.1 or lesser versions for that becasue of which scan is not picked in 6.6.1 but is picked in 6.0.2?