Workflow [PR], commit [4369634]

Summary: ❌

SET optimize_inverse_dictionary_lookup = 0;

It is OK, that it's under setting, because there is certain problem that a lot of default dictionary layouts quite bad on access not by key column.

When people manually do this kind of optimization, sometimes they do it not against dictionary, but "SOURCE" table of it.
(But this raise other questions about non consistency with data in dictionary and etc)

Another, more complex alternative, is to build cache of color_id -> result of expression: dictGet('colors','name', xxx) = 'red'

Another, more complex alternative, is to build cache of color_id -> result of expression: dictGet('colors','name', xxx) = 'red'

Actually, I think the complex alternative that you mentioned is happening under the hood with this optimization. For example, for this query:

EXPLAIN PLAN SELECT color_id, payload FROM t WHERE dictGetString('colors', 'name', color_id) = 'red' ORDER BY color_id, payload;

The output is:

CreatingSets (Create sets before main query execution)
  Expression (Project names)
    Sorting (Sorting for ORDER BY)
      Expression ((Before ORDER BY + Projection))
        Expression ((WHERE + Change column names to column identifiers))
          ReadFromMergeTree (default.t)

My understanding is that it means dictGetString('colors', 'name', color_id) = 'red' is converted to a set in which later we are doing repeated lookups via IN, completely skipping dictionary lookup per row which could be slow.

Stateless tests (amd_msan, parallel)

• Flaky test: 00816_long_concurrent_alter_column #90683
• Test 00906_low_cardinality_cache is flaky #91024
• 02992_all_columns_should_have_comment - unrelated

BuzzHouse (amd_debug)

• Inconsistent AST formatting of INSERT SELECT EXCEPT/INTERSECT #90925

@nihalzp hi, please look at this performance run, perf degradation seems to be related

https://s3.amazonaws.com/clickhouse-test-reports/PRs/88971/436963437245b09c2c6963734176c74d59af4a04//performance_comparison_arm_release_master_head_3_6/report.html#unstable-queries

@fm4v Most likely not caused by this. The optimization in this PR is only applied for dictGet(....) = 'const' form which is not the case in the slowed down queries.

https://fiddle.clickhouse.com/8ba04806-58a1-4a09-b458-6b8e0ad6432e

It is confirmed by EXPLAIN SYNTAX. We can see that optimization is not applied.