ClickHouse / ClickHouse/ClickHouse
simple count queries are slow after lightweight deletes
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
If you use light-weight delete on the table - the feature optimize_trivial_count_query and virtual projection allow_experimental_projection_optimization will stop working, so `select count()` will be happening via slow-path (reading the smallest columns, which still can be huge).
It would be good if the LWD would write in every part number of rows deleted so that virtual count projection can use that information to avoid heavy scans.
P.S. To fix that, you need to optimize the table to remove _row_exists masks in all partitions - using OPTIMIZE or some (full) mutation.
```
SELECT DISTINCT format('OPTIMIZE TABLE {}.{} PARTITION ID \'{}\' FINAL;', database, table, partition_id)
FROM system.parts_columns
WHERE column = '_row_exists'
FORMAT TSVRaw
```
Contributor guide
Assessment
This issue has not been assessed yet.