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

Flatten nested aggregate queries when possible

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • None
    • None
    • None
    • 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

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

              Dates

                Created:
                Updated: