Add ci_finished_pipelines_daily_mv ClickHouse materialized view
What does this MR do and why?
This MR adds a ci_finished_pipelines_daily_mv ClickHouse materialized view, to complement the existing ci_finished_pipelines_hourly_mv materialized view. This allows us to maintain performance when showing graphs over a longer span of time (up to 1 year).
Changelog: added
Closes #486259 (closed)
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
n/a
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
n/a
Database query plan
Query plan
EXPLAIN indexes = 1
SELECT
ci_finished_pipelines_daily.status,
countMerge(ci_finished_pipelines_daily.count_pipelines) AS count
FROM ci_finished_pipelines_daily
WHERE (ci_finished_pipelines_daily.path = '94/96/97/100/') AND (ci_finished_pipelines_daily.started_at_bucket >= toDateTime64('2024-08-01 00:00:00', 6, 'UTC')) AND (ci_finished_pipelines_daily.started_at_bucket < toDateTime64('2024-09-01 00:00:00', 6, 'UTC')) AND (ci_finished_pipelines_daily.status IN ('success', 'canceled', 'skipped'))
GROUP BY ci_finished_pipelines_daily.status
Query id: 00497c3c-164b-4c03-9a02-aedb67af8e8f
┌─explain─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ Expression ((Project names + Projection)) │
2. │ Aggregating │
3. │ Expression (Before GROUP BY) │
4. │ Expression │
5. │ ReadFromMergeTree (gitlab_clickhouse_development.ci_finished_pipelines_daily) │
6. │ Indexes: │
7. │ PrimaryKey │
8. │ Keys: │
9. │ started_at_bucket │
10. │ path │
11. │ status │
12. │ Condition: and((status in 3-element set), and((started_at_bucket in (-Inf, '1725148800')), and((started_at_bucket in ['1722470400', +Inf)), (path in ['94/96/97/100/', '94/96/97/100/'])))) │
13. │ Parts: 1/1 │
14. │ Granules: 1/1 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
14 rows in set. Elapsed: 0.002 sec.Edited by Pedro Pombeiro