opt: common subexpression elimination across aggregates

[justinj]

TPCH Analyzed suggests a cute optimization which ostensibly helps with Q1:

Turn

SELECT sum(a), count(a), avg(a) FROM x

into

SELECT s, c, s/c FROM (SELECT sum(a) s, count(a) c FROM x)

This issue is for tracking this optimization.

I've done some brief investigation, on my laptop and gceworker I saw no noticeable benefit to Q1 via performing this optimization. I ran a simplified experiment on my laptop, where I benchmarked the performance of these two queries:

"optimized":

select s, c, s::decimal/c::decimal from
      (select sum(l_quantity) as s, count(*) as c from lineitem)

"unoptimized":

select sum(l_quantity), count(*), avg(l_quantity) from lineitem

With 10 interleaved runs of each, the results I got were:

query	average result
optimized	5.199053
unoptimized	5.270249

Which is not especially convincing that this optimization is worthwhile for us right now.

In this particular example we were probably heavily limited by our scan speed, rather than our CPU performance, so it might be worth looking into this more, especially if our scan speeds improve in the future.

Jira issue: CRDB-4628

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 5 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!