Using Indexes with 'OR' filters

[h-oll]

When executing the following query :

WITH ["123"] AS _uuids
MATCH (en:Entity)
WHERE 
  en._uuid IN _uuids OR _uuids = [] 
RETURN COUNT(en)

where _uuid is indexed on Entity nodes, the planner does not use the index and hinting at it with USING does not work.

Expected behaviour would be using the index (or at least being able to hint its usage).

[sherfert]

@h-oll Thanks for the report. Where is _uuids defined. That would help us reproduce.

[h-oll]

@sherfert : it's coming from an earlier part of the query: Either because it was user defined and passed as a parameter (I usually put all the parameters at the top of the query inside a WITH {_uuids} AS _uuids) or as a result of a previous step and obtained via a COLLECT.

[sherfert]

The problem the planner encounters is this part: OR _uuids = []. To verify that, you can remove that part from the query, and then it will be solved with a NodeIndexSeek instead of NodeByLabelScan.

The index can only solve the predicate en._uuid IN _uuids. In the case where _uuids is empty, the index cannot help in providing the results. This is why a NodeByLabelScan is needed.

[h-oll]

Well I thought I understood:

If I follow you correctly, when writing WHERE en._uuid IN _uuids the planner will correctly use the index even if it happens that _uuids = [].

So I imagined that adding to the WHERE clause a test on something not related to the matched nodes would not mess up the way the planner would behave on the first part of the clause. What's preventing the planner to work this way ?

[sherfert]

Setup:

CREATE INDEX ON :Entity(_uuid)
CREATE (:Entity {_uuid: 1}), (:Entity {_uuid: 2}), (:Entity {_uuid: 3}), (:Entity)

Now this query will return 2 of the 4 nodes:

WITH [1, 2] AS _uuids
MATCH (en:Entity)
WHERE 
  en._uuid IN _uuids OR _uuids = [] 
RETURN en

It will return the same removing OR _uuids = []
On the other hand, with this query, you will get all 4 nodes as the result, even the one that does not have the property:

WITH [] AS _uuids
MATCH (en:Entity)
WHERE 
  en._uuid IN _uuids OR _uuids = [] 
RETURN en

If you remove OR _uuids = [] here, you will instead get no results (and these 0 results will come from the index).

Since it is not possible to find the nodes not having the property from any index, the planner chooses a NodeByLabelScan. It cannot plan anything else, since it might not know at plan time if _uuids is empty or not (end that might even vary per row).

To work around that, you could write:

WITH [] AS _uuids
MATCH (en:Entity)
WHERE 
  exists(en._uuid) AND (en._uuid IN _uuids OR _uuids = [])
RETURN en

This will still not plan an NodeIndexSeek (because _uuids could still be empty), but it will plan a NodeIndexScan, which will be faster than the NodeByLabelScan.

Please note, however, that this query would return 3 of the 4 above nodes. (Not the one missing the property).
Does that help?

[sherfert]

In lack of response I am assuming that my last response cleared out the confusion. Feel free to reopen if that was a premature assumption.