Pivot table: remove preaggregation
I have investigated whether the preaggregation is necessary in order
to achieve reasonable performance, and it does not seem to be the
case: version of query without preaggregation performs about the same
way on a random sample of 300 traces.
Therefore, it's useful to get rid of preaggregation in order to
simplify query generation for further feature development and
maintenance. This CL is doing exactly that.
Bug: b/259545074
Change-Id: I34f502da33f10685e10778ee37a871824264dbb1
diff --git a/ui/src/frontend/pivot_table_redux_query_generator.ts b/ui/src/frontend/pivot_table_redux_query_generator.ts
index 181fe5c..018fe81 100644
--- a/ui/src/frontend/pivot_table_redux_query_generator.ts
+++ b/ui/src/frontend/pivot_table_redux_query_generator.ts
@@ -28,7 +28,6 @@
import {globals} from './globals';
import {
Aggregation,
- AggregationFunction,
TableColumn,
tableColumnEquals,
} from './pivot_table_redux_types';
@@ -90,13 +89,6 @@
argument: string;
}
-function outerAggregation(fn: AggregationFunction): AggregationFunction {
- if (fn === 'COUNT') {
- return 'SUM';
- }
- return fn;
-}
-
// Exception thrown by query generator in case incoming parameters are not
// suitable in order to build a correct query; these are caught by the UI and
// displayed to the user.
@@ -118,7 +110,7 @@
export function expression(column: TableColumn): string {
switch (column.kind) {
case 'regular':
- return column.column;
+ return `${column.table}.${column.column}`;
case 'argument':
return extractArgumentExpression(column.argument);
}
@@ -137,55 +129,6 @@
return `extract_arg(${prefix}arg_set_id, ${sqliteString(argument)})`;
}
-function generateInnerQuery(
- pivots: TableColumn[],
- aggregations: Aggregation[],
- includeTrack: boolean,
- area: Area,
- constrainToArea: boolean): {query: string, groupByColumns: string[]} {
- const aggregationColumns: string[] = [];
-
- for (let i = 0; i < aggregations.length; i++) {
- aggregationColumns.push(
- `${aggregationExpression(aggregations[i])} as ${aggregationAlias(i)}`);
- }
-
- const selectColumns: string[] = [];
- const groupByColumns: string[] = [];
-
- let argumentCount = 0;
- for (const column of pivots) {
- switch (column.kind) {
- case 'regular': {
- selectColumns.push(column.column);
- groupByColumns.push(column.column);
- break;
- }
- case 'argument': {
- const alias = `pivot_argument_${argumentCount++}`;
- selectColumns.push(
- `${extractArgumentExpression(column.argument)} as ${alias}`);
- groupByColumns.push(alias);
- break;
- }
- }
- }
- if (includeTrack) {
- selectColumns.push('track_id');
- }
-
- const query = `
- select
- ${selectColumns.concat(aggregationColumns).join(',\n')}
- from slice
- ${(constrainToArea ? `where ${areaFilter(area)}` : '')}
- group by ${
- groupByColumns.concat(includeTrack ? ['track_id'] : []).join(', ')}
- `;
-
- return {query, groupByColumns};
-}
-
export function aggregationIndex(pivotColumns: number, aggregationNo: number) {
return pivotColumns + aggregationNo;
}
@@ -208,26 +151,17 @@
throw new QueryGeneratorError('No pivots selected');
}
- const outerAggregations = [];
- const innerQuery = generateInnerQuery(
- slicePivots,
- sliceTableAggregations,
- nonSlicePivots.length > 0,
- globals.state.areas[state.selectionArea.areaId],
- state.constrainToArea);
+ const aggregations = sliceTableAggregations.map(
+ (agg, index) =>
+ `${aggregationExpression(agg)} as ${aggregationAlias(index)}`);
- const prefixedSlicePivots =
- innerQuery.groupByColumns.map((p) => `preaggregated.${p}`);
+ const prefixedSlicePivots = slicePivots.map(expression);
const renderedNonSlicePivots =
nonSlicePivots.map((pivot) => `${pivot.table}.${pivot.column}`);
const sortCriteria =
globals.state.nonSerializableState.pivotTableRedux.sortCriteria;
const sortClauses: string[] = [];
for (let i = 0; i < sliceTableAggregations.length; i++) {
- const agg = `preaggregated.${aggregationAlias(i)}`;
- const fn = outerAggregation(sliceTableAggregations[i].aggregationFunction);
- outerAggregations.push(`${fn}(${agg}) as ${aggregationAlias(i)}`);
-
if (sortCriteria !== undefined &&
tableColumnEquals(
sliceTableAggregations[i].column, sortCriteria.column)) {
@@ -236,20 +170,22 @@
}
const joins = `
- left join thread_track on thread_track.id = preaggregated.track_id
+ left join thread_track on thread_track.id = slice.track_id
left join thread using (utid)
left join process using (upid)
`;
+ const whereClause = state.constrainToArea ?
+ `where ${areaFilter(globals.state.areas[state.selectionArea.areaId])}` :
+ '';
const text = `
select
${
- renderedNonSlicePivots.concat(prefixedSlicePivots, outerAggregations)
+ renderedNonSlicePivots.concat(prefixedSlicePivots, aggregations)
.join(',\n')}
- from (
- ${innerQuery.query}
- ) preaggregated
+ from slice
${nonSlicePivots.length > 0 ? joins : ''}
+ ${whereClause}
group by ${renderedNonSlicePivots.concat(prefixedSlicePivots).join(', ')}
${sortClauses.length > 0 ? ('order by ' + sortClauses.join(', ')) : ''}
`;
