mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2025-12-28 00:42:56 +00:00
238ed003df
* fix: group by expr not as column Signed-off-by: discord9 <discord9@163.com> * test: dist analyzer date_bin Signed-off-by: discord9 <discord9@163.com> * ???fix wip Signed-off-by: discord9 <discord9@163.com> * fix: deduce using correct input fields Signed-off-by: discord9 <discord9@163.com> * refactor: clearer wrapper Signed-off-by: discord9 <discord9@163.com> * chore: update sqlness Signed-off-by: discord9 <discord9@163.com> * chore: per review Signed-off-by: discord9 <discord9@163.com> * chore: per review Signed-off-by: discord9 <discord9@163.com> * chore: rm todo Signed-off-by: discord9 <discord9@163.com> --------- Signed-off-by: discord9 <discord9@163.com>
455 lines
25 KiB
Plaintext
455 lines
25 KiB
Plaintext
CREATE TABLE integers(
|
|
host STRING PRIMARY KEY,
|
|
i BIGINT,
|
|
ts TIMESTAMP TIME INDEX
|
|
) PARTITION ON COLUMNS (host) (
|
|
host < '550-A',
|
|
host >= '550-A'
|
|
AND host < '550-W',
|
|
host >= '550-W'
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO
|
|
integers (host, i, ts)
|
|
VALUES
|
|
('220-A', 2, '2023-01-01 00:00:00'),
|
|
('220-B', 3, '2023-01-01 00:00:00'),
|
|
('550-A', 1, '2023-01-01 00:00:00'),
|
|
('550-B', 5, '2023-01-01 00:00:00'),
|
|
('550-A', 2, '2023-01-01 01:00:00'),
|
|
('550-W', 3, '2023-01-01 02:00:00'),
|
|
('550-Z', 4, '2023-01-01 02:00:00'),
|
|
('550-W', 5, '2023-01-01 03:00:00'),
|
|
('550-Z', 6, '2023-01-01 03:00:00');
|
|
|
|
Affected Rows: 9
|
|
|
|
SELECT
|
|
count(i),
|
|
sum(i),
|
|
uddsketch_calc(0.5, uddsketch_state(128, 0.01, i)),
|
|
hll_count(hll(i))
|
|
FROM
|
|
integers;
|
|
|
|
+-------------------+-----------------+-----------------------------------------------------------------------------------+----------------------------+
|
|
| count(integers.i) | sum(integers.i) | uddsketch_calc(Float64(0.5),uddsketch_state(Int64(128),Float64(0.01),integers.i)) | hll_count(hll(integers.i)) |
|
|
+-------------------+-----------------+-----------------------------------------------------------------------------------+----------------------------+
|
|
| 9 | 31 | 2.9742334234767016 | 6 |
|
|
+-------------------+-----------------+-----------------------------------------------------------------------------------+----------------------------+
|
|
|
|
-- SQLNESS REPLACE (-+) -
|
|
-- SQLNESS REPLACE (\s\s+) _
|
|
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
|
|
-- SQLNESS REPLACE (Hash.*) REDACTED
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
EXPLAIN
|
|
SELECT
|
|
count(i),
|
|
sum(i),
|
|
uddsketch_calc(0.5, uddsketch_state(128, 0.01, i)),
|
|
hll_count(hll(i))
|
|
FROM
|
|
integers;
|
|
|
|
+-+-+
|
|
| plan_type_| plan_|
|
|
+-+-+
|
|
| logical_plan_| Projection: count(integers.i), sum(integers.i), uddsketch_calc(Float64(0.5), uddsketch_state(Int64(128),Float64(0.01),integers.i)), hll_count(hll(integers.i))_|
|
|
|_|_Aggregate: groupBy=[[]], aggr=[[__count_merge(__count_state(integers.i)) AS count(integers.i), __sum_merge(__sum_state(integers.i)) AS sum(integers.i), __uddsketch_state_merge(__uddsketch_state_state(Int64(128),Float64(0.01),integers.i)) AS uddsketch_state(Int64(128),Float64(0.01),integers.i), __hll_merge(__hll_state(integers.i)) AS hll(integers.i)]] |
|
|
|_|_MergeScan [is_placeholder=false, remote_input=[_|
|
|
|_| Aggregate: groupBy=[[]], aggr=[[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128), Float64(0.01), CAST(integers.i AS Float64)), __hll_state(CAST(integers.i AS Utf8))]]_|
|
|
|_|_TableScan: integers_|
|
|
|_| ]]_|
|
|
| physical_plan | ProjectionExec: expr=[count(integers.i)@0 as count(integers.i), sum(integers.i)@1 as sum(integers.i), uddsketch_calc(0.5, uddsketch_state(Int64(128),Float64(0.01),integers.i)@2) as uddsketch_calc(Float64(0.5),uddsketch_state(Int64(128),Float64(0.01),integers.i)), hll_count(hll(integers.i)@3) as hll_count(hll(integers.i))]_|
|
|
|_|_AggregateExec: mode=Final, gby=[], aggr=[count(integers.i), sum(integers.i), uddsketch_state(Int64(128),Float64(0.01),integers.i), hll(integers.i)]_|
|
|
|_|_CoalescePartitionsExec_|
|
|
|_|_AggregateExec: mode=Partial, gby=[], aggr=[count(integers.i), sum(integers.i), uddsketch_state(Int64(128),Float64(0.01),integers.i), hll(integers.i)]_|
|
|
|_|_CooperativeExec_|
|
|
|_|_MergeScanExec: REDACTED
|
|
|_|_|
|
|
+-+-+
|
|
|
|
-- SQLNESS REPLACE (metrics.*) REDACTED
|
|
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
|
|
-- SQLNESS REPLACE (-+) -
|
|
-- SQLNESS REPLACE (\s\s+) _
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
|
|
-- might write to different partitions
|
|
-- SQLNESS REPLACE "partition_count":\{(.*?)\} "partition_count":REDACTED
|
|
-- SQLNESS REPLACE (Hash.*) REDACTED
|
|
EXPLAIN ANALYZE
|
|
SELECT
|
|
count(i),
|
|
sum(i),
|
|
uddsketch_calc(0.5, uddsketch_state(128, 0.01, i)),
|
|
hll_count(hll(i))
|
|
FROM
|
|
integers;
|
|
|
|
+-+-+-+
|
|
| stage | node | plan_|
|
|
+-+-+-+
|
|
| 0_| 0_|_ProjectionExec: expr=[count(integers.i)@0 as count(integers.i), sum(integers.i)@1 as sum(integers.i), uddsketch_calc(0.5, uddsketch_state(Int64(128),Float64(0.01),integers.i)@2) as uddsketch_calc(Float64(0.5),uddsketch_state(Int64(128),Float64(0.01),integers.i)), hll_count(hll(integers.i)@3) as hll_count(hll(integers.i))] REDACTED
|
|
|_|_|_AggregateExec: mode=Final, gby=[], aggr=[count(integers.i), sum(integers.i), uddsketch_state(Int64(128),Float64(0.01),integers.i), hll(integers.i)] REDACTED
|
|
|_|_|_CoalescePartitionsExec REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[], aggr=[count(integers.i), sum(integers.i), uddsketch_state(Int64(128),Float64(0.01),integers.i), hll(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_MergeScanExec: REDACTED
|
|
|_|_|_|
|
|
| 1_| 0_|_AggregateExec: mode=Final, gby=[], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CoalescePartitionsExec REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED
|
|
|_|_|_|
|
|
| 1_| 1_|_AggregateExec: mode=Final, gby=[], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CoalescePartitionsExec REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED
|
|
|_|_|_|
|
|
| 1_| 2_|_AggregateExec: mode=Final, gby=[], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CoalescePartitionsExec REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED
|
|
|_|_|_|
|
|
|_|_| Total rows: 1_|
|
|
+-+-+-+
|
|
|
|
SELECT
|
|
avg(i)
|
|
FROM
|
|
integers;
|
|
|
|
+--------------------+
|
|
| avg(integers.i) |
|
|
+--------------------+
|
|
| 3.4444444444444446 |
|
|
+--------------------+
|
|
|
|
-- SQLNESS REPLACE (-+) -
|
|
-- SQLNESS REPLACE (\s\s+) _
|
|
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
|
|
-- SQLNESS REPLACE (Hash.*) REDACTED
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
EXPLAIN SELECT
|
|
avg(i)
|
|
FROM
|
|
integers;
|
|
|
|
+-+-+
|
|
| plan_type_| plan_|
|
|
+-+-+
|
|
| logical_plan_| Aggregate: groupBy=[[]], aggr=[[__avg_merge(__avg_state(integers.i)) AS avg(integers.i)]]_|
|
|
|_|_MergeScan [is_placeholder=false, remote_input=[_|
|
|
|_| Aggregate: groupBy=[[]], aggr=[[__avg_state(CAST(integers.i AS Float64))]]_|
|
|
|_|_TableScan: integers_|
|
|
|_| ]]_|
|
|
| physical_plan | AggregateExec: mode=Final, gby=[], aggr=[avg(integers.i)]_|
|
|
|_|_CoalescePartitionsExec_|
|
|
|_|_AggregateExec: mode=Partial, gby=[], aggr=[avg(integers.i)]_|
|
|
|_|_CooperativeExec_|
|
|
|_|_MergeScanExec: REDACTED
|
|
|_|_|
|
|
+-+-+
|
|
|
|
-- SQLNESS REPLACE (metrics.*) REDACTED
|
|
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
|
|
-- SQLNESS REPLACE (-+) -
|
|
-- SQLNESS REPLACE (\s\s+) _
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
|
|
-- might write to different partitions
|
|
-- SQLNESS REPLACE "partition_count":\{(.*?)\} "partition_count":REDACTED
|
|
-- SQLNESS REPLACE (Hash.*) REDACTED
|
|
EXPLAIN ANALYZE SELECT
|
|
avg(i)
|
|
FROM
|
|
integers;
|
|
|
|
+-+-+-+
|
|
| stage | node | plan_|
|
|
+-+-+-+
|
|
| 0_| 0_|_AggregateExec: mode=Final, gby=[], aggr=[avg(integers.i)] REDACTED
|
|
|_|_|_CoalescePartitionsExec REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[], aggr=[avg(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_MergeScanExec: REDACTED
|
|
|_|_|_|
|
|
| 1_| 0_|_AggregateExec: mode=Final, gby=[], aggr=[__avg_state(integers.i)] REDACTED
|
|
|_|_|_CoalescePartitionsExec REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[], aggr=[__avg_state(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED
|
|
|_|_|_|
|
|
| 1_| 1_|_AggregateExec: mode=Final, gby=[], aggr=[__avg_state(integers.i)] REDACTED
|
|
|_|_|_CoalescePartitionsExec REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[], aggr=[__avg_state(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED
|
|
|_|_|_|
|
|
| 1_| 2_|_AggregateExec: mode=Final, gby=[], aggr=[__avg_state(integers.i)] REDACTED
|
|
|_|_|_CoalescePartitionsExec REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[], aggr=[__avg_state(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED
|
|
|_|_|_|
|
|
|_|_| Total rows: 1_|
|
|
+-+-+-+
|
|
|
|
SELECT
|
|
ts,
|
|
count(i),
|
|
sum(i),
|
|
uddsketch_calc(0.5, uddsketch_state(128, 0.01, i)),
|
|
hll_count(hll(i))
|
|
FROM
|
|
integers
|
|
GROUP BY
|
|
ts
|
|
ORDER BY
|
|
ts;
|
|
|
|
+---------------------+-------------------+-----------------+-----------------------------------------------------------------------------------+----------------------------+
|
|
| ts | count(integers.i) | sum(integers.i) | uddsketch_calc(Float64(0.5),uddsketch_state(Int64(128),Float64(0.01),integers.i)) | hll_count(hll(integers.i)) |
|
|
+---------------------+-------------------+-----------------+-----------------------------------------------------------------------------------+----------------------------+
|
|
| 2023-01-01T00:00:00 | 4 | 11 | 2.9742334234767016 | 4 |
|
|
| 2023-01-01T01:00:00 | 1 | 2 | 1.9936617014173446 | 1 |
|
|
| 2023-01-01T02:00:00 | 2 | 7 | 4.014835333028587 | 2 |
|
|
| 2023-01-01T03:00:00 | 2 | 11 | 5.98951037117262 | 2 |
|
|
+---------------------+-------------------+-----------------+-----------------------------------------------------------------------------------+----------------------------+
|
|
|
|
-- SQLNESS REPLACE (-+) -
|
|
-- SQLNESS REPLACE (\s\s+) _
|
|
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
|
|
-- SQLNESS REPLACE (Hash.*) REDACTED
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
EXPLAIN
|
|
SELECT
|
|
ts,
|
|
count(i),
|
|
sum(i),
|
|
uddsketch_calc(0.5, uddsketch_state(128, 0.01, i)),
|
|
hll_count(hll(i))
|
|
FROM
|
|
integers
|
|
GROUP BY
|
|
ts
|
|
ORDER BY
|
|
ts;
|
|
|
|
+-+-+
|
|
| plan_type_| plan_|
|
|
+-+-+
|
|
| logical_plan_| Sort: integers.ts ASC NULLS LAST_|
|
|
|_|_Projection: integers.ts, count(integers.i), sum(integers.i), uddsketch_calc(Float64(0.5), uddsketch_state(Int64(128),Float64(0.01),integers.i)), hll_count(hll(integers.i))_|
|
|
|_|_Aggregate: groupBy=[[integers.ts]], aggr=[[__count_merge(__count_state(integers.i)) AS count(integers.i), __sum_merge(__sum_state(integers.i)) AS sum(integers.i), __uddsketch_state_merge(__uddsketch_state_state(Int64(128),Float64(0.01),integers.i)) AS uddsketch_state(Int64(128),Float64(0.01),integers.i), __hll_merge(__hll_state(integers.i)) AS hll(integers.i)]] |
|
|
|_|_MergeScan [is_placeholder=false, remote_input=[_|
|
|
|_| Aggregate: groupBy=[[integers.ts]], aggr=[[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128), Float64(0.01), CAST(integers.i AS Float64)), __hll_state(CAST(integers.i AS Utf8))]]_|
|
|
|_|_TableScan: integers_|
|
|
|_| ]]_|
|
|
| physical_plan | SortPreservingMergeExec: [ts@0 ASC NULLS LAST]_|
|
|
|_|_SortExec: expr=[ts@0 ASC NULLS LAST], preserve_partitioning=[true]_|
|
|
|_|_ProjectionExec: expr=[ts@0 as ts, count(integers.i)@1 as count(integers.i), sum(integers.i)@2 as sum(integers.i), uddsketch_calc(0.5, uddsketch_state(Int64(128),Float64(0.01),integers.i)@3) as uddsketch_calc(Float64(0.5),uddsketch_state(Int64(128),Float64(0.01),integers.i)), hll_count(hll(integers.i)@4) as hll_count(hll(integers.i))]_|
|
|
|_|_AggregateExec: mode=FinalPartitioned, gby=[ts@0 as ts], aggr=[count(integers.i), sum(integers.i), uddsketch_state(Int64(128),Float64(0.01),integers.i), hll(integers.i)]_|
|
|
|_|_CoalesceBatchesExec: target_batch_size=8192_|
|
|
|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_AggregateExec: mode=Partial, gby=[ts@0 as ts], aggr=[count(integers.i), sum(integers.i), uddsketch_state(Int64(128),Float64(0.01),integers.i), hll(integers.i)]_|
|
|
|_|_CooperativeExec_|
|
|
|_|_MergeScanExec: REDACTED
|
|
|_|_|
|
|
+-+-+
|
|
|
|
-- SQLNESS REPLACE (metrics.*) REDACTED
|
|
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
|
|
-- SQLNESS REPLACE (-+) -
|
|
-- SQLNESS REPLACE (\s\s+) _
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
|
|
-- might write to different partitions
|
|
-- SQLNESS REPLACE "partition_count":\{(.*?)\} "partition_count":REDACTED
|
|
-- SQLNESS REPLACE (Hash.*) REDACTED
|
|
EXPLAIN ANALYZE
|
|
SELECT
|
|
ts,
|
|
count(i),
|
|
sum(i),
|
|
uddsketch_calc(0.5, uddsketch_state(128, 0.01, i)),
|
|
hll_count(hll(i))
|
|
FROM
|
|
integers
|
|
GROUP BY
|
|
ts
|
|
ORDER BY
|
|
ts;
|
|
|
|
+-+-+-+
|
|
| stage | node | plan_|
|
|
+-+-+-+
|
|
| 0_| 0_|_SortPreservingMergeExec: [ts@0 ASC NULLS LAST] REDACTED
|
|
|_|_|_SortExec: expr=[ts@0 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|
|
|_|_|_ProjectionExec: expr=[ts@0 as ts, count(integers.i)@1 as count(integers.i), sum(integers.i)@2 as sum(integers.i), uddsketch_calc(0.5, uddsketch_state(Int64(128),Float64(0.01),integers.i)@3) as uddsketch_calc(Float64(0.5),uddsketch_state(Int64(128),Float64(0.01),integers.i)), hll_count(hll(integers.i)@4) as hll_count(hll(integers.i))] REDACTED
|
|
|_|_|_AggregateExec: mode=FinalPartitioned, gby=[ts@0 as ts], aggr=[count(integers.i), sum(integers.i), uddsketch_state(Int64(128),Float64(0.01),integers.i), hll(integers.i)] REDACTED
|
|
|_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[ts@0 as ts], aggr=[count(integers.i), sum(integers.i), uddsketch_state(Int64(128),Float64(0.01),integers.i), hll(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_MergeScanExec: REDACTED
|
|
|_|_|_|
|
|
| 1_| 0_|_AggregateExec: mode=FinalPartitioned, gby=[ts@0 as ts], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[ts@1 as ts], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED
|
|
|_|_|_|
|
|
| 1_| 1_|_AggregateExec: mode=FinalPartitioned, gby=[ts@0 as ts], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[ts@1 as ts], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED
|
|
|_|_|_|
|
|
| 1_| 2_|_AggregateExec: mode=FinalPartitioned, gby=[ts@0 as ts], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[ts@1 as ts], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED
|
|
|_|_|_|
|
|
|_|_| Total rows: 4_|
|
|
+-+-+-+
|
|
|
|
SELECT
|
|
date_bin('2s'::INTERVAL, ts) as time_window,
|
|
count(i),
|
|
sum(i),
|
|
uddsketch_calc(0.5, uddsketch_state(128, 0.01, i)),
|
|
hll_count(hll(i))
|
|
FROM
|
|
integers
|
|
GROUP BY
|
|
time_window
|
|
ORDER BY
|
|
time_window;
|
|
|
|
+---------------------+-------------------+-----------------+-----------------------------------------------------------------------------------+----------------------------+
|
|
| time_window | count(integers.i) | sum(integers.i) | uddsketch_calc(Float64(0.5),uddsketch_state(Int64(128),Float64(0.01),integers.i)) | hll_count(hll(integers.i)) |
|
|
+---------------------+-------------------+-----------------+-----------------------------------------------------------------------------------+----------------------------+
|
|
| 2023-01-01T00:00:00 | 4 | 11 | 2.9742334234767016 | 4 |
|
|
| 2023-01-01T01:00:00 | 1 | 2 | 1.9936617014173446 | 1 |
|
|
| 2023-01-01T02:00:00 | 2 | 7 | 4.014835333028587 | 2 |
|
|
| 2023-01-01T03:00:00 | 2 | 11 | 5.98951037117262 | 2 |
|
|
+---------------------+-------------------+-----------------+-----------------------------------------------------------------------------------+----------------------------+
|
|
|
|
-- SQLNESS REPLACE (-+) -
|
|
-- SQLNESS REPLACE (\s\s+) _
|
|
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
|
|
-- SQLNESS REPLACE (Hash.*) REDACTED
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
EXPLAIN
|
|
SELECT
|
|
date_bin('2s'::INTERVAL, ts) as time_window,
|
|
count(i),
|
|
sum(i),
|
|
uddsketch_calc(0.5, uddsketch_state(128, 0.01, i)),
|
|
hll_count(hll(i))
|
|
FROM
|
|
integers
|
|
GROUP BY
|
|
time_window
|
|
ORDER BY
|
|
time_window;
|
|
|
|
+-+-+
|
|
| plan_type_| plan_|
|
|
+-+-+
|
|
| logical_plan_| Sort: time_window ASC NULLS LAST_|
|
|
|_|_Projection: date_bin(Utf8("2 seconds"),integers.ts) AS time_window, count(integers.i), sum(integers.i), uddsketch_calc(Float64(0.5), uddsketch_state(Int64(128),Float64(0.01),integers.i)), hll_count(hll(integers.i))_|
|
|
|_|_Aggregate: groupBy=[[date_bin(Utf8("2 seconds"),integers.ts)]], aggr=[[__count_merge(__count_state(integers.i)) AS count(integers.i), __sum_merge(__sum_state(integers.i)) AS sum(integers.i), __uddsketch_state_merge(__uddsketch_state_state(Int64(128),Float64(0.01),integers.i)) AS uddsketch_state(Int64(128),Float64(0.01),integers.i), __hll_merge(__hll_state(integers.i)) AS hll(integers.i)]] |
|
|
|_|_MergeScan [is_placeholder=false, remote_input=[_|
|
|
|_| Aggregate: groupBy=[[date_bin(CAST(Utf8("2 seconds") AS Interval(MonthDayNano)), integers.ts)]], aggr=[[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128), Float64(0.01), CAST(integers.i AS Float64)), __hll_state(CAST(integers.i AS Utf8))]]_|
|
|
|_|_TableScan: integers_|
|
|
|_| ]]_|
|
|
| physical_plan | SortPreservingMergeExec: [time_window@0 ASC NULLS LAST]_|
|
|
|_|_SortExec: expr=[time_window@0 ASC NULLS LAST], preserve_partitioning=[true]_|
|
|
|_|_ProjectionExec: expr=[date_bin(Utf8("2 seconds"),integers.ts)@0 as time_window, count(integers.i)@1 as count(integers.i), sum(integers.i)@2 as sum(integers.i), uddsketch_calc(0.5, uddsketch_state(Int64(128),Float64(0.01),integers.i)@3) as uddsketch_calc(Float64(0.5),uddsketch_state(Int64(128),Float64(0.01),integers.i)), hll_count(hll(integers.i)@4) as hll_count(hll(integers.i))]_|
|
|
|_|_AggregateExec: mode=FinalPartitioned, gby=[date_bin(Utf8("2 seconds"),integers.ts)@0 as date_bin(Utf8("2 seconds"),integers.ts)], aggr=[count(integers.i), sum(integers.i), uddsketch_state(Int64(128),Float64(0.01),integers.i), hll(integers.i)]_|
|
|
|_|_CoalesceBatchesExec: target_batch_size=8192_|
|
|
|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_AggregateExec: mode=Partial, gby=[date_bin(Utf8("2 seconds"),integers.ts)@0 as date_bin(Utf8("2 seconds"),integers.ts)], aggr=[count(integers.i), sum(integers.i), uddsketch_state(Int64(128),Float64(0.01),integers.i), hll(integers.i)]_|
|
|
|_|_CooperativeExec_|
|
|
|_|_MergeScanExec: REDACTED
|
|
|_|_|
|
|
+-+-+
|
|
|
|
-- SQLNESS REPLACE (metrics.*) REDACTED
|
|
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
|
|
-- SQLNESS REPLACE (-+) -
|
|
-- SQLNESS REPLACE (\s\s+) _
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
|
|
-- might write to different partitions
|
|
-- SQLNESS REPLACE "partition_count":\{(.*?)\} "partition_count":REDACTED
|
|
-- SQLNESS REPLACE (Hash.*) REDACTED
|
|
EXPLAIN ANALYZE
|
|
SELECT
|
|
date_bin('2s'::INTERVAL, ts) as time_window,
|
|
count(i),
|
|
sum(i),
|
|
uddsketch_calc(0.5, uddsketch_state(128, 0.01, i)),
|
|
hll_count(hll(i))
|
|
FROM
|
|
integers
|
|
GROUP BY
|
|
time_window
|
|
ORDER BY
|
|
time_window;
|
|
|
|
+-+-+-+
|
|
| stage | node | plan_|
|
|
+-+-+-+
|
|
| 0_| 0_|_SortPreservingMergeExec: [time_window@0 ASC NULLS LAST] REDACTED
|
|
|_|_|_SortExec: expr=[time_window@0 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|
|
|_|_|_ProjectionExec: expr=[date_bin(Utf8("2 seconds"),integers.ts)@0 as time_window, count(integers.i)@1 as count(integers.i), sum(integers.i)@2 as sum(integers.i), uddsketch_calc(0.5, uddsketch_state(Int64(128),Float64(0.01),integers.i)@3) as uddsketch_calc(Float64(0.5),uddsketch_state(Int64(128),Float64(0.01),integers.i)), hll_count(hll(integers.i)@4) as hll_count(hll(integers.i))] REDACTED
|
|
|_|_|_AggregateExec: mode=FinalPartitioned, gby=[date_bin(Utf8("2 seconds"),integers.ts)@0 as date_bin(Utf8("2 seconds"),integers.ts)], aggr=[count(integers.i), sum(integers.i), uddsketch_state(Int64(128),Float64(0.01),integers.i), hll(integers.i)] REDACTED
|
|
|_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[date_bin(Utf8("2 seconds"),integers.ts)@0 as date_bin(Utf8("2 seconds"),integers.ts)], aggr=[count(integers.i), sum(integers.i), uddsketch_state(Int64(128),Float64(0.01),integers.i), hll(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_MergeScanExec: REDACTED
|
|
|_|_|_|
|
|
| 1_| 0_|_AggregateExec: mode=FinalPartitioned, gby=[date_bin(Utf8("2 seconds"),integers.ts)@0 as date_bin(Utf8("2 seconds"),integers.ts)], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[date_bin(IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 2000000000 }, ts@1) as date_bin(Utf8("2 seconds"),integers.ts)], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED
|
|
|_|_|_|
|
|
| 1_| 1_|_AggregateExec: mode=FinalPartitioned, gby=[date_bin(Utf8("2 seconds"),integers.ts)@0 as date_bin(Utf8("2 seconds"),integers.ts)], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[date_bin(IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 2000000000 }, ts@1) as date_bin(Utf8("2 seconds"),integers.ts)], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED
|
|
|_|_|_|
|
|
| 1_| 2_|_AggregateExec: mode=FinalPartitioned, gby=[date_bin(Utf8("2 seconds"),integers.ts)@0 as date_bin(Utf8("2 seconds"),integers.ts)], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CoalesceBatchesExec: target_batch_size=8192 REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[date_bin(IntervalMonthDayNano { months: 0, days: 0, nanoseconds: 2000000000 }, ts@1) as date_bin(Utf8("2 seconds"),integers.ts)], aggr=[__count_state(integers.i), __sum_state(integers.i), __uddsketch_state_state(Int64(128),Float64(0.01),integers.i), __hll_state(integers.i)] REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_SeqScan: region=REDACTED, "partition_count":REDACTED REDACTED
|
|
|_|_|_|
|
|
|_|_| Total rows: 4_|
|
|
+-+-+-+
|
|
|
|
DROP TABLE integers;
|
|
|
|
Affected Rows: 0
|
|
|