obj_array[1]['x'] IN ('...') is slow (generic function query)
- Dominant language
- Java
- Stars
- 4.4k
- Forks
- 616
- Avg merge
- 14h 49m
- Merged PRs (30d)
- 206
Description
**CrateDB version**:
- 4.2.6 (via docker)
- 4.3.1 (via cr8)
**Problem description**:
Consider the following simple table
```sql
CREATE TABLE a(a ARRAY(OBJECT(DYNAMIC) AS (a TEXT)));
```
and a small dataset of 100000 entries.
The following query is fast (0.019 sec)
```sql
SELECT * FROM a WHERE a.a[1]['a'] = 'abcd';
```
.. while the following is slow when compared via `IN` array ~(1.0- 3.633 sec)
```sql
SELECT * FROM a WHERE a.a[1]['a'] IN ('abcd');
```
Here's the explain queries:
```sql
EXPLAIN SELECT * FROM a WHERE a.a[1]['a'] IN ('abcd');
```
```
+----------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------+
| Collect[doc.a | [a] | (a[1]['a'] = ANY(['abcd']))] |
+----------------------------------------------------+
```
```sql
EXPLAIN SELECT * FROM a WHERE a.a[1]['a'] = 'abcd';
```
```
+---------------------------------------------+
| EXPLAIN |
+---------------------------------------------+
| Collect[doc.a | [a] | (a[1]['a'] = 'abcd')] |
+---------------------------------------------+
```
and here's the output of explain analyze
```sql
EXPLAIN SELECT * FROM a WHERE a.a[1]['a'] IN ('abcd');
```
```json
{
"Analyze": 1.3915,
"Execute": {
"Phases": {
"0-collect": {
"nodes": {
"vx37mCzEQ5O0Xp_Zf1-8rA": 1659.3984
}
}
},
"Total": 1664.2538,
"vx37mCzEQ5O0Xp_Zf1-8rA": {
"QueryBreakdown": [
{
"BreakDown": {
"advance": 0,
"advance_count": 0,
"build_scorer": 1.4562,
"build_scorer_count": 20,
"compute_max_score": 0,
"compute_max_score_count": 0,
"create_weight": 0.0201,
"create_weight_count": 1,
"match": 0,
"match_count": 0,
"next_doc": 478.9814,
"next_doc_count": 11,
"score": 0,
"score_count": 0,
"shallow_advance": 0,
"shallow_advance_count": 0
},
"QueryDescription": "(a[1]['a'] = ANY(['abcd']))",
"QueryName": "GenericFunctionQuery",
"Time": 480.457732
},
{
"BreakDown": {
"advance": 0,
"advance_count": 0,
"build_scorer": 297.4574,
"build_scorer_count": 4,
"compute_max_score": 0,
"compute_max_score_count": 0,
"create_weight": 0.0197,
"create_weight_count": 1,
"match": 0,
"match_count": 0,
"next_doc": 135.2281,
"next_doc_count": 2,
"score": 0,
"score_count": 0,
"shallow_advance": 0,
"shallow_advance_count": 0
},
"QueryDescription": "(a[1]['a'] = ANY(['abcd']))",
"QueryName": "GenericFunctionQuery",
"Time": 432.705207
},
{
"BreakDown": {
"advance": 0,
"advance_count": 0,
"build_scorer": 365.2579,
"build_scorer_count": 8,
"compute_max_score": 0,
"compute_max_score_count": 0,
"create_weight": 0.0638,
"create_weight_count": 1,
"match": 0,
"match_count": 0,
"next_doc": 169.4074,
"next_doc_count": 4,
"score": 0,
"score_count": 0,
"shallow_advance": 0,
"shallow_advance_count": 0
},
"QueryDescription": "(a[1]['a'] = ANY(['abcd']))",
"QueryName": "GenericFunctionQuery",
"Time": 534.729113
},
{
"BreakDown": {
"advance": 0,
"advance_count": 0,
"build_scorer": 1.1277,
"build_scorer_count": 10,
"compute_max_score": 0,
"compute_max_score_count": 0,
"create_weight": 0.0201,
"create_weight_count": 1,
"match": 0,
"match_count": 0,
"next_doc": 206.5428,
"next_doc_count": 5,
"score": 0,
"score_count": 0,
"shallow_advance": 0,
"shallow_advance_count": 0
},
"QueryDescription": "(a[1]['a'] = ANY(['abcd']))",
"QueryName": "GenericFunctionQuery",
"Time": 207.690616
}
]
}
},
"Plan": 0.9449
}
```
**Extra Note**:
There is no performance impact when there is no array schema involved:
e.g. with
```
cr> create table b (b text);
cr> create table c(c object(dynamic) as (c text));
```
```sql
`SELECT * FROM c WHERE c['c'] IN ('abcd')`
```
`IN` is still fast
**Steps to reproduce**:
1.
```sql
CREATE TABLE a(a ARRAY(OBJECT(DYNAMIC) AS (a TEXT)));
```
2.
```sql
INSERT INTO a(a) VALUES (['{"a":"abcd"}']);
```
3.
```bash
cr8 insert-fake-data --table a
```
4.
```sql
SELECT * FROM a WHERE a.a[1]['a'] IN ('abcd');
```
Contributor guide
Assessment
This issue has not been assessed yet.