[FEATURE] Timechart Performance Enhancement

[selsong]

Is your feature request related to a problem?
This is an enhancement.
Currently, the timechart command with count and fill zero runs queries roughly 4x slower than necessary. This is because the query executes in two passes:
First pass: Aggregate events per time span and by field (e.g., host) without zero-filling.
Second pass: Generate all timestamp x category combinations and apply zero-filling, unioning the result with actual counts.
This duplication of aggregation first, then zero-fill is what causes the performance overhead.

For Context:
Timechart PR: #3993
Timechart RFC: #3965

What solution would you like?
We could improve performance by either:

1. Split approach: Reuse the first-pass aggregation result and remove redundant work from the second pass.
2. Single-pass approach: Remove the second half entirely by combining aggregation and zero-filling in one pass, preserving all behaviors (limit, top N, OTHER category, null handling).
   The goal is to maintain correct output while reducing unnecessary re-aggregation and joins.

What alternatives have you considered?
Keep the current two-pass implementation (slow, ~4x runtime).
Partial optimization: only reuse part of the first-pass results, but the second pass still does some redundant work.

Do you have any additional context?
Big5 Performance Results:
SetUp: 3-node testing on EC2. Full dataset enables proper shard distribution across nodes (>333M docs/node)

Query without BY field:
source=big5 | timechart span=1h count
Average: 71,500ms
P90: 77,472ms

Query with BY field:
source=big5 | timechart span=1h count by cloud.region()
Average: 225,100ms
P90: 243,677ms
3.14x slower than without by field

SQL Queries for timechart limit=3 count() by host also seen in UT, CalcitePPLTimechartTest.java:

SELECT `@timestamp`, `host`, SUM(`actual_count`) AS `count`
FROM (
    SELECT CAST(t1.`@timestamp` AS TIMESTAMP) AS `@timestamp`,
           CASE WHEN t7.`host` IS NOT NULL THEN t1.`host`
                ELSE CASE WHEN t1.`host` IS NULL THEN NULL ELSE 'OTHER' END
           END AS `host`,
           SUM(t1.`$f2_0`) AS `actual_count`
    FROM (
        SELECT SPAN(`@timestamp`, 1, 'm') AS `@timestamp`,
               `host`,
               COUNT(*) AS `$f2_0`
        FROM `scott`.`events`
        GROUP BY `host`, SPAN(`@timestamp`, 1, 'm')
    ) AS t1
    LEFT JOIN (
        SELECT `host`, SUM(`$f2_0`) AS `grand_total`
        FROM (
            SELECT SPAN(`@timestamp`, 1, 'm') AS `@timestamp`,
                   `host`,
                   COUNT(*) AS `$f2_0`
            FROM `scott`.`events`
            GROUP BY `host`, SPAN(`@timestamp`, 1, 'm')
        ) AS t4
        WHERE `host` IS NOT NULL
        GROUP BY `host`
        ORDER BY 2 DESC NULLS FIRST
        LIMIT 3
    ) AS t7
    ON t1.`host` IS NOT DISTINCT FROM t7.`host`
    GROUP BY CAST(t1.`@timestamp` AS TIMESTAMP),
             CASE WHEN t7.`host` IS NOT NULL THEN t1.`host`
                  ELSE CASE WHEN t1.`host` IS NULL THEN NULL ELSE 'OTHER' END
             END
    UNION
    SELECT CAST(t13.`@timestamp` AS TIMESTAMP) AS `@timestamp`,
           t24.`$f0` AS `host`,
           0 AS `count`
    FROM (
        SELECT `@timestamp`
        FROM (
            SELECT SPAN(`@timestamp`, 1, 'm') AS `@timestamp`
            FROM `scott`.`events`
            GROUP BY `host`, SPAN(`@timestamp`, 1, 'm')
        ) AS t12
        GROUP BY `@timestamp`
    ) AS t13
    CROSS JOIN (
        SELECT CASE WHEN t22.`host` IS NOT NULL THEN t16.`host`
                    ELSE CASE WHEN t16.`host` IS NULL THEN NULL ELSE 'OTHER' END
               END AS `$f0`
        FROM (
            SELECT SPAN(`@timestamp`, 1, 'm') AS `@timestamp`,
                   `host`,
                   COUNT(*) AS `$f2_0`
            FROM `scott`.`events`
            GROUP BY `host`, SPAN(`@timestamp`, 1, 'm')
        ) AS t16
        LEFT JOIN (
            SELECT `host`, SUM(`$f2_0`) AS `grand_total`
            FROM (
                SELECT SPAN(`@timestamp`, 1, 'm') AS `@timestamp`,
                       `host`,
                       COUNT(*) AS `$f2_0`
                FROM `scott`.`events`
                GROUP BY `host`, SPAN(`@timestamp`, 1, 'm')
            ) AS t19
            WHERE `host` IS NOT NULL
            GROUP BY `host`
            ORDER BY 2 DESC NULLS FIRST
            LIMIT 3
        ) AS t22
        ON t16.`host` IS NOT DISTINCT FROM t22.`host`
        GROUP BY CASE WHEN t22.`host` IS NOT NULL THEN t16.`host`
                      ELSE CASE WHEN t16.`host` IS NULL THEN NULL ELSE 'OTHER' END
                 END
    ) AS t24
) AS t26
GROUP BY `@timestamp`, `host`
ORDER BY `@timestamp` NULLS LAST, `host` NULLS LAST;