Using method op.and with multiple boolean expressions functions slows down my query

Question

In my case I am using both op.or() and op.and() with multiple boolean expressions (about 250 and more). Method op.or() works fine but when I change method in .where() to op.and() with the same list of boolean expressions elapsedTime increases 10x times like from PT5.590023S to PT1M11.772006S. Single boolean expression from my query exported via view.export() is no bigger than that:

{
    "ns": "op",
    "fn": "and",
    "args": [
        {
            "ns": "op",
            "fn": "in",
            "args": [
                {
                    "ns": "op",
                    "fn": "col",
                    "args": [
                        "id"
                    ]
                },
                [
                    1
                ]
            ]
        },
        {
            "ns": "op",
            "fn": "or",
            "args": [
                {
                    "ns": "op",
                    "fn": "eq",
                    "args": [
                        {
                            "ns": "op",
                            "fn": "col",
                            "args": [
                                "type"
                            ]
                        },
                        "First"
                    ]
                },
                {
                    "ns": "op",
                    "fn": "eq",
                    "args": [
                        {
                            "ns": "op",
                            "fn": "col",
                            "args": [
                                "type"
                            ]
                        },
                        "Second"
                    ]
                },
                {
                    "ns": "op",
                    "fn": "eq",
                    "args": [
                        {
                            "ns": "op",
                            "fn": "col",
                            "args": [
                                "type"
                            ]
                        },
                        "Third"
                    ]
                }
            ]
        }
    ]
}

I try to optimize query with multiple where:

     filters.forEach(filter => {
         view = view.where(filter);
     });

and with multiple intersects too but the results were similar to op.and(). Marklogic version is: 10.0-11

I want this query to be optimized but have no idea why op.and() works that slow because it should probably return empty result.

Answer 1

Consider using a scoping query. With the Optic API, a .where clause can take a cts query, which can use the universal or range indexes. In some cases, that works a lot faster. You can use this approach if you have a way to target the value that the column is based on.

For example, let's suppose that target is a distinctive property name in your JSON documents. You could do something like this:

const targetValues = ['valueA', 'valueB', 'valueC'];

op.fromView('mySchema', 'myView')
  .where(cts.jsonPropertyValueQuery('target', targetValues)
  .result();

This can significantly cut down the number of values that MarkLogic has to retrieve from the triples index (the view), often resulting in a faster overall query.