Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-2794

Flatten nested aggregate queries when possible

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      The following query:

      SELECT TRUNC(ts,'HOUR'), AVG(avg_val)
      FROM (SELECT AVG(val),ts FROM T GROUP BY ts)
      GROUP BY TRUNC(ts,'HOUR');
      

      will run much more efficiently if flattened so that the hourly bucketing is done on the server-side like this:

      SELECT TRUNC(ts,'HOUR'), AVG(val)
      FROM T
      GROUP BY TRUNC(ts,'HOUR');
      

      We should flatten when possible. Not sure what the general rule is, but perhaps if the inner and outer aggregate function matches, you can always do this? Maybe only for some aggregate functions like SUM, MIN, MAX, AVG?

      This comes up in time series queries in particular.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                jamestaylor James R. Taylor
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: