ClickHouse / ClickHouse/ClickHouse

Constraint optimization requires to have WHERE clause in query.

Open
#33,544 3 comments 1 reaction 0 assignees View on GitHub
comp-query-optimizer external unfinished code
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

**How to reproduce**
ClickHouse 21.13.1.852

```
CREATE TABLE default.test_constr
(
`key` UInt32,
`map` Map(String, String),
`key_a` String DEFAULT map['key_a'],
CONSTRAINT key_a_constr ASSUME key_a = (map['key_a']),
CONSTRAINT key_a_constr ASSUME (map['key_a']) = key_a
)
ENGINE = MergeTree
ORDER BY key
SETTINGS index_granularity = 8192;

set optimize_using_constraints =1, optimize_substitute_columns = 1, convert_query_to_cnf =1, optimize_append_index=1;

INSERT INTO test_constr(key, map) SELECT number, map('key_a','lala', 'key_b','lalalalalalalalalalalalala','key_c', 'lalalalalalalalalala') FROM numbers(1000000);

SELECT count(map['key_a'])
FROM test_constr

┌─count(arrayElement(map, 'key_a'))─┐
│ 1000000 │
└───────────────────────────────────┘

1 rows in set. Elapsed: 0.064 sec. Processed 1.00 million rows, 127.00 MB (15.55 million rows/s., 1.97 GB/s.)

SELECT count(key_a)
FROM test_constr

┌─count(key_a)─┐
│ 1000000 │
└──────────────┘

1 rows in set. Elapsed: 0.008 sec. Processed 1.00 million rows, 13.00 MB (120.52 million rows/s., 1.57 GB/s.)

SELECT count()
FROM test_constr
WHERE NOT ignore(map['key_a'])

┌─count()─┐
│ 1000000 │
└─────────┘

1 rows in set. Elapsed: 0.009 sec. Processed 1.00 million rows, 13.00 MB (110.58 million rows/s., 1.44 GB/s.)

SELECT count()
FROM test_constr
WHERE NOT ignore(key_a)

┌─count()─┐
│ 1000000 │
└─────────┘

1 rows in set. Elapsed: 0.015 sec. Processed 1.00 million rows, 13.00 MB (67.03 million rows/s., 871.37 MB/s.)

SELECT count(x)
FROM test_constr
WHERE NOT ignore(map['key_a'] AS x)

┌─count(arrayElement(map, 'key_a'))─┐
│ 1000000 │
└───────────────────────────────────┘

1 rows in set. Elapsed: 0.010 sec. Processed 1.00 million rows, 13.00 MB (96.81 million rows/s., 1.26 GB/s.)

EXPLAIN SYNTAX
SELECT count(x)
FROM test_constr
WHERE NOT ignore(map['key_a'] AS x)

┌─explain─────────────────────────────────────────────────────────┐
│ SELECT count(key_a AS x) AS `count(arrayElement(map, 'key_a'))` │
│ FROM test_constr │
│ WHERE NOT ignore(key_a) │
└─────────────────────────────────────────────────────────────────┘
```

**Expected behavior**
All queries will be optimized by CONSTRAINT optimization

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.