ClickHouse / ClickHouse/ClickHouse
Unexpected projection using
- 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
Assessment
This issue has not been assessed yet.