Index not used with OR/AND condition combination

[creisle]

OrientDB Version: 2.2.37

Java Version:

openjdk version "1.8.0_151"
OpenJDK Runtime Environment (build 1.8.0_151-b12)
OpenJDK 64-Bit Server VM (build 25.151-b12, mixed mode)

OS: centos7

Expected behavior

Expected the index to be used

Actual behavior

The index was not used

Steps to reproduce

Create the test conditions with randomly named property prop1, prop2, prop3

create property v.prop1 string
create property v.prop2 string
create property v.prop3 string

Create full-text indexes on prop1 and prop2

create index prop1Index on v (prop1) fulltext
create index prop2Index on v (prop2) fulltext

Now attempt to query (explain)

select * from v where prop1 containstext 'sometext' or prop2 containstext 'sometext'

This works as expected and the index is used

{
    "result": [
        {
            "@type": "d",
            "@version": 0,
            "fullySortedByIndex": false,
            "compositeIndexUsed": 2,
            "involvedIndexes": [
                "prop2Index",
                "prop1Index"
            ],
            "limit": -1,
            "fetchingFromTargetElapsed": 0,
            "indexIsUsedInOrderBy": false,
            "elapsed": 0.445399,
            "resultType": "collection",
            "resultSize": 0,
            "@fieldTypes": "compositeIndexUsed=l,involvedIndexes=e,fetchingFromTargetElapsed=l,elapsed=f"
        }
    ],
    "notification": "Query executed in 0.01 sec. Returned 1 record(s)"
}

However if these conditions are then surrounded in parentheses and the conditions following it use an AND this will fail to use the index

select * from v where (prop1 containstext 'sometext' or prop2 containstext 'sometext') and prop3 = 'sometext'

see explain below

{
    "result": [
        {
            "@type": "d",
            "@version": 0,
            "fetchingFromTargetElapsed": 0,
            "elapsed": 0.202031,
            "resultType": "collection",
            "resultSize": 0,
            "@fieldTypes": "fetchingFromTargetElapsed=l,elapsed=f"
        }
    ],
    "notification": "Query executed in 0.015 sec. Returned 1 record(s)"
}

[creisle]

I tried this on v3.0.6 as well, but I am not 100% sure how to read the query plans? But if the index is supposed to be listed when used then I can't get it to use a fulltext index in v3 at all?

select * from v where (prop1 containstext 'sometext' or prop2 containstext 'sometext') and prop3 = 'containstext'

+ FETCH FROM CLASS v
  + FETCH FROM CLUSTER 9 ASC
  + FETCH NEW RECORDS FROM CURRENT TRANSACTION SCOPE (if any)
+ FILTER ITEMS WHERE 
  (prop1 CONTAINSTEXT 'sometext' OR prop2 CONTAINSTEXT 'sometext' ) AND prop3 = 'containstext'
+ CALCULATE PROJECTIONS
  *

[luigidellaquila]

Hi @creisle

V 2.2 query engine has significant limitations on query optimization, this is why we re-wrote the engine. I doubt we can fix it.

In v 3.0 it's supposed to work though, the fact that it doesn't is probably a bug (the CONTAINSTEXT is an old operator, most users tend to use LUCENE instead, so probably we just missed a little piece in the implementation). I'll check it ASAP and let you know

Thanks

Luigi

[creisle]

@luigidellaquila did you have any luck looking at this is v3?

[luigidellaquila]

Hi @creisle

Sorry, not yet, I'll try to check it today

Thanks

Luigi

[creisle]

Sorry to bug you again, any luck here?

[creisle]

@luigidellaquila any luck here? this is blocking me upgrading to v3. I looked into swapping to lucene indices but they are overkill for my use-case and hang so I cannot delete that database with them (related to 8471) so this seems like the only option atm

[luigidellaquila]

Hi @creisle

Sorry, not yet :(

I hope I'll find some time to fix it this week

Thanks

Luigi

[luigidellaquila]

Hi @creisle

I have good news for you, I just pushed a fix for this problem. The fix will be released with v 3.0.19, the snapshot will be available in a few minutes

Anyway, in general I strongly suggest you to use Lucene indexes instead of the legacy FULLTEXT indexes, that are much more stable

Thanks

Luigi

[creisle]

@luigidellaquila thank you so much! I will likely revisit swapping to the lucene indexes later once #8471 is resolved

[creisle]

@luigidellaquila I just tried this in v3.0.20 and it still does not appear to use the index

select * from vocabulary where name containstext 'expr' 

+ FETCH FROM CLASS vocabulary
  + FETCH FROM CLUSTER 77 ASC
  + FETCH FROM CLUSTER 78 ASC
  + FETCH NEW RECORDS FROM CURRENT TRANSACTION SCOPE (if any)
+ FILTER ITEMS WHERE 
  name CONTAINSTEXT 'expr'
+ CALCULATE PROJECTIONS
  *

[luigidellaquila]

Hi @creisle

I just tested it as follows:

create class vocabulary;
create property vocabulary.name STRING;
create index vocabulary.name on vocabulary (name) FULLTEXT;

insert into vocabulary set name ='foo'
insert into vocabulary set name ='expr'

explain select * from vocabulary where name containstext 'expr' 

and the result:

+ FETCH FROM INDEX vocabulary.name
  name CONTAINSTEXT 'expr'
+ EXTRACT VALUE FROM INDEX ENTRY
  filtering clusters [54,50,51,52,53,46,47,48,49,45,55,56]
+ DISTINCT
+ FILTER ITEMS BY CLASS 
  vocabulary
+ CALCULATE PROJECTIONS
  *

Am I missing something?

Thanks

Luigi

[creisle]

@luigidellaquila I was using the fulltext hash index previously

create index vocabulary.name on vocabulary (name) FULLTEXT_HASH_INDEX 

when you replace creating the index with the above instead, the index is not used in the query plan

+ FETCH FROM CLASS vocabulary
  + FETCH FROM CLUSTER 15 ASC
  + FETCH FROM CLUSTER 16 ASC
  + FETCH NEW RECORDS FROM CURRENT TRANSACTION SCOPE (if any)
+ FILTER ITEMS WHERE 
  name CONTAINSTEXT 'expr'
+ CALCULATE PROJECTIONS
  *

[creisle]

Is FULLTEXT_HASH_INDEX no longer supported in v3? I could use the SBTREE option instead? should it throw an error?

[luigidellaquila]

Hi @creisle

OK, it makes sense, I think it just needs a little fix. FULLTEXT_HASH_INDEX is still there, even though it is not recommended (same as other legacy FULLTEXT implementations).

Checking it now, I'll keep you updated

Thanks

Luigi

[luigidellaquila]

Hi @creisle

I pushed a new fix, now also fulltext hash indexes work fine

Thanks

Luigi