Subresult reuse and result caching

Learn about subresult reuse in Firebolt in more detail in our blog post Caching & Reuse of Subresults across Queries. Workloads using repetitive query patterns can benefit tremendously from reuse and caching. Firebolt can cache subresults from various operators within the query plan, including hash tables from hash-joins.

How Firebolt caches subresults

Subresults are placed in an in-memory FireCache, which can use up to 20% of the available RAM. If a sub-plan is reused in a different query, Firebolt’s caching system detects it and can retrieve cached subresults, even if the rest of the query differs. Firebolt uses the following guidelines to determine which subresults to cache:

Firebolt’s optimizer may insert a MaybeCache operator above any node in the query plan, which may cache a subresult if it isn’t too large. The MaybeCache operator may later retrieve and reuse the cached subresult if the same subplan, with the same underlying data, is evaluated again. Currently, the optimizer places a MaybeCache operator in the following places:
- At the top of the query plan to cache the full result.
- At nodes where “sideways information passing” occurs, optimizing joins where the probe-side has an indexed key.
- When a common table expression (CTE) is marked as MATERIALIZED REUSABLE.
- Around the sort of a LIMIT ... OFFSET query when pagination caching is enabled.
The MaybeCache operator is versatile, and it can be placed anywhere in the plan.
Firebolt stores subresult hash-tables created for Join operators in the FireCache, provided they are not too large. These hash tables are costly to compute, so reusing them when similar consecutive queries run offers significant performance advantages.

Subresult reuse in Firebolt is fully transactional. When any changes occur in a base table (such as through an INSERT, UPDATE, or DELETE), outdated cache entries are no longer used.

Example

The following query, based on the TPC-H benchmark schema, calculates the total order price and the number of orders for each nation by joining the orders, customer, and nation tables:

SELECT n_name as nation, SUM(o_totalprice), COUNT(*)
  FROM orders, customer, nation
 WHERE o_custkey = c_custkey AND c_nationkey = n_nationkey
 GROUP BY ALL;

The simplified plan for this example query would look like the following:

                                                 ┌────────────────┐
┌──────────────────────┐                         │  FireCache     │
│ MaybeCache           │── subresult ──────────▶ │                │
└──────────┬───────────┘                         │ subresult      │
           │                                     │ hash table (1) │
┌──────────┴───────────┐                         │ hash table (2) │
│ Aggregate            │                         └────────────────┘
│ n_name, SUM, COUNT   │                              ▲    ▲
└──────────┬───────────┘                              │    │
           │                                          │    │
┌──────────┴───────────┐                              │    │
│ Join                 │── hash table ────────────────┘    │
│ o_custkey = c_custkey│                                   │
└──────┬───────────┬───┘                                   │
       │           │                                       │
┌──────┴─────┐ ┌───┴──────────────────┐                    │
│ Scan       │ │ Join                 │── hash table ──────┘
│ orders     │ │ c_nationkey = n_nkey │
└────────────┘ └───┬─────────────┬────┘
                   │             │
            ┌──────┴─────┐ ┌─────┴────────┐
            │ Scan       │ │ Scan         │
            │ customer   │ │ nation       │
            └────────────┘ └──────────────┘

In this example, a MaybeCache operator positioned at the top of the plan caches the subresult from the first run into the FireCache. Additionally, both Join operators store their respective hash tables in the cache. On a subsequent run of exactly the same query (over unchanged data), the MaybeCache operator fetches the subresult from the cache, allowing the entire evaluation to be skipped. As a result, query latency is reduced to mere milliseconds. In this example, it leads to a speed improvement of over 100x on a single node, medium engine running TPC-H with scale factor of 100. If the WHERE condition is changed to add ... AND o_orderdate >= '1998-01-01'::Date ..., the subresult cached by the MaybeCache operator cannot be used because the query plan below it has changed. However, the subplan below the upper Join remains unchanged, allowing the previously cached hash table to be reused in that JOIN operator. This eliminates the need to re-evaluate the subplan and rebuild the hash table. This results in more than 5x speed improvement on subsequent queries, even when each query has a different date restriction.

Recognizing subresult reuse in query telemetry

Firebolt transparently leverages subresult reuse. If you want to see whether subresult reuse helped to speed up your query, look at the EXPLAIN (ANALYZE) output. For every operator that can cache subresults (currently MaybeCache and Join), the Execution Metrics section shows a line like 2/2 nodes read from subresult cache, 0/2 nodes wrote to subresult cache. You can also see the metrics Nothing was executed for operators that were skipped because a higher level operator retrieved the subresult from the FireCache. For example, in the following EXPLAIN (ANALYZE) output, the Join operator retrieved the result from the cache, bypassing the need to construct the build side:

[0] [Projection] t.a, t.b
|   [Execution Metrics]: [...]
 \_[1] [MaybeCache]
   |   [Execution Metrics]: [...], 0/2 nodes read from subresult cache, 2/2 nodes wrote to subresult cache
    \_[2] [Filter] (t.a < t2.a)
      |   [Execution Metrics]: [...]
       \_[3] [Join] Mode: Inner [(t.a = t2.a)]
         |   [Execution Metrics]: [...], 2/2 nodes read from subresult cache, 0/2 nodes wrote to subresult cache
          \_[4] [TableFuncScan] t.a: $0.a, t.b: $0.b
            |   [Execution Metrics]: Nothing was executed
[...]

Observing cache state

You can view the current state of the cache using the information_schema.engine_caches view. Subresult reuse caches are available with the pool column being query. For example, the following query shows information (number of entries, size, etc.) about cached hash tables from the Join operator:

SELECT * FROM information_schema.engine_caches WHERE type = 'join_hash_table';

Limitations

Firebolt supports subresult caching for as many queries as possible. The following are specific limitations where subresult caching cannot be applied:

Result cache size – The result cache is limited to 1 MB per result to ensure that large results do not evict smaller cached subresults needed for other queries.
Nondeterministic functions – Queries that use nondeterministic functions such as RANDOM cannot cache subresults. If an operator in the query plan depends directly or indirectly on the output of a nondeterministic function, caching is disabled.
Mutable functions – Queries that use deterministic but mutable functions such as NOW() cannot cache subresults.
Order dependent results – For some queries, the order in which Firebolt computes a result can impact the result. Examples include floating point arithmetic (such as the SUM aggregation) or using LIMIT on a result that is not fully sorted. Note that whether a query is order dependent can also depend on whether you are using a single-node or multi-node engine. For more information on this topic, read the section on Consistent Subresults in Distributed Settings in our blog post.
External table scans – Results from external table scans cannot be cached. These tables rely on external data sources, which may change independently of Firebolt’s caching mechanism.
Non-equality joins – Cross joins and joins that use a join condition that’s not an equality (for example, joining on left_side.column1 < right_side.column2) cannot use subresult caching directly. The result cache can still be used for queries using such joins.

Disabling subresult reuse

Firebolt exposes system settings that allow turning off subresult caching at a per-query basis:

Setting enable_result_cache to FALSE ensures that full query results aren’t retrieved from cache, while still allowing for semantic cross-query subresult reuse.
Setting enable_subresult_cache to FALSE disables Firebolt’s entire subresult caching layer.

For most benchmarking scenarios, disable the result cache. This approach affects only the final result caching while preserving the benefits of cross-query subresult optimizations.

Pagination caching

Nightly Feature Pagination queries fetch one page at a time with a growing offset:

SELECT "PlayerID", "CurrentScore" FROM playstats
ORDER BY "CurrentScore" DESC LIMIT 25 OFFSET 50;

Each page request re-sorts the full input to return a handful of rows, and the result cache does not help across pages: every OFFSET value produces a different plan and therefore a different cache key. Setting the result_pagination_limit system setting to a value greater than zero (the default is 0, disabled) makes the planner rewrite eligible pagination queries to sort once per window of that many rows and serve all pages of the window from the FireCache. With result_pagination_limit = 1000, the plan for the page above becomes:

┌─────────────────────────────────────────┐
│ Sort                                    │  applies the page's offset
│ ORDER BY score DESC LIMIT 25 OFFSET 50  │  within the cached window
└───────────────────┬─────────────────────┘
                    │
┌───────────────────┴─────────────────────┐
│ MaybeCache                              │──▶ sorted window
└───────────────────┬─────────────────────┘    (result cache)
                    │
┌───────────────────┴─────────────────────┐
│ Sort                                    │  runs once per window
│ ORDER BY score DESC LIMIT 1000          │
└───────────────────┬─────────────────────┘
                    │
┌───────────────────┴─────────────────────┐
│ MaybeCache                              │──▶ raw sort input
└───────────────────┬─────────────────────┘    (pagination_input cache)
                    │
┌───────────────────┴─────────────────────┐
│ Scan playstats                          │
└─────────────────────────────────────────┘

Pages are grouped into aligned windows of result_pagination_limit rows: a query with OFFSET y belongs to the window starting at row floor(y / N) * N, where N is result_pagination_limit. Every page in the same window produces the same inner sort (same window start and limit), so all of them share one cache entry for the sorted window. The outer sort then applies the page’s offset relative to the window start. It sorts the cached window again, but that is at most result_pagination_limit rows, a negligible cost next to sorting the full input. Only the first request per window runs the inner sort; on a multi-node engine this is the distributed sort and merge, so the savings grow with data size and node count. The second MaybeCache below the inner sort caches the raw sort input. It pays off when the sort itself changes but the input does not, for example when the application flips the sort direction: the sorted window misses the cache, but the input is served from the pagination_input cache without re-scanning the table. Input entries are limited to 1 MB; larger inputs are not cached and are re-read when the sorted window misses the cache. The rewrite applies when all of the following hold:

The query is a SELECT with literal LIMIT and OFFSET values (with or without ORDER BY).
enable_result_cache is on (the default) and result_pagination_limit is greater than zero.
The page fits entirely within its window: the offset within the window plus the LIMIT must not exceed result_pagination_limit. Pages that straddle a window boundary keep the regular result cache.

Choose result_pagination_limit as a multiple of your page size so pages never straddle a boundary. Larger windows amortize the sort over more pages but produce larger cache entries; a window must fit within the result cache’s 1 MB entry limit to be cached. Pagination caching is transactional like all subresult reuse: after an INSERT, UPDATE, or DELETE on a base table, outdated windows are no longer used and the next page request re-sorts.

Observing pagination caching

EXPLAIN (ANALYZE) shows the rewritten plan and the cache traffic. On the second page request of a window, the MaybeCache above the inner sort reads the sorted window from the cache, and everything below it is skipped:

[0] [Projection] playstats.PlayerID, playstats.CurrentScore
 \_[1] [Sort] OrderBy: [playstats.CurrentScore Descending First] Offset: [50] Limit: [25]
   |   [Execution Metrics]: output cardinality = 25, [...]
    \_[2] [MaybeCache]
      |   [Execution Metrics]: output cardinality = 1000, [...], 1/1 nodes read from subresult cache, 0/1 nodes wrote to subresult cache
       \_[3] [Sort] OrderBy: [playstats.CurrentScore Descending First] Limit: [1000]
          \_[4] [MaybeCache]
             \_[5] [TableFuncScan] playstats.PlayerID: $0.PlayerID, playstats.CurrentScore: $0.CurrentScore
               |   $0 = read_tablets(table_name => playstats, tablet)
               |   [Execution Metrics]: Nothing was executed

The raw-input entries appear as the pagination_input cache type in the information_schema.engine_caches view; the sorted windows are part of the result cache.

Configure cache sizes

Firebolt automatically manages a query cache for different subresults to improve the performance of subsequent queries by reusing already computed parts of shared query plans. By default, engines use up to 20% of their main memory for this cache. You can configure the size of the query cache at the engine level using the QUERY_CACHE_MEMORY_FRACTION parameter in CREATE ENGINE or ALTER ENGINE commands. For example, to allocate 30% of engine memory to the query cache:

-- Configure the query cache to use up to 30% of the engine's memory
ALTER ENGINE my_engine SET QUERY_CACHE_MEMORY_FRACTION = 0.3;

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

How Firebolt caches subresults

Example

Recognizing subresult reuse in query telemetry

Observing cache state

Limitations

Disabling subresult reuse

Configure cache sizes

​How Firebolt caches subresults

​Example

​Recognizing subresult reuse in query telemetry

​Observing cache state

​Limitations

​Disabling subresult reuse

​Pagination caching

​Observing pagination caching

​Configure cache sizes

How Firebolt caches subresults

Example

Recognizing subresult reuse in query telemetry

Observing cache state

Limitations

Disabling subresult reuse

Pagination caching

Observing pagination caching

Configure cache sizes