Analyzer: row policy may disable optimize_move_to_prewhere #83748
Description
Company or project name
No response
Describe the situation
After upgrading from ClickHouse version 23.8 to 24.8, we observed a performance regression for queries on tables that have row-level security policies (i.e., row policies) attached. Specifically, the automatic optimization that moves filter conditions from WHERE to PREWHERE no longer functions in some cases. This results in significant query slowdown, especially on large datasets, as the filtering is delayed and more data is read unnecessarily.
Which ClickHouse versions are affected?
24.3+
How to reproduce
CREATE TABLE n ENGINE=MergeTree ORDER BY tuple()
AS select number, intDiv(number, 16384) as x, randomPrintableASCII(10) as s FROM numbers(1000000);
-- IRL it was custom, user-defined setting
CREATE ROW POLICY n_rp ON n USING getSetting('temporary_live_view_timeout')=1 TO ALL;
SELECT 'no PREWHERE (always true row policy there instad)';
explain actions=1 SELECT max(s) FROM n WHERE number > 16384*9 and x < 10 SETTINGS optimize_move_to_prewhere = 1;
SELECT max(s) FROM n WHERE number > 16384*9 and x < 10 SETTINGS optimize_move_to_prewhere = 1 FORMAT JSON;
SELECT 'there is PREWHERE (both Row level filter and Prewhere filter are in the explain):';
explain actions=1 SELECT max(s) FROM n WHERE number > 16384*9 and x < 10 SETTINGS optimize_move_to_prewhere = 1, query_plan_optimize_prewhere = 0, enable_analyzer = 0;
-- pay attenction at elapsed & bytes_read
SELECT max(s) FROM n WHERE number > 16384*9 and x < 10 SETTINGS optimize_move_to_prewhere = 1, query_plan_optimize_prewhere = 0, enable_analyzer = 0 FORMAT JSON;https://fiddle.clickhouse.com/a571b1ea-0ba3-4453-9d26-8b75fe9e106b
Expected performance
same as with optimize_move_to_prewhere = 1, query_plan_optimize_prewhere = 0, enable_analyzer = 0
Additional context
No response