Low performance when using 'OR' in WHERE clause

SQL++
,
1

Querying over two indexed properties is painfully slow when using ‘OR’. This query takes about 40 seconds to execute with 500 000 records in the bucket:

SELECT device FROM default 
WHERE device.cashDrawerOpenCount > 1000000 OR
device.tempMaxBoard > 10000000

Execution time: 40s

Runnning separately is fine:

SELECT device FROM default 
WHERE device.cashDrawerOpenCount > 1000000

Execution time: 4ms

SELECT device from default 
WHERE device.tempMaxBoard < -100000

Execution time: 480ms

Both device.cashDrawerOpenCount and device.tempMaxBoard have global secondary indexes created.

Explain on the first query:

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "PrimaryScan",
          "index": "#primary",
          "keyspace": "default",
          "namespace": "default",
          "using": "gsi"
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Fetch",
                "keyspace": "default",
                "namespace": "default"
              },
              {
                "#operator": "Filter",
                "condition": "((1000000 < ((`default`.`device`).`cashDrawerOpenCount`)) or (10000000 < ((`default`.`device`).`tempMaxBoard`)))"
              },
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "expr": "(`default`.`device`)"
                  }
                ]
              },
              {
                "#operator": "FinalProject"
              }
            ]
          }
        }
      ]
    },
    "text": "select device from default \nwhere device.cashDrawer1OpenCount > 1000000 OR\ndevice.tempMaxBoard > 10000000"
  }
]

Is there any way how to force the query to use indexScan instead of primaryScan? Do I need to create an additional index?

2

Try new create global secondary index

İndex
CREATE INDEX Devices ON default(device.tempMaxBoard,device.cashDrawerOpenCount) USING GSI;

Try it please.

3

Creating the composite index did not help. After successful creation the execution speed and EXPLAIN statement results are still the same.

FYI: I am using version 4.5 Beta.

4

Try it please:

SELECT device FROM default UNNEST device d WHERE d.cashDrawerOpenCount > 1000000 OR d.tempMaxBoard > 10000000

5

Running

SELECT d FROM default UNNEST device d WHERE d.cashDrawerOpenCount > 1000000 OR d.tempMaxBoard > 10000000

Was a bit faster, still aroung 30 seconds though. EXPLAIN showing PrimaryScan.

6

İndexes write please

7

What do you mean by that?

8

You create indexes you write

9

Create İndex Nested Objects

I want to you examine forum topic from hyperlink.

10

This seems to be only relevant for Array type properties, which is not the case for either of the properties I am accessing. Is it relevant for this use case?

11

Hello @jakubjenis,

Yes, we are aware of this issue. You can do the following:

SELECT device FROM default 
WHERE device.cashDrawerOpenCount > 1000000
UNION
SELECT device FROM default 
WHERE device.tempMaxBoard > 10000000;
12

Thanks, that fixes the problem, although it will be kind of hard to translate from more complex conditions (multiple 'AND’s and 'OR’s). Does this happen specifically with ‘OR’ statements? Can we expect it to be fixed in some future release?

13

Hi @jakubjenis,

This issue only occurs with OR predicates involving unrelated predicates. So if you have a < 10 OR a > 20, N1QL will handle it well. If you have a < 10 OR b < 10, we currently go to a full primary scan, and you are better off using UNION. Yes, we will address this in the future. Currently, we tend to favor a single index.

14

@geraldss Is the unrelated predicates with OR is still an issue in 6.0 enterprise version
We are using
((a = 10) OR (a = 20 AND b in )
Currently we are overcoming it by UNION but
we need to order by and limit on large data on the UNION
SELECT a, d
FROM c
WHERE a = 10
UNION
SELECT a, d
FROM c
WHERE a = 20 AND b in
ORDER BY d
LIMIT 10 OFFSET 20000

does not push the limit and offset to the index, any suggestions? Thanks

15

LIMIT AND OFFSET can pushed to index only when all predicates are pushed to indexer and no false positives are possible and query order follows index order.

CREATE INDEX ix1 ON default(a,b);
SELECT a,d FROM  default WHERE (a = 10) OR (a = 20 AND b IN [....])  ORDER BY d  OFFSET 20000 LIMIt 10;

Also you can try this,
CREATE INDEX ix1 ON default(d,a,b);
SELECT a,d FROM default WHERE d IS NOT MISSING AND ((a = 10) OR (a = 20 AND b IN […])) ORDER BY d OFFSET 20000 LIMIt 10;

https://blog.couchbase.com/create-right-index-get-right-performance/
https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/

16

Hi,
Without UNION limit push down happens but the question is with UNION the limit on the result of the UNION the limit push down does not happen.
Thanks
Jothi

17

UNION requires eliminate duplicates and sometimes it is not possible to push limit/offset which can result in less rows (which contribute to wrong results). N1QL will disable optimizations in that situations.