ClickHouse / ClickHouse/ClickHouse

simple count queries are slow after lightweight deletes

Open
#47,930 0 comments 3 reactions 0 assignees View on GitHub
comp-mutations comp-projections comp-query-optimizer external feature performance
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.