Workflow [PR], commit [da741fb]

Does this transformation work when the key x is in the primary key, or has special index analysis, like _part_offset + _part_starting_offset? For my use case, I have a query like this:

WITH offsets AS
    (
        SELECT _part_starting_offset + _parent_part_offset AS offset
        FROM mergeTreeProjection('otel', 'spans', 'by_span')
        WHERE trace_id = unhex({trace:String})
    )
SELECT *
FROM otel.spans
WHERE (_part_offset + _part_starting_offset) IN (offsets)

There are ~90 million matching rows in the projection, and CreatingSetsTransformation takes a very long time:

CreatingSetsTransform: Created Set with 89792618 entries from 89792618 rows in 9.025697714 sec.

But the rest of the query is very fast because the part offsets are included in primary index analysis, and can quickly filter parts and granules.
However, If I try to manually rewrite the query as this PR suggests:

WITH offsets AS
    (
        SELECT _part_starting_offset + _parent_part_offset AS offset
        FROM mergeTreeProjection('otel', 'spans', 'by_span')
        WHERE trace_id = unhex({trace:String})
    )
SELECT *
FROM otel.spans
WHERE exists((
    SELECT 1
    FROM
    (
        SELECT offset
        FROM offsets
    )
    WHERE (_part_offset + _part_starting_offset) = offset
    LIMIT 1
))

then the query takes even longer, and it appears to perform a full table scan.

Is there some way to eliminate the lag of CreatingSets, but also utilize the primary key for part offsets? Then, at any size, it would be fast to join against the right side of IN.

Is there some way to eliminate the lag of CreatingSets, but also utilize the primary key for part offsets?

I think you can try to play with this sub-query to speed it up. Does it also take 9 sec when run separately?

 SELECT _part_starting_offset + _parent_part_offset AS offset
         FROM mergeTreeProjection('otel', 'spans', 'by_span')
         WHERE trace_id = unhex({trace:String}) FORMAT Null

Then, at any size, it would be fast to join against the right side of IN
You right, just rewriting IN to join will not help, there needs to be an optimization that allows skipping data during reading. Maybe this optimization can help #81526

I think you can try to play with this sub-query to speed it up. Does it also take 9 sec when run separately?

The subquery only takes ~2 seconds to complete. Then, after it's complete, the CreatingSetsTransformation takes an additional ~9 seconds to transform the result into a set for use with IN, and finally the main query runs quickly, due to the primary index analysis on the part offset columns.

Maybe this optimization can help #81526

Does this work for the primary index too? Notably I am not utilizing any skip indexes in the main query, just the special part offset columns.

At any rate, it seems there are two separate issues:

• CreatingSets can be very slow for large sets (is this expected? Maybe it can be optimized? Or maybe it's bad and that's why this PR exists?)
• This JOIN transformation eliminated the cost of CreatingSets for my query (yay!) but also seems to have eliminated index analysis (sad!) on the intermediate result. I don't know how to recover the original behavior of the main query with this transformation.

Test failures:
AST fuzzer (amd_tsan) #86957
Integration tests (arm_binary, distributed plan, 1/4) #87787

Just double checking, is this transformation going to affect queries like my earlier comment, which leverage the primary key analysis between running the subquery and the main query? If it will have a negative impact, then is the transformation optional?

@EmeraldShift This transformation is optional and is disabled by default, it is controlled by rewrite_in_to_join setting.
Currently the rewritten query will not use indexes for the IN condition that was transformed.