composite index OR filter not included in columnHints

[wey-gu]

Introduction

If we do OR conditions for single column indexscan, the filter could be pushdown as optimal.

profile match(v:player) where ((v.player.name == "Tim Duncan") OR (v.player.name == "Yao Ming") ) return v

...
-----+----------------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------
|  7 | IndexScan      | 2            | {                                                                                                                                                                                                                                                                                              | outputVar: {                                                              |
|    |                |              | ver: 0, rows: 3, execTime: 0us, totalTime: 4472us                                                                                                                                                                                                                                              |   "colNames": [                                                           |
|    |                |              | storage_detail: {IndexDedupNode(dedup=[_vid]):370(us),IndexLimitNode(limit=9223372036854775807):370(us),IndexProjectionNode(projectColumn=[_vid]):590(us),IndexVertexScanNode(IndexID=7, Path=(name="Tim Duncan", )):295(us),IndexVertexScanNode(IndexID=7, Path=(name="Yao Ming", )):302(us)} |     "_vid"                                                                |
|    |                |              | "storaged2":9779 exec/total: 633(us)/1906(us)                                                                                                                                                                                                                                                  |   ],                                                                      |
|    |                |              | "storaged1":9779 exec/total: 581(us)/3020(us)                                                                                                                                                                                                                                                  |   "type": "DATASET",                                                      |
|    |                |              | "storaged0":9779 exec/total: 529(us)/4266(us)                                                                                                                                                                                                                                                  |   "name": "__IndexScan_1"                                                 |
|    |                |              | }                                                                                                                                                                                                                                                                                              | }                                                                         |
|    |                |              |                                                                                                                                                                                                                                                                                                | inputVar:                                                                 |
|    |                |              |                                                                                                                                                                                                                                                                                                | space: 1                                                                  |
|    |                |              |                                                                                                                                                                                                                                                                                                | dedup: false                                                              |
|    |                |              |                                                                                                                                                                                                                                                                                                | limit: 9223372036854775807                                                |
|    |                |              |                                                                                                                                                                                                                                                                                                | filter:                                                                   |
|    |                |              |                                                                                                                                                                                                                                                                                                | orderBy: []                                                               |
|    |                |              |                                                                                                                                                                                                                                                                                                | schemaId: 2                                                               |
|    |                |              |                                                                                                                                                                                                                                                                                                | isEdge: false                                                             |
|    |                |              |                                                                                                                                                                                                                                                                                                | returnCols: [                                                             |
|    |                |              |                                                                                                                                                                                                                                                                                                |   "_vid"                                                                  |
|    |                |              |                                                                                                                                                                                                                                                                                                | ]                                                                         |
|    |                |              |                                                                                                                                                                                                                                                                                                | indexCtx: [                                                               |
|    |                |              |                                                                                                                                                                                                                                                                                                |   {                                                                       |
|    |                |              |                                                                                                                                                                                                                                                                                                |     "columnHints": [                                                      |
|    |                |              |                                                                                                                                                                                                                                                                                                |       {                                                                   |
|    |                |              |                                                                                                                                                                                                                                                                                                |         "includeEnd": false,                                              |
|    |                |              |                                                                                                                                                                                                                                                                                                |         "includeBegin": true,                                             |
|    |                |              |                                                                                                                                                                                                                                                                                                |         "endValue": "__EMPTY__",                                          |
|    |                |              |                                                                                                                                                                                                                                                                                                |         "beginValue": "Tim Duncan",                                       |
|    |                |              |                                                                                                                                                                                                                                                                                                |         "scanType": "PREFIX",                                             |
|    |                |              |                                                                                                                                                                                                                                                                                                |         "column": "name"                                                  |
|    |                |              |                                                                                                                                                                                                                                                                                                |       }                                                                   |
|    |                |              |                                                                                                                                                                                                                                                                                                |     ],                                                                    |
|    |                |              |                                                                                                                                                                                                                                                                                                |     "filter": "",                                                         |
|    |                |              |                                                                                                                                                                                                                                                                                                |     "index_id": 7                                                         |
|    |                |              |                                                                                                                                                                                                                                                                                                |   },                                                                      |
|    |                |              |                                                                                                                                                                                                                                                                                                |   {                                                                       |
|    |                |              |                                                                                                                                                                                                                                                                                                |     "columnHints": [                                                      |
|    |                |              |                                                                                                                                                                                                                                                                                                |       {                                                                   |
|    |                |              |                                                                                                                                                                                                                                                                                                |         "includeEnd": false,                                              |
|    |                |              |                                                                                                                                                                                                                                                                                                |         "includeBegin": true,                                             |
|    |                |              |                                                                                                                                                                                                                                                                                                |         "endValue": "__EMPTY__",                                          |
|    |                |              |                                                                                                                                                                                                                                                                                                |         "beginValue": "Yao Ming",                                         |
|    |                |              |                                                                                                                                                                                                                                                                                                |         "scanType": "PREFIX",                                             |
|    |                |              |                                                                                                                                                                                                                                                                                                |         "column": "name"                                                  |
|    |                |              |                                                                                                                                                                                                                                                                                                |       }                                                                   |
|    |                |              |                                                                                                                                                                                                                                                                                                |     ],                                                                    |
|    |                |              |                                                                                                                                                                                                                                                                                                |     "filter": "",                                                         |
|    |                |              |                                                                                                                                                                                                                                                                                                |     "index_id": 7                                                         |
|    |                |              |                                                                                                                                                                                                                                                                                                |   }                                                                       |
|    |                |              |                                                                                                                                                                                                                                                                                                | ]
...

While it seems in the composite index case, it's full scan.

profile match(v:player) where ((v.player.name == "Tim Duncan" AND v.player.age > 32) OR (v.player.name == "Yao Ming" AND v.player.age > 32) ) return v

-----+----------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------
|  1 | IndexScan      | 2            | {                                                                                                                                                                     | outputVar: {                                                                                                              |
|    |                |              | ver: 0, rows: 52, execTime: 0us, totalTime: 4698us                                                                                                                    |   "colNames": [                                                                                                           |
|    |                |              | "storaged1":9779 exec/total: 556(us)/2951(us)                                                                                                                         |     "_vid"                                                                                                                |
|    |                |              | "storaged0":9779 exec/total: 472(us)/4376(us)                                                                                                                         |   ],                                                                                                                      |
|    |                |              | storage_detail: {IndexLimitNode(limit=9223372036854775807):307(us),IndexProjectionNode(projectColumn=[_vid]):306(us),IndexVertexScanNode(IndexID=6, Path=()):298(us)} |   "type": "DATASET",                                                                                                      |
|    |                |              | "storaged2":9779 exec/total: 580(us)/3758(us)                                                                                                                         |   "name": "__IndexScan_1"                                                                                                 |
|    |                |              | }                                                                                                                                                                     | }                                                                                                                         |
|    |                |              |                                                                                                                                                                       | inputVar:                                                                                                                 |
|    |                |              |                                                                                                                                                                       | space: 1                                                                                                                  |
|    |                |              |                                                                                                                                                                       | dedup: false                                                                                                              |
|    |                |              |                                                                                                                                                                       | limit: 9223372036854775807                                                                                                |
|    |                |              |                                                                                                                                                                       | filter:                                                                                                                   |
|    |                |              |                                                                                                                                                                       | orderBy: []                                                                                                               |
|    |                |              |                                                                                                                                                                       | schemaId: 2                                                                                                               |
|    |                |              |                                                                                                                                                                       | isEdge: false                                                                                                             |
|    |                |              |                                                                                                                                                                       | returnCols: [                                                                                                             |
|    |                |              |                                                                                                                                                                       |   "_vid"                                                                                                                  |
|    |                |              |                                                                                                                                                                       | ]                                                                                                                         |
|    |                |              |                                                                                                                                                                       | indexCtx: [                                                                                                               |
|    |                |              |                                                                                                                                                                       |   {                                                                                                                       |
|    |                |              |                                                                                                                                                                       |     "columnHints": [],                                                                                                    |
|    |                |              |                                                                                                                                                                       |     "filter": "",                                                                                                         |
|    |                |              |                                                                                                                                                                       |     "index_id": 6                                                                                                         |
|    |                |              |                                                                                                                                                                       |   }                                                                                                                       |
|    |                |              |                                                                                                                                                                       | ]                                                                                                                         |
-----+----------------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------


# we could see index itself is ready

profile match(v:player) where (v.player.name == "Tim Duncan" AND v.player.age > 32) return v
...
------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------
|  7 | IndexScan      | 2            | {                                                                                                                                                                                                                                                                             | outputVar: {                                                         |
|    |                |              | ver: 0, rows: 2, execTime: 0us, totalTime: 5423us                                                                                                                                                                                                                             |   "colNames": [                                                      |
|    |                |              | "storaged0":9779 exec/total: 496(us)/5160(us)                                                                                                                                                                                                                                 |     "_vid"                                                           |
|    |                |              | storage_detail: {IndexLimitNode(limit=9223372036854775807):276(us),IndexProjectionNode(projectColumn=[_vid]):276(us),IndexSelectionNode(expr=[((player.name=="Tim Duncan") AND (player.age>32))]):281(us),IndexVertexScanNode(IndexID=7, Path=(name="Tim Duncan", )):326(us)} |   ],                                                                 |
|    |                |              | "storaged2":9779 exec/total: 738(us)/4733(us)                                                                                                                                                                                                                                 |   "type": "DATASET",                                                 |
|    |                |              | "storaged1":9779 exec/total: 1505(us)/3986(us)                                                                                                                                                                                                                                |   "name": "__IndexScan_1"                                            |
|    |                |              | }                                                                                                                                                                                                                                                                             | }                                                                    |
|    |                |              |                                                                                                                                                                                                                                                                               | inputVar:                                                            |
|    |                |              |                                                                                                                                                                                                                                                                               | space: 1                                                             |
|    |                |              |                                                                                                                                                                                                                                                                               | dedup: false                                                         |
|    |                |              |                                                                                                                                                                                                                                                                               | limit: 9223372036854775807                                           |
|    |                |              |                                                                                                                                                                                                                                                                               | filter:                                                              |
|    |                |              |                                                                                                                                                                                                                                                                               | orderBy: []                                                          |
|    |                |              |                                                                                                                                                                                                                                                                               | schemaId: 2                                                          |
|    |                |              |                                                                                                                                                                                                                                                                               | isEdge: false                                                        |
|    |                |              |                                                                                                                                                                                                                                                                               | returnCols: [                                                        |
|    |                |              |                                                                                                                                                                                                                                                                               |   "_vid"                                                             |
|    |                |              |                                                                                                                                                                                                                                                                               | ]                                                                    |
|    |                |              |                                                                                                                                                                                                                                                                               | indexCtx: [                                                          |
|    |                |              |                                                                                                                                                                                                                                                                               |   {                                                                  |
|    |                |              |                                                                                                                                                                                                                                                                               |     "columnHints": [                                                 |
|    |                |              |                                                                                                                                                                                                                                                                               |       {                                                              |
|    |                |              |                                                                                                                                                                                                                                                                               |         "includeEnd": false,                                         |
|    |                |              |                                                                                                                                                                                                                                                                               |         "includeBegin": true,                                        |
|    |                |              |                                                                                                                                                                                                                                                                               |         "endValue": "__EMPTY__",                                     |
|    |                |              |                                                                                                                                                                                                                                                                               |         "beginValue": "Tim Duncan",                                  |
|    |                |              |                                                                                                                                                                                                                                                                               |         "scanType": "PREFIX",                                        |
|    |                |              |                                                                                                                                                                                                                                                                               |         "column": "name"                                             |
|    |                |              |                                                                                                                                                                                                                                                                               |       }                                                              |
|    |                |              |                                                                                                                                                                                                                                                                               |     ],                                                               |
|    |                |              |                                                                                                                                                                                                                                                                               |     "filter": "((player.name==\"Tim Duncan\") AND (player.age>32))", |
|    |                |              |                                                                                                                                                                                                                                                                               |     "index_id": 7                                                    |
|    |                |              |                                                                                                                                                                                                                                                                               |   }                                                                  |
|    |                |              |                                                                                                                                                                                                                                                                               | ]

Contents

Related work

In Lookup, it's working as expected:

profile LOOKUP ON player WHERE ((player.name == "Tim Duncan" AND player.age > 32) OR (player.name == "Yao Ming" AND player.age > 32) ) YIELD properties(vertex)

...
-----+-----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------
|  4 | IndexScan | 0            | {                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | outputVar: {                                                         |
|    |           |              | ver: 0, rows: 3, execTime: 0us, totalTime: 4288us                                                                                                                                                                                                                                                                                                                                                                                                                                    |   "colNames": [                                                      |
|    |           |              | "storaged2":9779 exec/total: 999(us)/3944(us)                                                                                                                                                                                                                                                                                                                                                                                                                                        |     "_vid",                                                          |
|    |           |              | "storaged1":9779 exec/total: 409(us)/3404(us)                                                                                                                                                                                                                                                                                                                                                                                                                                        |     "player._tag",                                                   |
|    |           |              | "storaged0":9779 exec/total: 483(us)/3782(us)                                                                                                                                                                                                                                                                                                                                                                                                                                        |     "player.age",                                                    |
|    |           |              | storage_detail: {IndexDedupNode(dedup=[_vid]):288(us),IndexLimitNode(limit=9223372036854775807):288(us),IndexProjectionNode(projectColumn=[_vid,_tag,age,name]):446(us),IndexSelectionNode(expr=[((player.name=="Tim Duncan") AND (player.age>32))]):230(us),IndexSelectionNode(expr=[((player.name=="Yao Ming") AND (player.age>32))]):231(us),IndexVertexScanNode(IndexID=7, Path=(name="Tim Duncan", )):241(us),IndexVertexScanNode(IndexID=7, Path=(name="Yao Ming", )):236(us)} |     "player.name"                                                    |
|    |           |              | }                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |   ],                                                                 |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |   "type": "DATASET",                                                 |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |   "name": "__Filter_2"                                               |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | }                                                                    |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | inputVar:                                                            |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | space: 1                                                             |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | dedup: false                                                         |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | limit: 9223372036854775807                                           |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | filter:                                                              |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | orderBy: []                                                          |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | schemaId: 2                                                          |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | isEdge: false                                                        |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | returnCols: [                                                        |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |   "_vid",                                                            |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |   "_tag",                                                            |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |   "age",                                                             |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |   "name"                                                             |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | ]                                                                    |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | indexCtx: [                                                          |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |   {                                                                  |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |     "columnHints": [                                                 |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |       {                                                              |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |         "includeEnd": false,                                         |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |         "includeBegin": true,                                        |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |         "endValue": "__EMPTY__",                                     |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |         "beginValue": "Tim Duncan",                                  |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |         "scanType": "PREFIX",                                        |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |         "column": "name"                                             |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |       }                                                              |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |     ],                                                               |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |     "filter": "((player.name==\"Tim Duncan\") AND (player.age>32))", |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |     "index_id": 7                                                    |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |   },                                                                 |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |   {                                                                  |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |     "columnHints": [                                                 |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |       {                                                              |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |         "includeEnd": false,                                         |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |         "includeBegin": true,                                        |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |         "endValue": "__EMPTY__",                                     |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |         "beginValue": "Yao Ming",                                    |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |         "scanType": "PREFIX",                                        |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |         "column": "name"                                             |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |       }                                                              |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |     ],                                                               |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |     "filter": "((player.name==\"Yao Ming\") AND (player.age>32))",   |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |     "index_id": 7                                                    |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |   }                                                                  |
|    |           |              |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | ]                                                                    |

[czpmango]

That's indeed a problem, and fixing it may require refactoring some old code..

[jievince]

This is not a result error, just performance degradation. To fix it, we may need to do a major refactor to the index selection module, so could we move it to 3.5? @xtcyclist @yixinglu
Related issues:
#3095
#3181

[yixinglu]

Agree with you.

[xtcyclist]

I'm closing this issue and removing it from the 3.5 milestone due to resource constraints. We welcome anyone from the community to address this issue lateron.