ClickHouse / ClickHouse/ClickHouse
Could you provide a special skipping index?
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 20h 33m
- Merged PRs (30d)
- 501
Description
### Company or project name
_No response_
### Use case
We want to exclude whitelist domains(subdomains) from the query, But there is no suitable index to meet the performance requirements.
We want an index similar to a trie or postgresql ltree.
[postgresql ltree](https://www.postgresql.org/docs/18/ltree.html)
### Describe the solution you'd like
for example:
```sql
CREATE TABLE IF NOT EXISTS default.http_log
(
`timestamp` DateTime64(9,'UTC'),
`sip` IPv6,
`sport` UInt16,
`dip` IPv6,
`dport` UInt16,
`host` String
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (timestamp);
insert into http_log values
(now(),'1.1.1.1',40001,'2.2.2.2',8080,'api.google.com'),
(now(),'1.1.1.1',40001,'2.2.2.2',8080,'blog.google.com'),
(now(),'1.1.1.1',40001,'2.2.2.2',8080,'x.com'),
(now(),'1.1.1.1',40001,'2.2.2.2',8080,'myownck.com');
CREATE TABLE IF NOT EXISTS default.domain_whitelist
(
`domain` String
)
ENGINE = MergeTree
ORDER BY (domain);
INSERT into domain_whitelist values
('google.com'),
('x.com');
SELECT host from http_log a where not EXISTS (SELECT 1 from domain_whitelist where endsWith(a.host,domain));
```
Table domain_whitelist has 200k rows, unable to query when `http_log` data is large.
### Describe alternatives you've considered
_No response_
### Additional context
_No response_
Contributor guide
Assessment
This issue has not been assessed yet.