feat: cache hist query with non ts col order by #8731

Loaki07 · 2025-10-06T05:45:18Z

Cache Non-Timestamp Order By Histogram Implementation

Add is_histogram_non_ts_order flag to CacheQueryRequest struct
Calculate is_histogram_non_ts_order in check_cache() based on histogram + non-timestamp ORDER BY
Pass is_histogram_non_ts_order through all cache write paths (main, websocket, cluster)
Scan all hits for min/max timestamps when writing cache files for histogram non-ts ORDER BY queries
Fix get_allowed_up_to() to scan all hits for max timestamp (discard_ts calculation)
Fix response_start_time/response_end_time calculation in cache retrieval to scan all hits
Update retain filter boundary conditions (strict to inclusive where needed)
Preserve ORDER BY metadata in cached responses for correct result sorting
Refactor naming from is_non_ts_histogram to is_histogram_non_ts_order for clarity

Test Queries for Histogram with Non-Timestamp Column ORDER BY Cache

New Feature Test Queries (Histogram Non-TS Order)

1. Basic Histogram with COUNT ORDER BY DESC

SELECT histogram(_timestamp) AS x_axis_1, count(_timestamp) AS y_axis_1 
FROM "oly" 
GROUP BY x_axis_1 
ORDER BY y_axis_1 DESC

Expected: Results cached, ordered by count descending, correct count returned

2. Basic Histogram with COUNT ORDER BY ASC

SELECT histogram(_timestamp) AS x_axis_1, count(_timestamp) AS y_axis_1 
FROM "oly" 
GROUP BY x_axis_1 
ORDER BY y_axis_1 ASC

Expected: Results cached, ordered by count ascending

3. Histogram with SUM ORDER BY

SELECT histogram(_timestamp) AS time_bucket, SUM(bytes) AS total_bytes 
FROM "logs" 
GROUP BY time_bucket 
ORDER BY total_bytes DESC

Expected: Results cached, ordered by sum of bytes

4. Histogram with AVG ORDER BY

SELECT histogram(_timestamp) AS time_bucket, AVG(response_time) AS avg_response 
FROM "metrics" 
GROUP BY time_bucket 
ORDER BY avg_response DESC

Expected: Results cached, ordered by average

5. Histogram with Multiple Aggregations, Non-TS ORDER BY

SELECT histogram(_timestamp) AS time_bucket, 
       count(*) AS event_count,
       SUM(bytes) AS total_bytes
FROM "logs" 
GROUP BY time_bucket 
ORDER BY total_bytes DESC

Expected: Results cached, ordered by total_bytes

6. Histogram with MAX ORDER BY

SELECT histogram(_timestamp) AS time_bucket, MAX(value) AS max_val 
FROM "metrics" 
GROUP BY time_bucket 
ORDER BY max_val DESC

Expected: Results cached, ordered by max value

7. Multiple Histogram Columns with Non-TS ORDER BY

SELECT histogram(_timestamp) AS time_bucket,
       status_code,
       count(*) AS request_count
FROM "access_logs"
GROUP BY time_bucket, status_code
ORDER BY request_count DESC

Expected: Results cached with correct grouping and ordering

Regression Test Queries (Existing Functionality)

8. Standard Histogram with Timestamp ORDER BY (Default ASC)

SELECT histogram(_timestamp) AS x_axis_1, count(_timestamp) AS y_axis_1 
FROM "oly" 
GROUP BY x_axis_1 
ORDER BY x_axis_1 ASC

Expected: Results cached, time-ordered ascending, should use fast first/last logic

9. Standard Histogram with Timestamp ORDER BY DESC

SELECT histogram(_timestamp) AS x_axis_1, count(_timestamp) AS y_axis_1 
FROM "oly" 
GROUP BY x_axis_1 
ORDER BY x_axis_1 DESC

Expected: Results cached, time-ordered descending, should use fast first/last logic

10. Histogram with NO ORDER BY (Default Time Order)

SELECT histogram(_timestamp) AS x_axis_1, count(_timestamp) AS y_axis_1 
FROM "oly" 
GROUP BY x_axis_1

Expected: Results cached, default time ordering, should use fast first/last logic

11. Histogram with Explicit _timestamp ORDER BY

SELECT histogram(_timestamp) AS time_bucket, count(*) AS cnt 
FROM "logs" 
GROUP BY time_bucket 
ORDER BY _timestamp DESC

Expected: Results cached, time-ordered, should use fast first/last logic

12. Non-Histogram Aggregate Query

SELECT count(*) AS total_count, SUM(bytes) AS total_bytes 
FROM "logs" 
WHERE _timestamp BETWEEN '2025-10-01' AND '2025-10-06'

Expected: Results cached normally (not affected by changes)

13. Raw Log Query (No Aggregation)

SELECT * FROM "logs" 
WHERE _timestamp BETWEEN '2025-10-01' AND '2025-10-06'
ORDER BY _timestamp DESC
LIMIT 1000

Expected: Results cached normally, time-ordered

14. Non-Histogram with Non-Timestamp ORDER BY

SELECT method, count(*) AS cnt 
FROM "access_logs" 
GROUP BY method 
ORDER BY cnt DESC

Expected: Should NOT use histogram non-ts order logic (no histogram)

Edge Case Test Queries

15. Histogram with Mixed ORDER BY (Timestamp First, Then Count)

SELECT histogram(_timestamp) AS time_bucket, count(*) AS cnt 
FROM "logs" 
GROUP BY time_bucket 
ORDER BY time_bucket ASC, cnt DESC

Expected: Should be treated as time-ordered (first ORDER BY is timestamp)

16. Histogram with Empty Result Set

SELECT histogram(_timestamp) AS time_bucket, count(*) AS cnt 
FROM "logs" 
WHERE log_level = 'NONEXISTENT'
GROUP BY time_bucket 
ORDER BY cnt DESC

Expected: Cache should handle empty results gracefully

17. Very Large Time Range Histogram

SELECT histogram(_timestamp) AS time_bucket, count(*) AS cnt 
FROM "logs" 
WHERE _timestamp BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY time_bucket 
ORDER BY cnt DESC

Expected: Results cached with correct time range metadata

18. Histogram with Recent Data (Within Cache Delay)

SELECT histogram(_timestamp) AS time_bucket, count(*) AS cnt 
FROM "logs" 
WHERE _timestamp >= now() - INTERVAL '10 minutes'
GROUP BY time_bucket 
ORDER BY cnt DESC

Expected: Recent buckets (last 5 mins) should not be cached, older ones should be

19. Histogram with Multiple Non-Timestamp ORDER BY Columns

SELECT histogram(_timestamp) AS time_bucket, 
       status_code,
       count(*) AS cnt,
       SUM(bytes) AS total_bytes
FROM "access_logs"
GROUP BY time_bucket, status_code
ORDER BY cnt DESC, total_bytes DESC

Expected: Results cached, ordered by count then bytes

Performance Test Queries

20. Large Dataset Histogram (Test Scan Performance)

SELECT histogram(_timestamp) AS time_bucket, count(*) AS cnt 
FROM "large_logs_table" 
WHERE _timestamp BETWEEN '2025-01-01' AND '2025-10-31'
GROUP BY time_bucket 
ORDER BY cnt DESC

Expected: Verify scanning all hits doesn't cause significant overhead

21. High Cardinality Histogram (Many Buckets)

SELECT histogram(_timestamp, '1m') AS time_bucket, count(*) AS cnt 
FROM "metrics" 
WHERE _timestamp BETWEEN '2025-10-01' AND '2025-10-06'
GROUP BY time_bucket 
ORDER BY cnt DESC

Expected: Handle many histogram buckets efficiently (1-minute granularity)

Cache Behavior Verification Tests

22. First Query (Cold Cache)

SELECT histogram(_timestamp) AS x_axis_1, count(*) AS y_axis_1 
FROM "oly" 
GROUP BY x_axis_1 
ORDER BY y_axis_1 DESC

Verify:

result_cache_ratio: 0 (no cache hit)
Cache file created with correct timestamps
scan_records > 0 (fresh query executed)

23. Second Query (Warm Cache)

-- Run same query as #22 again
SELECT histogram(_timestamp) AS x_axis_1, count(*) AS y_axis_1 
FROM "oly" 
GROUP BY x_axis_1 
ORDER BY y_axis_1 DESC

Verify:

result_cache_ratio: 100 (or close to 100)
Cache hit successful
scan_records minimal (only delta if any)
Result count matches first query
Result ordering matches first query

24. Query with Overlapping Time Range

SELECT histogram(_timestamp) AS x_axis_1, count(*) AS y_axis_1 
FROM "oly" 
WHERE _timestamp BETWEEN '2025-10-01' AND '2025-10-05'  -- Subset of cached range
GROUP BY x_axis_1 
ORDER BY y_axis_1 DESC

Verify:

Cache hit for overlapping period
Correct subset of results returned
No duplicate buckets

25. Query with Extended Time Range

SELECT histogram(_timestamp) AS x_axis_1, count(*) AS y_axis_1 
FROM "oly" 
WHERE _timestamp BETWEEN '2025-10-01' AND '2025-10-10'  -- Extended beyond cache
GROUP BY x_axis_1 
ORDER BY y_axis_1 DESC

Verify:

Partial cache hit
Delta query for new time range
Results properly merged without duplicates

greptile-apps

Greptile Overview

Summary

This PR introduces a targeted enhancement to the caching mechanism for histogram queries in OpenObserve's search service. The change addresses a limitation where histogram queries with non-timestamp `ORDER BY` clauses (such as `ORDER BY count DESC`) were being excluded from caching.

The modification works by detecting histogram queries through sql.histogram_interval.is_some() and creating an exception in the existing cache skip logic. Previously, any query with an ORDER BY clause on a non-timestamp column would skip caching entirely. Now, histogram queries are exempt from this restriction because they maintain inherent temporal characteristics that make them suitable for time-based caching strategies.

The implementation includes sophisticated sort direction handling for histogram queries. When a histogram query uses non-timestamp ordering, the system defaults to ascending order (is_descending = false) since histograms are typically displayed in chronological order for better user experience and visualization clarity.

This change integrates seamlessly with the existing cache infrastructure in cacher.rs, which already handles cached query responses, delta calculations, and result filtering based on timestamps. The enhancement maintains backward compatibility while extending caching support to an important class of queries that benefit significantly from caching due to their computational complexity.

Important Files Changed

Changed Files

Filename	Score	Overview
src/service/search/cache/cacher.rs	4/5	Enhanced cache logic to allow histogram queries with non-timestamp ORDER BY clauses to be cached, with special handling for sort direction

Confidence score: 4/5

This PR is safe to merge with minimal risk as it adds targeted functionality without breaking existing behavior
Score reflects well-structured logic changes that maintain backward compatibility while addressing a specific caching limitation
Pay close attention to the cache skip condition logic to ensure histogram queries are properly identified and handled

Sequence Diagram

sequenceDiagram
    participant User
    participant SearchService as "Search Service"
    participant CacheChecker as "Cache Checker"
    participant SqlParser as "SQL Parser"
    participant CacheStorage as "Cache Storage"
    participant HistogramHandler as "Histogram Handler"
    participant DeltaCalculator as "Delta Calculator"
    
    User->>SearchService: "Execute search query"
    SearchService->>CacheChecker: "check_cache(trace_id, org_id, stream_type, req, origin_sql, file_path, is_aggregate, should_exec_query, is_streaming)"
    
    CacheChecker->>SqlParser: "Sql::new(query, org_id, stream_type, req.search_type)"
    SqlParser-->>CacheChecker: "Return parsed SQL object"
    
    CacheChecker->>CacheChecker: "get_ts_col_order_by(sql, TIMESTAMP_COL_NAME, is_aggregate)"
    CacheChecker->>CacheChecker: "Check if histogram query"
    
    alt Histogram query detected
        CacheChecker->>HistogramHandler: "handle_histogram(origin_sql, q_time_range, interval)"
        HistogramHandler-->>CacheChecker: "Modified SQL with histogram function"
        CacheChecker->>CacheChecker: "Update file_path with histogram interval"
    end
    
    CacheChecker->>CacheChecker: "Generate query_key from file_path"
    CacheChecker->>CacheChecker: "Determine sort order (is_descending)"
    
    alt Multi-result cache enabled
        CacheChecker->>CacheStorage: "get_cached_results(query_key, file_path, trace_id, cache_request, is_streaming)"
        CacheStorage-->>CacheChecker: "Return cached_responses array"
        CacheChecker->>CacheChecker: "invalidate_cached_response_by_stream_min_ts(file_path, cached_responses)"
        CacheChecker->>DeltaCalculator: "calculate_deltas_multi(cached_responses, start_time, end_time, discard_interval)"
        DeltaCalculator-->>CacheChecker: "Return (deltas, updated_start_time, cache_duration)"
    else Single cache mode
        CacheChecker->>CacheStorage: "get_cached_results(query_key, file_path, trace_id, cache_request)"
        CacheStorage-->>CacheChecker: "Return single cached response"
        CacheChecker->>DeltaCalculator: "calculate_deltas_v1(result_meta, start_time, end_time, deltas)"
        DeltaCalculator-->>CacheChecker: "Return delta calculations"
    end
    
    alt Cache hit with complete data
        CacheChecker->>CacheChecker: "Set should_exec_query = false"
    else Partial cache hit
        CacheChecker->>CacheChecker: "Prepare deltas for missing data"
    end
    
    CacheChecker-->>SearchService: "Return MultiCachedQueryResponse"
    SearchService-->>User: "Return search results"

_{1 file reviewed, no comments}

_{Edit Code Review Agent Settings | Greptile}

name

…l_order_by

ref: #8731 --------- Signed-off-by: loaki07 <[email protected]>

### **User description** ref: #8731 ___ ### **PR Type** Enhancement, Bug fix ___ ### **Description** - Cache support for histogram with non-TS ORDER BY - Scan all hits to compute time range - Preserve ORDER BY metadata in cache writes - Refactor utils for ORDER BY timestamp checks ___ ### Diagram Walkthrough ```mermaid flowchart LR SQL["SQL parse (histogram, ORDER BY)"] Check["check_cache: detect non-TS histogram order"] Req["CacheQueryRequest + is_histogram_non_ts_order"] Utils["result_utils: is_timestamp_field, has_non_timestamp_ordering, extract_timestamp_range"] Write["write_results: scan hits for min/max when needed"] WS["streaming cache: propagate flag"] Mod["search mod: cache write integration"] SQL -- "order_by, ts_column" --> Check Check -- "set flag" --> Req Check -- "use timestamp detection" --> Utils Req -- "persist flag" --> Write Mod -- "compute is_histogram_non_ts_order" --> Write WS -- "compute and pass flag" --> Write Write -- "min/max via Utils" --> Utils ``` <details> <summary><h3> File Walkthrough</h3></summary> <table><thead><tr><th></th><th align="left">Relevant files</th></tr></thead><tbody><tr><td><strong>Enhancement</strong></td><td><table> <tr> <td> <details> <summary><strong>search.rs</strong><dd><code>Add non-TS histogram order flag to request</code>                              </dd></summary> <hr> src/common/meta/search.rs <ul><li>Add <code>is_histogram_non_ts_order</code> to <code>CacheQueryRequest</code><br> <li> Document behavior requiring full-hit scan</ul> </details> </td> <td><a href="https://github.com/openobserve/openobserve/pull/8837/files#diff-a36a6683e327641569d8098ee0d4f13e894e1b9d51230dc3569abe42e87ba310">+5/-0</a>      </td> </tr> <tr> <td> <details> <summary><strong>cacher.rs</strong><dd><code>Detect and flag non-TS ordered histograms</code>                                </dd></summary> <hr> src/service/search/cache/cacher.rs <ul><li>Import new utils for ORDER BY checks<br> <li> Allow histogram caching without TS ORDER BY<br> <li> Determine <code>is_histogram_non_ts_order</code><br> <li> Propagate flag in cache write paths</ul> </details> </td> <td><a href="https://github.com/openobserve/openobserve/pull/8837/files#diff-8c387b22b2c0a282600ee41ca702b3db60f5a284d507c0feb7418608ff062c6c">+33/-3</a>    </td> </tr> <tr> <td> <details> <summary><strong>mod.rs</strong><dd><code>Compute and pass histogram order flag to writer</code>                    </dd></summary> <hr> src/service/search/cache/mod.rs <ul><li>Import <code>extract_timestamp_range</code><br> <li> Compute <code>is_histogram_non_ts_order</code> on write<br> <li> Pass flag to <code>write_results</code></ul> </details> </td> <td><a href="https://github.com/openobserve/openobserve/pull/8837/files#diff-f07bfacc0501b9c234e64b16c1dd8eb0ae8fcbe231f90c81fed72bcc94946f74">+18/-5</a>    </td> </tr> <tr> <td> <details> <summary><strong>result_utils.rs</strong><dd><code>Utilities for timestamp/order checks and range extraction</code></dd></summary> <hr> src/service/search/cache/result_utils.rs <ul><li>Add <code>is_timestamp_field</code> helper<br> <li> Add <code>has_non_timestamp_ordering</code><br> <li> Add <code>extract_timestamp_range</code> with scan fallback</ul> </details> </td> <td><a href="https://github.com/openobserve/openobserve/pull/8837/files#diff-fb601e2f37657029b6a4dcb82d16aeff5916e13660d284e7fb00db6002dc0f73">+59/-0</a>    </td> </tr> <tr> <td> <details> <summary><strong>cache.rs</strong><dd><code>Streaming cache supports non-TS histogram ordering</code>              </dd></summary> <hr> src/service/search/streaming/cache.rs <ul><li>Detect non-TS histogram order in streaming path<br> <li> Pass flag to cache <code>write_results</code></ul> </details> </td> <td><a href="https://github.com/openobserve/openobserve/pull/8837/files#diff-55769b7cb67b550daf45363c6d7d569b39d4a6235f6a68c091beb40f16b6511e">+10/-0</a>    </td> </tr> </table></td></tr><tr><td><strong>Miscellaneous</strong></td><td><table> <tr> <td> <details> <summary><strong>mod.rs</strong><dd><code>Hardcode partition timing and count</code>                                            </dd></summary> <hr> src/service/search/mod.rs <ul><li>Force <code>total_secs = 4</code> (debug change)<br> <li> Force <code>part_num = 10</code> (debug change)</ul> </details> </td> <td><a href="https://github.com/openobserve/openobserve/pull/8837/files#diff-c3b85ea662a885fbd39797968928b291cb59a58f53a03f7b7dfd7ba8b6a7985a">+2/-0</a>      </td> </tr> </table></td></tr></tr></tbody></table> </details> ___ --------- Signed-off-by: loaki07 <[email protected]>

feat: cache hist query with non ts col order by

9cc3212

Loaki07 requested a review from oasisk October 6, 2025 05:45

github-actions bot added the ✏️ Feature label Oct 6, 2025

greptile-apps bot reviewed Oct 6, 2025

View reviewed changes

Loaki07 added 6 commits October 7, 2025 12:48

fix: for non-ts hist col get the smallest and largest ts for cache file

6762632

name

fix: re-design cache write and read for non-ts hist queries

ed86f4c

refactor: is_histogram_non_ts_order

9c18548

refactor: check for non-ts hist col

fd326a9

Merge branch 'branch-v0.14.6-rc9' into feat/cache_hist_with_non_ts_co…

f0b450e

…l_order_by

Merge branch 'branch-v0.14.6-rc9' into feat/cache_hist_with_non_ts_co…

7e00272

…l_order_by

Loaki07 mentioned this pull request Oct 7, 2025

feat: cache hist query with non ts col order by#8731 #8758

Merged

Loaki07 requested review from Subhra264 and uddhavdave October 7, 2025 14:14

Subhra264 approved these changes Oct 7, 2025

View reviewed changes

Loaki07 merged commit 54cf9fe into branch-v0.14.6-rc9 Oct 7, 2025
46 of 50 checks passed

Loaki07 deleted the feat/cache_hist_with_non_ts_col_order_by branch October 7, 2025 14:53

Loaki07 added a commit that referenced this pull request Oct 7, 2025

feat: cache hist query with non ts col order by#8731 (#8758)

cc72425

ref: #8731 --------- Signed-off-by: loaki07 <[email protected]>

Loaki07 mentioned this pull request Oct 17, 2025

feat: cache hist query with non ts col order by #8837

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

feat: cache hist query with non ts col order by #8731

feat: cache hist query with non ts col order by #8731

Uh oh!

Loaki07 commented Oct 6, 2025 •

edited

Loading

Uh oh!

greptile-apps bot left a comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

feat: cache hist query with non ts col order by #8731

feat: cache hist query with non ts col order by #8731

Uh oh!

Conversation

Loaki07 commented Oct 6, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Cache Non-Timestamp Order By Histogram Implementation

Test Queries for Histogram with Non-Timestamp Column ORDER BY Cache

New Feature Test Queries (Histogram Non-TS Order)

1. Basic Histogram with COUNT ORDER BY DESC

2. Basic Histogram with COUNT ORDER BY ASC

3. Histogram with SUM ORDER BY

4. Histogram with AVG ORDER BY

5. Histogram with Multiple Aggregations, Non-TS ORDER BY

6. Histogram with MAX ORDER BY

7. Multiple Histogram Columns with Non-TS ORDER BY

Regression Test Queries (Existing Functionality)

8. Standard Histogram with Timestamp ORDER BY (Default ASC)

9. Standard Histogram with Timestamp ORDER BY DESC

10. Histogram with NO ORDER BY (Default Time Order)

11. Histogram with Explicit _timestamp ORDER BY

12. Non-Histogram Aggregate Query

13. Raw Log Query (No Aggregation)

14. Non-Histogram with Non-Timestamp ORDER BY

Edge Case Test Queries

15. Histogram with Mixed ORDER BY (Timestamp First, Then Count)

16. Histogram with Empty Result Set

17. Very Large Time Range Histogram

18. Histogram with Recent Data (Within Cache Delay)

19. Histogram with Multiple Non-Timestamp ORDER BY Columns

Performance Test Queries

20. Large Dataset Histogram (Test Scan Performance)

21. High Cardinality Histogram (Many Buckets)

Cache Behavior Verification Tests

22. First Query (Cold Cache)

23. Second Query (Warm Cache)

24. Query with Overlapping Time Range

25. Query with Extended Time Range

Uh oh!

greptile-apps bot left a comment

Choose a reason for hiding this comment

Greptile Overview

Summary

Important Files Changed

Confidence score: 4/5

Sequence Diagram

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Loaki07 commented Oct 6, 2025 •

edited

Loading