Why OR logical operator affects the execution time in a diffirent way than AND operator?

SQL++
,
1

I’ve noticed that a query with two conditions combined with OR logical operator, is quicker than with the use of AND logical operator.

The queries i’ve checked have the exact same conditions combined with AND or OR operator. Why a query with conditions combined with OR operator is nuch quicker?

2

Do the explain plans show the same index use? Can you give examples of your conditions/queries?

Conceptually, AND requires evaluation of all terms; OR can stop on the first positive - so less work. How much of a difference I would expect would depend on how long each of the terms takes to evaluate.

HTH.

3

Yes, the explain plans have the same index use which is a primary index. I do not use any other index in my database.

An example query is the following =>OR operator

SELECT name, title
FROM mydb
WHERE ANY relation IN relationship SATISFIES relation.id = “id-2”
AND
(ANY relation IN … SATISFIES … END)
OR
(ANY relation IN … SATISFIES … END)
END

=> AND operator

SELECT name, title
FROM mydb
WHERE ANY relation IN relationship SATISFIES relation.id = “id-2”
AND
(ANY relation IN … SATISFIES … END)
AND
(ANY relation IN … SATISFIES … END)
END

4

Please provide complete EXPLAIN. If you have only primary index it should have single index scan.
As @dh mentioned you are iterating array many times it all depends on condition.

ANY relation IN relationship SATISFIES relation.id = “id-2” END
AND ANY relation IN relationship SATISFIES relation.id = “id-2” END
means relationship must have both values. If array index it needs to do two times scan and do intersectScan.

5 
{
    "#operator": "Sequence",
    "~children": [
        {
            "#operator": "PrimaryScan3",
            "as": "m",
            "index": "#primary",
            "index_projection": {
                "primary_key": true
            },
            "keyspace": "OASBucket",
            "namespace": "default",
            "using": "gsi"
        },
        {
            "#operator": "Fetch",
            "as": "m",
            "keyspace": "OASBucket",
            "namespace": "default"
        },
        {
            "#operator": "Parallel",
            "~child": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "Unnest",
                        "as": "sec_names",
                        "expr": "object_names(((`m`.`components`).`securitySchemes`))",
                        "outer": true
                    }
                ]
            }
        },
        {
            "#operator": "Parallel",
            "~child": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "Filter",
                        "condition": "any `v` within array_star(object_values((`m`.`paths`))) satisfies (((array_star(object_values((`m`.`paths`))) is not missing) and any `p` in (`v`.`param`) satisfies (((`p`.`in`) = \"query\") and ((`p`.`name`) = \"limit\")) end) and any `x` in (`v`.`sec`) satisfies (object_names(`x`) and any `y` in object_names(`x`) satisfies ((`y` = `sec_names`) and any `c` within (`m`.`comp`) satisfies ((((`c`.[`y`]).`type`) = \"http\") and (((`c`.[`y`]).`scheme`) = \"basic\")) end) end) end) end"
                    },
                    {
                        "#operator": "InitialProject",
                        "result_terms": [
                            {
                                "as": "service_id",
                                "expr": "(meta(`m`).`id`)"
                            }
                        ]
                    },
                    {
                        "#operator": "FinalProject"
                    }
                ]
            }
        }
    ]
}
6

@iwanna_ster ,

The looks straight forward. Will you able to provide SQL statement instead of me construct from the plan. Filters might be doing too much work. Using Many ANY, WITHIN, UNNEST. If possible explain what you are looking will provide WHERE clause.

Is sec_names is part of main document? Why do u do UNNEST security_names never used?

Are you really need WITHIN it is recursive? based on explain try following query and see. It is too complex and they have many loops. It will take time iterate all those.

SELECT RAW META(o).id
FROM OASBucket AS o
LET sec_names = OBJECT_NAMES(m.components.securitySchemes)
WHERE (ANY n:v WITHIN m.paths
      SATISFIES (ANY p IN v.param SATISFIES p.`in` = "query" AND p.name = "limit" END)
                 AND ( ANY x IN v.sec
                       SATISFIES (ANY n1:v1 IN x
                                  SATISFIES n1 IN sec_names
                                           AND (ANY c WITHIN m.comp
                                                SATISFIES c.[n1].scheme = "basic" AND c.[n1].type = "http"
                                                END)
                                  END)
                        END)
      END);
7

This was my mistake. I edited the previous post. I only use sec names. Yes sec_nanes are part of my documents and i really need to do all these iterations because my documents are semi-structured JSON data.
I just want to see the exact time of each condition in order to compare them between queries.

8

Granularity is at operator level. In this case you interested in Filter.

1 Like
9

The field “#phaseSwitches”, what kind of information is giving us?

10 
`#phaseSwitches`

Number of switches between executing, waiting for services, or waiting for the `goroutine` scheduler.

https://docs.couchbase.com/server/current/manage/monitor/monitoring-n1ql-query.html

1 Like