1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
|
ALTER TABLE roi_ods.pwa_webpush_log_local
ON CLUSTER oci_clickhouse_cluster
ADD INDEX idx_stats_minmax (`status`) TYPE minmax GRANULARITY 4;
CREATE TABLE roi_ods.pwa_webpush_log_local
(
`unique_id` String,
`uuid` String DEFAULT '',
`project_id` String DEFAULT '',
`task_id` UInt32 DEFAULT 0,
`tpl_id` UInt32 DEFAULT 0,
`ts` UInt32 DEFAULT 0,
`end_ts` UInt32 DEFAULT 0,
`status` Nullable(Int32),
`is_click` Nullable(Int32),
`is_install` Nullable(Int32),
`is_start` Nullable(Int32),
`process_start_ts` UInt32 DEFAULT 0,
`process_handler_ts` UInt32 DEFAULT 0,
`process_finish_ts` UInt32 DEFAULT 0,
`push_start_ts` UInt32 DEFAULT 0,
`push_handler_ts` UInt32 DEFAULT 0,
`push_finish_ts` UInt32 DEFAULT 0,
`result` String DEFAULT '',
`msg_event_time` DateTime64(3, 'UTC') DEFAULT now(),
INDEX idx_stats_minmax status TYPE minmax GRANULARITY 4,
INDEX idx_project_id_minmax project_id TYPE minmax GRANULARITY 64
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}', msg_event_time)
PARTITION BY toYYYYMMDD(msg_event_time)
ORDER BY unique_id
SETTINGS index_granularity = 8192 ;
EXPLAIN indexes = 1
SELECT ts
FROM roi_ods.pwa_webpush_log
WHERE (status IN (1, 2)) AND (project_id IN ('2370275851', '1525570300', '1466615877', '3388308004', '9315805214', '7627565981', '6248511643', '8500697977', '9270423031', '4455115017', '5585490324', '6950993136', '5094990157', '3759271512', '7282197246', '9384732058', '2295762890', '3764739170', '4750605621', '8002089310', '4621989591', '4664611466', '9063424305', '7249086740', '8920837722', '4651403212', '1248826202', '4138144208', '4699105058', '1058118665', '5171510317', '5448898742', '1024449145', '4966520217', '6717497544', '6080091125', '2137894782', '1719525694', '2246288786', '7659152860', '9059986931', '5662918328', '4908495065', '5169124273', '7880374970', '3228208071', '8119869884', '2501370695', '5792624362', '9798502776', '1711082853', '8185162124', '4145579670', '7552460793', '4380276538', '8589154676', '1010422230', '5902549316', '1430180443', '2298325048', '2277736917', '3427093700', '6800739932', '7225178928', '3470911185', '2535247723', '9536793717', '4657547859', '1757970814', '9582590655', '1699341497', '6191325623', '7501909777', '6802088009', '3139477233', '1705586812', '4235344341', '2383868303', '6309114546', '6737010075', '8917822880', '2650470110', '3694708742', '5409096014', '9429894049', '2799098232', '8769124438', '6807005461', '8565491866', '2720061989', '8420192724', '5880327561', '3726335033', '8337746608', '6946638561', '8103876239', '4377177237', '8542007712', '3344205986', '4021427415', '4010380006', '7195690165', '9387340026', '8224336481', '3369214370', '3437291040', '9276905895', '4436531382', '9040184279', '1448879063', '5590846606', '5038806994', '8635762763', '6385359165', '5190715246', '8919668247', '1274939963', '5092799384', '8001561399', '1091023735', '9019419278', '1081841100', '4211671474', '4511377020', '4059469202', '2440942580', '1170316444', '3975539430', '1169104074', '4981541932', '6121085721', '4914461651', '7804329439', '2806532771', '3786896748', '9251694485', '9534828103', '4098450826', '3798932204', '5274046854', '9102960732', '9075085479', '4255366243', '8655497341', '2519190174', '9963595849', '4711059620', '1159011056', '1075459867', '2076409815', '8967483632', '2372039001', '7432158905', '1755048770', '8935132174', '7769752729', '1432992915', '8450717760', '2664320137', '3524399855', '4903515779', '6571536843', '8250183278', '4114514727', '1705501838', '1580288919', '9254586855', '6208329348', '7308752983', '5765955750', '3124574560', '7325535587', '3655003206', '8288711452', '8162679095', '3624999345', '6182468614', '4509199939', '9364377919', '1471550523', '2797955634', '8188955472', '5327863766', '1670186785', '6175886315', '2440402261', '4259667985', '8272258676', '9489987214', '1161238522', '8213537614', '2749042296', '7563777107', '8519944900', '1804302194', '9493766561', '4337812651', '3091067955', '9414699372', '6672063345', '6823940370', '4906986212', '4129883793', '2735712474', '9363618300', '6960655043', '2249530974', '7390483765', '4200932526', '5022766814', '3457826075', '1137810097', '9049936652', '5748815994', '6544114973', '7649532681', '6662977255', '4504023865', '1591933046', '4830587152', '9835612374', '2963698864', '5493614584', '3634976075', '8338357406', '8661920864', '3501078994', '6592889406', '1987693128', '1001265492', '7651620515', '9880187864', '1804676952', '5312162919', '6123729487', '4895671502', '7434778209', '3939950065', '3594362422', '8550817520', '9580421624', '4563885337', '8592638261', '7476209981', '6401631773', '6855741527', '2425472542', '2519688629', '8600820801', '8446318363', '1048594676', '1926294746', '1084578824', '4224922073', '6774396158', '2442164595', '1754608261', '4410576110', '9186886904', '7634948535', '6145707670', '8554901764', '7655238752', '9485061357', '9640581703', '2648048013', '1642651674', '7740882305', '5958802888', '4593926452', '7753280866', '3127828903', '1076995295', '6395828290', '9647502396'))
ORDER BY ts DESC
LIMIT 1 ;
explain 执行结果：
Expression (Project names)
Limit (preliminary LIMIT (without OFFSET))
Sorting (Merge sorted streams after aggregation stage for ORDER BY)
Union
Sorting (Sorting for ORDER BY)
Expression ((Before ORDER BY + Projection))
Expression
ReadFromMergeTree (roi_ods.pwa_webpush_log_local)
Indexes:
MinMax
Condition: true
Parts: 500/500
Granules: 62679/62679
Partition
Condition: true
Parts: 500/500
Granules: 62679/62679
PrimaryKey
Condition: true
Parts: 500/500
Granules: 62679/62679
Skip
Name: idx_stats_minmax
Description: minmax GRANULARITY 4
Parts: 499/500
Granules: 62678/62679
Skip
Name: idx_project_id_minmax
Description: minmax GRANULARITY 64
Parts: 496/499
Granules: 62675/62678
ReadFromRemote (Read from remote replica)
|