Query result caching for sub-queries by nbarannik · Pull Request #76252 · ClickHouse/ClickHouse

nbarannik · 2025-02-17T09:46:48Z

Resolves #51656

Settings use_query_cache = true, query_cache_for_subqueries = true now enable caching of subquery results in the query cache.

Example:

SELECT some_expensive_calculation(column_1, column_2)
FROM table
SETTINGS use_query_cache = true, query_cache_for_subqueries = true;

-- returns cached results
SELECT column
FROM (
   SELECT some_expensive_calculation(column_1, column_2)
   FROM table
)
SETTINGS use_query_cache = true, query_cache_for_subqueries = true;

-- returns cached results
SELECT column
FROM table
WHERE column IN (
   SELECT some_expensive_calculation(column_1, column_2)
   FROM table
)
SETTINGS use_query_cache = true, query_cache_for_subqueries = true;

The field is_subquery was added to the system.query_cache table. It is set to true for all records related to sub-queries (including the main query that was cached as a sub-query for future use)

Changelog category (leave one):

New Feature

Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):

Add subqueries caching in query cache

Documentation entry for user-facing changes

Documentation is written (mandatory for new features)

…o query_cache_for_subqueries

CLAassistant · 2025-02-17T09:46:57Z

All committers have signed the CLA.

clickhouse-gh · 2025-02-17T10:53:07Z

Workflow [PR], commit [86d636e]

…_subqueries

…display

…ckHouse into query_cache_for_subqueries

…stem.query_log

clickhouse-gh · 2025-04-15T13:16:16Z

Dear @rschu1ze, this PR hasn't been updated for a while. You will be unassigned. Will you continue working on it? If so, please feel free to reassign yourself.

fastio · 2025-04-25T11:38:34Z

@rschu1ze Hi, are you still working on this PR? I really need this PR as it can improve query performance.

rschu1ze · 2025-04-25T12:39:47Z

@fastio Not at the moment but it is still on my radar.

alexey-milovidov · 2025-05-12T20:52:13Z

I also need this PR, it is an amazing feature!

movy · 2025-07-14T06:11:29Z

Any progress on this one? It looks very promising!

alexey-milovidov · 2025-07-14T16:09:49Z

+1, very eager to see it merged! It is in the ClickHouse Roadmap 2025.

balajisraghavan · 2025-07-31T13:01:26Z

+1 a very useful feature. Looking forward to using this in production

alexey-milovidov · 2025-11-09T20:56:00Z

It appears that this does not offer flexibility for selectively enabling caching for one or another subquery.

Let's do it this way:

Make sure that the use_query_cache setting does not automatically propagate to subqueries by default. So if it is set outside, then in the subqueries, it is considered unset by default.
Users can enable the setting use_query_cache explicitly inside subqueries to turn on/off the caching.
The new setting query_cache_for_subqueries will change this default and enable the propagation of use_query_cache into all subqueries by default.

kiran-4444 · 2025-11-12T22:24:49Z

Hey @rschu1ze can I continue this if you're busy? I'd like to complete this feature implementation.

alexey-milovidov · 2025-11-13T01:08:08Z

@kiran-4444, yes, this will be amazing! You can create a new pull request that contains all the commits from this one and adds your commits. Also, take the comment above about the logic of this feature.

Implement CTO feedback on PR ClickHouse#76252: use_query_cache on the outer query no longer auto-propagates to subqueries. Instead: 1. No auto-propagation: use_query_cache on outer query does NOT propagate to subqueries by default 2. Explicit per-subquery opt-in: SETTINGS use_query_cache = true on a specific subquery enables caching for that subquery only 3. Bulk propagation: query_cache_for_subqueries = true enables propagation of use_query_cache into ALL subqueries (existing behavior) Changes: - Planner.cpp: Add shouldUseQueryCacheForSubquery() helper implementing the three-rule decision logic for cache read/write - ExecuteScalarSubqueriesVisitor.cpp: Add shouldCacheScalarSubquery() checking explicit subquery SETTINGS before falling back to propagation - PreparedSets.cpp: Gate IN-subquery cache finalization on query_cache_for_subqueries setting - New test: 03381_query_result_cache_subquery_settings_propagation covering all 4 scenarios (no propagation, explicit opt-in, bulk propagation, explicit opt-out override) Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

Implement CTO feedback on PR ClickHouse#76252: `use_query_cache` on the outer query no longer auto-propagates to subqueries. Instead: 1. No auto-propagation: `use_query_cache` on outer query does NOT propagate to subqueries by default 2. Explicit per-subquery opt-in: `SETTINGS use_query_cache = true` on a specific subquery enables caching for that subquery only 3. Bulk propagation: `query_cache_for_subqueries = true` enables propagation of `use_query_cache` into ALL subqueries Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

vvo · 2026-03-17T17:09:14Z

@alexey-milovidov I too would love this feature (example: metadata table in a logs UI to fetch a list of projects: can be cached as a subquery like where projects IN ...).

I fired #99804 which is based on your comment in #76252 (comment), let me know what you think 🙏

Implement CTO feedback on PR ClickHouse#76252: `use_query_cache` on the outer query no longer auto-propagates to subqueries. Instead: 1. No auto-propagation: `use_query_cache` on outer query does NOT propagate to subqueries by default 2. Explicit per-subquery opt-in: `SETTINGS use_query_cache = true` on a specific subquery enables caching for that subquery only 3. Bulk propagation: `query_cache_for_subqueries = true` enables propagation of `use_query_cache` into ALL subqueries Key implementation details: - Gate subquery cache on `select_query_options.is_subquery` to prevent outer queries from going through the subquery cache path - Set context `canUseQueryResultCache` flag for explicit per-subquery opt-in so `checkCanWriteQueryResultCache` works - Always call `finalizeWriteInQueryResultCache` to support subqueries that explicitly opt in independently of the outer query Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

nbarannik added 21 commits January 14, 2025 11:19

Fix typos

f722c4f

Merge branch 'master' of https://github.com/ClickHouse/ClickHouse

34c8bb1

Merge branch 'master' of https://github.com/ClickHouse/ClickHouse

45fb3f4

Add steps for query caching

40bbd47

Fix subquery cache for scalar subqueries

f13816d

Fix subquery cache in WHERE

fc60f8e

Cache initial ast instead of query tree ast

ee2818c

Fix different changed settings for queries and subqueries

0b4bfd9

Use initial query cache usage for main query

067c8fc

Fix

a93d31a

Remove table aliases from query tree in query cache

c9b0064

Fix multiple finalizes of query cache writer

f11e7f5

Merge branch 'master' of https://github.com/ClickHouse/ClickHouse int…

b92bafc

…o query_cache_for_subqueries

Small refactor after merge

70280c7

Remove debug output

dc83f24

Refactor checks before write to query cache initialization

de6291b

Clean up

c63cf7c

Remove debug output

d403a66

Remove debug output

3f0b240

Clean up

d265dc1

Merge branch 'master' of https://github.com/ClickHouse/ClickHouse int…

9a2823d

…o query_cache_for_subqueries

alexey-milovidov added the can be tested Allows running workflows for external contributors label Feb 17, 2025

clickhouse-gh bot added the pr-feature Pull request with new product feature label Feb 17, 2025

nbarannik added 3 commits February 17, 2025 18:08

Add query_cache_for_subqueries setting

bd41f88

Remove settings clause if its empty after query cache settings remove

06a6ccf

Fix style

ecb8fb4

rschu1ze self-assigned this Feb 17, 2025

Merge remote-tracking branch 'ClickHouse/master' into query_cache_for…

b70452b

…_subqueries

rschu1ze changed the title ~~Query cache for subqueries~~ Query result caching for sub-queries Feb 18, 2025

nbarannik added 9 commits February 21, 2025 13:24

Add is_subquery field to system.query_cache, and fix subquery string …

bb55565

…display

Merge branch 'query_cache_for_subqueries' of github.com:nbarannik/Cli…

294ab70

…ckHouse into query_cache_for_subqueries

QueryCache -> QueryResultCache

cd558f7

QueryCache -> QueryResultCache

7a990f6

Remove __table alias from table functions in QC

1618c22

Add stateless tests

4847d2b

Fix style

c1216a2

Add current_database = currentDatabase() condition to selects from sy…

3e16a90

…stem.query_log

Change query_result_cache_table_subqueries test

edb2b70

clickhouse-gh bot unassigned rschu1ze Apr 15, 2025

Merge branch 'master' into query_cache_for_subqueries

86d636e

This was referenced Mar 17, 2026

Implement finer-grained subquery cache propagation control nbarannik/ClickHouse#1

Closed

Implement finer-grained subquery cache propagation control (on top of #76252) #99756

Closed

vvo mentioned this pull request Mar 17, 2026

Implement finer-grained subquery cache propagation control #99757

Closed

1 task

vvo mentioned this pull request Mar 17, 2026

Finer-grained subquery cache propagation control #99804

Open

1 task

Conversation

nbarannik commented Feb 17, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Changelog category (leave one):

Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):

Documentation entry for user-facing changes

Uh oh!

CLAassistant commented Feb 17, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

clickhouse-gh bot commented Feb 17, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

clickhouse-gh bot commented Apr 15, 2025

Uh oh!

fastio commented Apr 25, 2025

Uh oh!

rschu1ze commented Apr 25, 2025

Uh oh!

alexey-milovidov commented May 12, 2025

Uh oh!

movy commented Jul 14, 2025

Uh oh!

alexey-milovidov commented Jul 14, 2025

Uh oh!

balajisraghavan commented Jul 31, 2025

Uh oh!

alexey-milovidov commented Nov 9, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

kiran-4444 commented Nov 12, 2025

Uh oh!

alexey-milovidov commented Nov 13, 2025

Uh oh!

vvo commented Mar 17, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

9 participants

nbarannik commented Feb 17, 2025 •

edited

Loading

CLAassistant commented Feb 17, 2025 •

edited

Loading

clickhouse-gh bot commented Feb 17, 2025 •

edited

Loading

alexey-milovidov commented Nov 9, 2025 •

edited

Loading

vvo commented Mar 17, 2026 •

edited

Loading