This is an automated comment for commit 66660ee with description of existing statuses. It's updated for the latest CI running

❌ Click here to open a full report in a separate page

Nice.

Is it possible to use this feature directly as a secondary index, with or without projections? I don't see any documentation for these virtual columns, but it sounds like they might be useful for making some queries faster.

Is it possible to use this feature directly as a secondary index

You don't need any secondary index for this, it works just out-of-the box.

I don't see any documentation for these virtual columns, but it sounds like they might be useful for making some queries faster.

Yes, if you know the exact offset in the part you can optimize the query, but, ClickHouse already applies such optimizations internally based on primary and secondary indexes, you don't need to do this explicitly

if you know the exact offset in the part you can optimize the query, but, ClickHouse already applies such optimizations internally based on primary and secondary indexes, you don't need to do this explicitly

Can it be made possible to store _part_offset in a projection, too? I'm finding that secondary index analysis is too slow for a really big table (>100B rows) with a bloom filter index on a very high cardinality, very sparse column (trace_id). I imagine it would be asymptotically much faster if there were a projection like

SELECT trace_id, _part_offset ORDER BY trace_id

which could recalculate on merges and enable transforming a query like

SELECT * WHERE trace_id = 'abc'

into

SELECT * FROM table WHERE trace_id = 'abc' AND indexHint((_part, _part_offset) IN (SELECT (_part, _part_offset) FROM projection WHERE trace_id = 'abc'))

(using the same table snapshot for the inner select)

Is such a thing doable? I think there are a few things that need to work

• Referring to _part_offset in a projection (I get a "Missing columns" error)
• The projection must recompute the offset column on each merge, because the offset changes
• Either
  • The above query can be written using FROM table everywhere, and it'll use the projection for the inner select automatically because the projection reads the fewest rows
  • The query analyzer can rewrite the simpler version of the query to the expanded version
• The same table snapshot is used for the inner select query so a race with part merging won't make the results inconsistent
  • This might be much easier if the query analyzer does the expanding itself

Am I making any sense? My hope is that rather than doing an O(n) scan through secondary index marks (even if the constant factor is low due to high GRANULARITY), we can do an O(log n) binary search in the projection for k matching part offsets, then a O(k log n) binary search for those offsets in the main table. In my testing with primary key instead of _part_offset, this is MUCH faster in practice. And the advantage of this proposal is that it doesn't depend on your main table's primary key.

@EmeraldShift Oh this is a well-timed FR. I'm currently finalizing plans to contribute this feature in the coming days.

Wonderful! I look forward to reading your plans, and I'm happy to experiment with it and see how it fits in with my use case!