> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.
## Submitting Feedback
If you encounter incorrect, outdated, or confusing documentation on this page, submit feedback:
POST https://docs.firebolt.io/feedback
```json
{
"path": "/overview/queries/understand-query-performance-subresult",
"feedback": "Description of the issue"
}
```
Only submit feedback when you have something specific and actionable to report.
> How to understand subresult reuse and result caching.
# Subresult reuse and result caching
*Learn about subresult reuse in Firebolt in more detail in our blog post [Caching & Reuse of Subresults across Queries](https://www.firebolt.io/blog/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.
**Topics**
* [How firebolt caches subresults](#how-firebolt-caches-subresults) – An overview about how and when Firebolt caches subresults.
* [Example](#example) – How Firebolt's subresult cashing and hash table reuse improve query performance for repetitive and partially similar queries.
* [Recognizing subresult reuse in query telemetry](#recognizing-subresult-reuse-in-query-telemetry) – How to view results from `EXPLAIN (ANALYZE)` to check for subresult reuse.
* [Limitations](#limitations) – Understand the situations where subresults cannot be cached or used.
* [Disabling subresult reuse](#disabling-subresult-reuse) – How to turn off subresult caching for benchmarking or other tests.
* [Configure cache sizes](#configure-cache-sizes) – How to change the size (i.e., used memory) of different cache pools.
## 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`](/reference-sql/commands/queries/select#reusable-common-table-expressions).
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](https://www.tpc.org/tpch/) schema, calculates the total order price and the number of orders for each nation by joining the `orders`, `customer`, and `nation` tables:
```SQL theme={"theme":{"light":"github-light","dark":"github-dark"}}
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:
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)](/reference-sql/commands/queries/explain) 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:
```text {4, 8} theme={"theme":{"light":"github-light","dark":"github-dark"}}
[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](/reference-sql/information-schema/engine-caches).
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:
```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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 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](https://www.firebolt.io/blog/caching-reuse-of-subresults-across-queries#consistent-subresults-matter-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.colum1 < 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](/reference-sql/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.
## 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`](/reference-sql/commands/engines/create-engine) or [`ALTER ENGINE`](/reference-sql/commands/engines/alter-engine) commands. For example, to allocate 30% of engine memory to the query cache:
```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
-- 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;
```