ClickHouse / ClickHouse/ClickHouse

Unexpected projection using

Open
#63,444 1 comment 0 reactions 0 assignees View on GitHub
comp-projections external unexpected behaviour
Dominant language
C++
Stars
49.9k
Forks
9k
Avg merge
21h 32m
Merged PRs (30d)
515

Description

Here is my table schema:

```sql
CREATE TABLE default.t_open_tracing_data
(
`its` UInt32,
`traceId` String,
`spanId` String,
`serviceName` LowCardinality(String),
`operationName` LowCardinality(String),
`startTime` UInt64,
`endTime` UInt64,
`duration` Int64,
`logs` String,
`process` String,
`tags` String,
`error` UInt8,
`references` String,
`dye` LowCardinality(String),
`tagAppid` LowCardinality(String),
`tagTafServant` LowCardinality(String),
`tagIpv4` LowCardinality(String),
`tagPort` LowCardinality(String),
`tagSpanKind` LowCardinality(String),
`tagRetcode` LowCardinality(String),
`tagBizRetcode` LowCardinality(String),
`tagPeerIpv4` LowCardinality(String),
`tagPeerPort` LowCardinality(String),
`tagPeerService` LowCardinality(String),
`tagPeerTafServant` LowCardinality(String),
`tagPeerRetcode` LowCardinality(String),
`tagPeerBizRetcode` LowCardinality(String),
`tagUid` String,
`day` Date DEFAULT toDate(its),
`_cnt` UInt32 DEFAULT 1,
`tagDeviceId` String,
`tagDcacheModule` String,
`extra` String,
`servant` String,
`userLevel` Int8,
INDEX idx_trace_id traceId TYPE bloom_filter(0.001) GRANULARITY 1,
INDEX idx_service_name serviceName TYPE bloom_filter(0.025) GRANULARITY 8192,
INDEX idx_operation_name operationName TYPE bloom_filter(0.025) GRANULARITY 8192,
INDEX idx_tag_ipv4 tagIpv4 TYPE minmax GRANULARITY 8192,
INDEX idx_tag_port tagPort TYPE minmax GRANULARITY 8192,
INDEX idx_tag_uid tagUid TYPE minmax GRANULARITY 8192,
PROJECTION proj_u_t
(
SELECT
tagUid,
startTime,
traceId,
serviceName,
operationName,
tagPeerService,
tagIpv4,
tagPeerIpv4,
userLevel,
error,
duration
ORDER BY
tagUid,
startTime
)
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(day)
ORDER BY (its, serviceName, operationName, error)
TTL day + toIntervalDay(5)
SETTINGS index_granularity = 16384, parts_to_throw_insert = 900;
```

Try to explain the following query and output:
```sql
explain indexes=1
select distinct traceId
from t_open_tracing_data
where 1 = 1
and startTime >= toUnixTimestamp(toDateTime('2024-05-07 06:00:00')) * 1000000
and startTime <= toUnixTimestamp(toDateTime('2024-05-07 07:00:00')) * 1000000
and tagUid = '0'
order by startTime desc -- both startTime/traceId/tagUid are in the projection proj_u_t
limit 500
SETTINGS optimize_read_in_order=0, use_skip_indexes=0;

┌─explain───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Expression (Project names) │
│ Limit │
│ Distinct (DISTINCT) │
│ Sorting (Sorting for ORDER BY) │
│ Expression (Before ORDER BY) │
│ Distinct (Preliminary DISTINCT) │
│ Union │
│ Expression (Projection) │
│ Filter │
│ ReadFromMergeTree (default.t_open_tracing_data) │
│ Indexes: │
│ MinMax │
│ Condition: true │
│ Parts: 104/104 │
│ Granules: 542734/542734 │
│ Partition │
│ Condition: true │
│ Parts: 104/104 │
│ Granules: 542734/542734 │
│ PrimaryKey │
│ Condition: true │
│ Parts: 104/104 │
│ Granules: 542734/542734 │
│ Filter │
│ ReadFromMergeTree (proj_u_t) │
│ Indexes: │
│ MinMax │
│ Condition: true │
│ Parts: 62/62 │
│ Granules: 117270/117270 │
│ Partition │
│ Condition: true │
│ Parts: 62/62 │
│ Granules: 117270/117270 │
│ PrimaryKey │
│ Keys: │
│ tagUid │
│ startTime │
│ Condition: and(and((startTime in (-Inf, 1715044200000000]), (startTime in [1715043600000000, +Inf))), (tagUid in ['0', '0'])) │
│ Parts: 55/62 │
│ Granules: 56/117270 │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```

What I expected to happen: only filter by projection `proj_u_t`, but it also filter by the source table `t_open_tracing_data`. It cause the query process too much data. Why it filter by the source table? How to avoid it?

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.