vmarcos
commented
11 months ago As part of this work, we may want to additionally consider the following:
- [ ] Adding an annotation to each
TopKnode regarding whether it is implementing adistinct onor alimit.
This would allow us to have EXPLAIN output that looks like the following:
CREATE TABLE teachers (id INT, name TEXT);
CREATE TABLE sections (id INT, teacher_id INT, course_id INT, schedule TEXT);
-- Illustrates that new hints apply without ambiguity in a single query block.
CREATE MATERIALIZED VIEW distinct_on_group_by_limit AS
SELECT DISTINCT ON(teacher_id) id, teacher_id, MAX(course_id)
FROM sections
GROUP BY id, teacher_id
OPTIONS (
AGGREGATE INPUT GROUP SIZE = 1000,
DISTINCT ON INPUT GROUP SIZE = 60,
LIMIT INPUT GROUP SIZE = 50)
ORDER BY teacher_id, id
LIMIT 2;
EXPLAIN PLAN FOR MATERIALIZED VIEW distinct_on_group_by_limit;
-- With lineage, we could improve the EXPLAIN output as follows:
CREATE TABLE
CREATE TABLE
CREATE MATERIALIZED VIEW
Optimized Plan
-------------------------------------------------------------------------------------
materialize.public.distinct_on_group_by_limit: +
TopK order_by=[#1 asc nulls_last, #0 asc nulls_last] limit=2 input_group_size=50 +
TopK group_by=[#1] order_by=[#0 asc nulls_last] distinct on input_group_size=60+
Reduce group_by=[#0, #1] aggregates=[max(#2)] input_group_size=1000 +
Project (#0..=#2) +
ReadStorage materialize.public.sections +
(1 row)
aalexandrov
antiguru
lfest
ggevay
Product outcome
Currently, internal arrangement sizes are reported only in relation to the rendered dataflow graph that creates them. Consequently, it is hard to figure out which parts of a SQL query are the main contributors for excessive memory consumption. The current approach is to infer this manually by looking at the rendered dataflow graph. This is time consuming, error-prone, and in general impossible for people that are not well-versed in our rendering code. This approach does not scale, and at the moment is effectively limited to the members of the @MaterializeInc/compute team.
Automatically relating internal arrangements to SQL-level syntax fragments is hard, but relating them to the
MirRelationExpr(sub-)terms produced in theOPTIMIZED PLANstage is a tractable problem. As an outcome of this epic, Materialize customers as well as field engineering and engineering ambassadors (who are the more likely first consumers of this feature) should be able to runcommands on existing materialized view and index items. The output will show the cumulative memory consumption (reported as the sum of the arrangement sizes) associated with each
MirRelationExprin theOPTIMIZED PLANfor the explained item. For exampleEdit: Also, we'd like to show not just memory usage, but CPU usage, skewness, hydration state, input size in rows or bytes, etc. Basically, all the things that are shown in introspection relations at the level of dataflow operators, we'd like to map back to the MIR plan.
Discovery
To achieve the outcome of the epic we need to solve the following sub-problems:
OPTIMIZED PLANto the LIR expressions in the MIR ⇒ LIR lowering step.mz_internaltables in a way which allows us to attach this information to themz_arrangement_sizesdata.Attributethat can be requested with anExplainConfigflag.Some of the sub-problems have different viable solutions and we will need to motivate our choice (probably in a design doc).
Work items
Now
Related release blockers
Related
EXPLAINoutput corners that need to be polished before we can ship the featureNext
Related feature requests that will become easier to ship by piggy-backing on implementation work done for this feature.
Decision log