I have clickhouse 24.11 and this is table structure that AI helped me to do:
CREATE TABLE ethereum.transfers
(
`blockNumber` UInt64,
`transactionIndex` UInt32,
`transactionHash` LowCardinality(FixedString(66)) CODEC(ZSTD(3)),
`timestamp` UInt32,
`address` LowCardinality(Nullable(FixedString(42))),
`from_` LowCardinality(Nullable(FixedString(42))),
`to_` LowCardinality(Nullable(FixedString(42))),
`logIndex` Nullable(UInt32),
INDEX transfers_multi_address (address, from_, to_) TYPE set(100) GRANULARITY 4,
INDEX transfers_from_block_skip (from_, blockNumber) TYPE bloom_filter GRANULARITY 4,
INDEX transfers_to_block_skip (to_, blockNumber) TYPE bloom_filter GRANULARITY 4,
PROJECTION transfers_address_search
(
SELECT *
ORDER BY
from_,
to_,
blockNumber,
transactionIndex
)
)
ENGINE = MergeTree
PRIMARY KEY blockNumber
ORDER BY (blockNumber, transactionIndex)
SETTINGS index_granularity = 8192, min_rows_for_wide_part = 1000000, min_rows_for_compact_part = 10000, min_bytes_for_compact_part = 10000000
I need query:
SELECT
transactionHash,
from_ as from,
to_ as to
FROM transfers
WHERE (from_='0xda8cf15bc458a7b13735e7d04495f8cda53f3205' or to_='0xda8cf15bc458a7b13735e7d04495f8cda53f3205') and blockNumber<=21405134
ORDER BY blockNumber DESC, transactionIndex ASC
LIMIT 500
or
SELECT
transactionHash,
from_ as from,
to_ as to
FROM transfers
WHERE from_='0xda8cf15bc458a7b13735e7d04495f8cda53f3205'
AND blockNumber<=21405134
UNION ALL
SELECT
transactionHash,
from_ as from,
to_ as to
FROM transfers
WHERE to_='0xda8cf15bc458a7b13735e7d04495f8cda53f3205'
AND blockNumber<=21405134
ORDER BY blockNumber DESC, transactionIndex ASC
LIMIT 500;
The problem that it loads a lot of time:
1296 rows in set. Elapsed: 0.290 sec. Processed 63.85 million rows, 139.89 MB (220.12 million rows/s., 482.29 MB/s.)
SELECT count(*)
FROM transfers
Query id: 46c6e414-ec2f-457b-9028-09e76dba1426
┌───count()─┐
1. │ 108681712 │ -- 108.68 million
here is explain:
┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ Union │
2. │ Filter │
3. │ ReadFromMergeTree (transfers_address_search) │
4. │ Indexes: │
5. │ PrimaryKey │
6. │ Keys: │
7. │ from_ │
8. │ blockNumber │
9. │ Condition: and((blockNumber in (-Inf, 21405134]), (from_ in ['0xda8cf15bc458a7b13735e7d04495f8cda53f3205', '0xda8cf15bc458a7b13735e7d04495f8cda53f3205'])) │
10. │ Parts: 12/12 │
11. │ Granules: 12/13258 │
12. │ Expression (Project names) │
13. │ Limit (preliminary LIMIT (without OFFSET)) │
14. │ Sorting (Sorting for ORDER BY) │
15. │ Expression ((Before ORDER BY + Projection)) │
16. │ Expression │
17. │ ReadFromMergeTree (ethereum.transfers) │
18. │ Indexes: │
19. │ PrimaryKey │
20. │ Keys: │
21. │ blockNumber │
22. │ Condition: (blockNumber in (-Inf, 21405134]) │
23. │ Parts: 15/15 │
24. │ Granules: 13272/13272 │
25. │ Skip │
26. │ Name: transfers_multi_address │
27. │ Description: set GRANULARITY 4 │
28. │ Parts: 15/15 │
29. │ Granules: 13272/13272 │
30. │ Skip │
31. │ Name: transfers_to_block_skip │
32. │ Description: bloom_filter GRANULARITY 4 │
33. │ Parts: 9/15 │
34. │ Granules: 7782/13272 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Any ideas how to optimize?
1