mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-05-25 01:10:37 +00:00
59dd418629
* as optimizer rule Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * dump changes Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * perf: tighten count-count optimizer rewrite * extend inner op set Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * simplify and more coverage Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * remove prom-non-null Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * preserve value column through pruning Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * more sqlness cases Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * rename Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * enforce is not null before inner aggr Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * finalize Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * update sqlness result Signed-off-by: Ruihang Xia <waynestxia@gmail.com> --------- Signed-off-by: Ruihang Xia <waynestxia@gmail.com>
202 lines
9.6 KiB
Plaintext
202 lines
9.6 KiB
Plaintext
CREATE TABLE tsid_physical (
|
|
ts TIMESTAMP(3) TIME INDEX,
|
|
val DOUBLE,
|
|
) ENGINE = metric WITH ("physical_metric_table" = "");
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE tsid_metric (
|
|
job STRING NULL,
|
|
instance STRING NULL,
|
|
ts TIMESTAMP(3) NOT NULL,
|
|
val DOUBLE NULL,
|
|
TIME INDEX (ts),
|
|
PRIMARY KEY(job, instance),
|
|
)
|
|
ENGINE = metric
|
|
WITH(
|
|
on_physical_table = 'tsid_physical'
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO tsid_metric VALUES
|
|
('job1', 'instance1', 0, 1),
|
|
('job1', 'instance2', 0, 2),
|
|
('job1', 'instance1', 5000, 3),
|
|
('job1', 'instance2', 5000, 4),
|
|
('job1', 'instance1', 10000, 5),
|
|
('job1', 'instance2', 10000, 6);
|
|
|
|
Affected Rows: 6
|
|
|
|
-- 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
|
|
-- SQLNESS REPLACE (Hash.*) REDACTED
|
|
TQL ANALYZE (0, 10, '5s') sum(tsid_metric);
|
|
|
|
+-+-+-+
|
|
| stage | node | plan_|
|
|
+-+-+-+
|
|
| 0_| 0_|_CooperativeExec REDACTED
|
|
|_|_|_MergeScanExec: REDACTED
|
|
|_|_|_|
|
|
| 1_| 0_|_SortPreservingMergeExec: [ts@0 ASC NULLS LAST] REDACTED
|
|
|_|_|_SortExec: expr=[ts@0 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|
|
|_|_|_AggregateExec: mode=FinalPartitioned, gby=[ts@0 as ts], aggr=[sum(tsid_metric.val)] REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[ts@1 as ts], aggr=[sum(tsid_metric.val)] REDACTED
|
|
|_|_|_ProjectionExec: expr=[val@0 as val, ts@2 as ts] REDACTED
|
|
|_|_|_PromInstantManipulateExec: range=[0..10000], lookback=[300000], interval=[5000], time index=[ts] REDACTED
|
|
|_|_|_PromSeriesDivideExec: tags=["__tsid"] REDACTED
|
|
|_|_|_ProjectionExec: expr=[val@1 as val, __tsid@2 as __tsid, ts@0 as ts] REDACTED
|
|
|_|_|_SeriesScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":1, "files":0, "file_ranges":0}, "distribution":"PerSeries" REDACTED
|
|
|_|_|_|
|
|
|_|_| Total rows: 3_|
|
|
+-+-+-+
|
|
|
|
-- 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
|
|
-- SQLNESS REPLACE (Hash.*) REDACTED
|
|
TQL ANALYZE (0, 10, '5s') sum by (job, instance) (tsid_metric);
|
|
|
|
+-+-+-+
|
|
| stage | node | plan_|
|
|
+-+-+-+
|
|
| 0_| 0_|_CooperativeExec REDACTED
|
|
|_|_|_MergeScanExec: REDACTED
|
|
|_|_|_|
|
|
| 1_| 0_|_SortPreservingMergeExec: [job@0 ASC NULLS LAST, instance@1 ASC NULLS LAST, ts@2 ASC NULLS LAST] REDACTED
|
|
|_|_|_SortExec: expr=[job@0 ASC NULLS LAST, instance@1 ASC NULLS LAST, ts@2 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|
|
|_|_|_AggregateExec: mode=FinalPartitioned, gby=[job@0 as job, instance@1 as instance, ts@2 as ts], aggr=[sum(tsid_metric.val), __tsid] REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[job@2 as job, instance@1 as instance, ts@4 as ts], aggr=[sum(tsid_metric.val), __tsid] REDACTED
|
|
|_|_|_PromInstantManipulateExec: range=[0..10000], lookback=[300000], interval=[5000], time index=[ts] REDACTED
|
|
|_|_|_PromSeriesDivideExec: tags=["__tsid"] REDACTED
|
|
|_|_|_ProjectionExec: expr=[val@1 as val, instance@3 as instance, job@4 as job, __tsid@2 as __tsid, ts@0 as ts] REDACTED
|
|
|_|_|_SeriesScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":1, "files":0, "file_ranges":0}, "distribution":"PerSeries" REDACTED
|
|
|_|_|_|
|
|
|_|_| Total rows: 6_|
|
|
+-+-+-+
|
|
|
|
-- 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
|
|
-- SQLNESS REPLACE (Hash.*) REDACTED
|
|
TQL ANALYZE (0, 10, '5s') sum(irate(tsid_metric[1h])) / scalar(count(count(tsid_metric) by (job)));
|
|
|
|
+-+-+-+
|
|
| stage | node | plan_|
|
|
+-+-+-+
|
|
| 0_| 0_|_ProjectionExec: expr=[ts@1 as ts, sum(prom_irate(ts_range,val))@2 / scalar(count(count(tsid_metric.val)))@0 as lhs.sum(prom_irate(ts_range,val)) / rhs.scalar(count(count(tsid_metric.val)))] REDACTED
|
|
|_|_|_REDACTED
|
|
|_|_|_ScalarCalculateExec: tags=[] REDACTED
|
|
|_|_|_CoalescePartitionsExec REDACTED
|
|
|_|_|_MergeScanExec: REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_MergeScanExec: REDACTED
|
|
|_|_|_|
|
|
| 1_| 0_|_SortPreservingMergeExec: [ts@0 ASC NULLS LAST] REDACTED
|
|
|_|_|_SortExec: expr=[ts@0 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|
|
|_|_|_AggregateExec: mode=FinalPartitioned, gby=[ts@0 as ts], aggr=[count(count(tsid_metric.val))] REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[ts@0 as ts], aggr=[count(count(tsid_metric.val))] REDACTED
|
|
|_|_|_ProjectionExec: expr=[ts@0 as ts] REDACTED
|
|
|_|_|_AggregateExec: mode=FinalPartitioned, gby=[ts@0 as ts, job@1 as job], aggr=[] REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[ts@0 as ts, job@1 as job], aggr=[] REDACTED
|
|
|_|_|_ProjectionExec: expr=[ts@3 as ts, job@1 as job] REDACTED
|
|
|_|_|_PromInstantManipulateExec: range=[0..10000], lookback=[300000], interval=[5000], time index=[ts] REDACTED
|
|
|_|_|_PromSeriesDivideExec: tags=["__tsid"] REDACTED
|
|
|_|_|_ProjectionExec: expr=[val@1 as val, job@3 as job, __tsid@2 as __tsid, ts@0 as ts] REDACTED
|
|
|_|_|_SeriesScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":1, "files":0, "file_ranges":0}, "distribution":"PerSeries" REDACTED
|
|
|_|_|_|
|
|
| 1_| 0_|_SortPreservingMergeExec: [ts@0 ASC NULLS LAST] REDACTED
|
|
|_|_|_SortExec: expr=[ts@0 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|
|
|_|_|_AggregateExec: mode=FinalPartitioned, gby=[ts@0 as ts], aggr=[sum(prom_irate(ts_range,val))] REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[ts@0 as ts], aggr=[sum(prom_irate(ts_range,val))] REDACTED
|
|
|_|_|_FilterExec: prom_irate(ts_range,val)@1 IS NOT NULL REDACTED
|
|
|_|_|_ProjectionExec: expr=[ts@2 as ts, prom_irate(ts_range@3, val@0) as prom_irate(ts_range,val)] REDACTED
|
|
|_|_|_PromRangeManipulateExec: req range=[0..10000], interval=[5000], eval range=[3600000], time index=[ts] REDACTED
|
|
|_|_|_PromSeriesNormalizeExec: offset=[0], time index=[ts], filter NaN: [true] REDACTED
|
|
|_|_|_PromSeriesDivideExec: tags=["__tsid"] REDACTED
|
|
|_|_|_ProjectionExec: expr=[val@1 as val, __tsid@2 as __tsid, ts@0 as ts] REDACTED
|
|
|_|_|_SeriesScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":1, "files":0, "file_ranges":0}, "distribution":"PerSeries" REDACTED
|
|
|_|_|_|
|
|
|_|_| Total rows: 2_|
|
|
+-+-+-+
|
|
|
|
-- 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
|
|
-- SQLNESS REPLACE (Hash.*) REDACTED
|
|
TQL ANALYZE (0, 10, '5s') sum(irate(tsid_metric[1h])) / scalar(count(sum(tsid_metric) by (job)));
|
|
|
|
+-+-+-+
|
|
| stage | node | plan_|
|
|
+-+-+-+
|
|
| 0_| 0_|_ProjectionExec: expr=[ts@1 as ts, sum(prom_irate(ts_range,val))@2 / scalar(count(sum(tsid_metric.val)))@0 as lhs.sum(prom_irate(ts_range,val)) / rhs.scalar(count(sum(tsid_metric.val)))] REDACTED
|
|
|_|_|_REDACTED
|
|
|_|_|_ScalarCalculateExec: tags=[] REDACTED
|
|
|_|_|_CoalescePartitionsExec REDACTED
|
|
|_|_|_MergeScanExec: REDACTED
|
|
|_|_|_CooperativeExec REDACTED
|
|
|_|_|_MergeScanExec: REDACTED
|
|
|_|_|_|
|
|
| 1_| 0_|_SortPreservingMergeExec: [ts@0 ASC NULLS LAST] REDACTED
|
|
|_|_|_SortExec: expr=[ts@0 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|
|
|_|_|_AggregateExec: mode=FinalPartitioned, gby=[ts@0 as ts], aggr=[count(sum(tsid_metric.val))] REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[ts@0 as ts], aggr=[count(sum(tsid_metric.val))] REDACTED
|
|
|_|_|_ProjectionExec: expr=[ts@0 as ts] REDACTED
|
|
|_|_|_AggregateExec: mode=FinalPartitioned, gby=[ts@0 as ts, job@1 as job], aggr=[] REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[ts@0 as ts, job@1 as job], aggr=[] REDACTED
|
|
|_|_|_ProjectionExec: expr=[ts@1 as ts, job@0 as job] REDACTED
|
|
|_|_|_FilterExec: val@0 IS NOT NULL, projection=[job@1, ts@2] REDACTED
|
|
|_|_|_ProjectionExec: expr=[val@0 as val, job@1 as job, ts@3 as ts] REDACTED
|
|
|_|_|_PromInstantManipulateExec: range=[0..10000], lookback=[300000], interval=[5000], time index=[ts] REDACTED
|
|
|_|_|_PromSeriesDivideExec: tags=["__tsid"] REDACTED
|
|
|_|_|_ProjectionExec: expr=[val@1 as val, job@3 as job, __tsid@2 as __tsid, ts@0 as ts] REDACTED
|
|
|_|_|_SeriesScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":1, "files":0, "file_ranges":0}, "distribution":"PerSeries" REDACTED
|
|
|_|_|_|
|
|
| 1_| 0_|_SortPreservingMergeExec: [ts@0 ASC NULLS LAST] REDACTED
|
|
|_|_|_SortExec: expr=[ts@0 ASC NULLS LAST], preserve_partitioning=[true] REDACTED
|
|
|_|_|_AggregateExec: mode=FinalPartitioned, gby=[ts@0 as ts], aggr=[sum(prom_irate(ts_range,val))] REDACTED
|
|
|_|_|_RepartitionExec: partitioning=REDACTED
|
|
|_|_|_AggregateExec: mode=Partial, gby=[ts@0 as ts], aggr=[sum(prom_irate(ts_range,val))] REDACTED
|
|
|_|_|_FilterExec: prom_irate(ts_range,val)@1 IS NOT NULL REDACTED
|
|
|_|_|_ProjectionExec: expr=[ts@2 as ts, prom_irate(ts_range@3, val@0) as prom_irate(ts_range,val)] REDACTED
|
|
|_|_|_PromRangeManipulateExec: req range=[0..10000], interval=[5000], eval range=[3600000], time index=[ts] REDACTED
|
|
|_|_|_PromSeriesNormalizeExec: offset=[0], time index=[ts], filter NaN: [true] REDACTED
|
|
|_|_|_PromSeriesDivideExec: tags=["__tsid"] REDACTED
|
|
|_|_|_ProjectionExec: expr=[val@1 as val, __tsid@2 as __tsid, ts@0 as ts] REDACTED
|
|
|_|_|_SeriesScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":1, "files":0, "file_ranges":0}, "distribution":"PerSeries" REDACTED
|
|
|_|_|_|
|
|
|_|_| Total rows: 2_|
|
|
+-+-+-+
|
|
|
|
DROP TABLE tsid_metric;
|
|
|
|
Affected Rows: 0
|
|
|
|
DROP TABLE tsid_physical;
|
|
|
|
Affected Rows: 0
|
|
|