ClickHouse / ClickHouse/ClickHouse

Could you provide a special skipping index?

Open
#90,022 0 comments 0 reactions 0 assignees View on GitHub
comp-skip-index external feature
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

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.