{{ viewTitle }}

CREATE TABLE eth.transactions
(
    ...
    from_address        FixedString(20),
    -- murmur = the address hash ONLY for active wallets
    -- (in the dict: > 256 txns over the last 30 days); else 0
    from_address_murmur UInt64 MATERIALIZED if(dictHas(
        'active_addresses_last_30_days_from_dict',
        murmurHash3_64(from_address)), murmurHash3_64(from_address), 0),
    to_address          FixedString(20),
    to_address_murmur   UInt64 MATERIALIZED if(dictHas(
        'active_addresses_last_30_days_to_dict',
        murmurHash3_64(to_address)), murmurHash3_64(to_address), 0),
    ...
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(block_timestamp)
ORDER BY (mortonEncode(to_address_murmur, from_address_murmur),
          block_number, transaction_index)
SETTINGS index_granularity = 256

Both *_address_murmur columns are MATERIALIZED — computed on insert, non-zero only when the address is in the active dictionary; everyone else hashes to 0.

The ORDER BY sorts on mortonEncode(to, from), so an active address's rows cluster whether it's the sender or the receiver. PARTITION BY month and index_granularity 256 keep every scan bounded.

-- 1. hash → block : a flat lookup table
CREATE MATERIALIZED VIEW transactions_hash_reverse_idx_stream
TO transactions_hash_reverse_idx AS
SELECT murmurHash3_32(hash) AS hash, block_number
FROM eth.transactions;

-- using it: resolve the block from the index, then read just that block
SELECT * FROM eth.transactions
WHERE block_number IN (
        SELECT block_number FROM transactions_hash_reverse_idx
        WHERE hash = murmurHash3_32(unhex(substring('0x63f7…20c59a', 3)))
      )
  AND hash = unhex(substring('0x63f7…20c59a', 3));

A projection can be the reverse index

WITH idx AS (
  SELECT morton, block_number,
         sum(transactions_count) OVER (ORDER BY block_date DESC
              ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS newer
  FROM eth.transactions_address_reverse_idx
  WHERE address = murmurHash3_64(unhex('…'))
  QUALIFY newer < 25          -- keep groups until 25 is covered
)
SELECT ... FROM eth.transactions AS t
WHERE mortonEncode(t.to_address_murmur, t.from_address_murmur)
        IN (SELECT arrayJoin(morton) FROM idx)
   OR  block_number IN (SELECT arrayJoin(bitmapToArray(block_number)) FROM idx)
ORDER BY block_number DESC, transaction_index DESC
LIMIT 25

For this address (143 txns over 49 days), the last 25 are covered by the 9 newest day-groups — 29 candidate rows. The other 40 days, including a 48-tx day just below the cut, are never read. On a long-lived address it's a handful of recent groups instead of its entire history.

-- transactions per day across all of 2023
SELECT toDate(block_timestamp) AS day, count()
FROM eth.transactions
WHERE toDate(block_timestamp) BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY day
SETTINGS optimize_use_projections = 1   -- then 0

Both paths return the same 365 rows. The projection reads one pre-grouped row per day; the raw path re-scans every transaction in the year — 383 million rows, 1.8 GB.

PROJECTION tx_per_day (
  SELECT toDate(block_timestamp) AS date,
         count() AS total_count,
         countIf(receipt_status) AS completed_count
  GROUP BY date )

It's stored and merged alongside the table, so it's always in sync — no manual refresh. The planner picks it transparently whenever a query's GROUP BY matches.

-- one address, run twice: skip index ON vs OFF
SELECT count()
FROM eth.transactions_address_reverse_idx
WHERE address = murmurHash3_64(unhex('eaf9a34b…dc42'))
SETTINGS use_skip_indexes = 1   -- then 0

Both runs return the same 212 rows. The index is partitioned by month: with the bloom off, the primary key reads one candidate granule in every one of the 264 parts; with it on, the 215 parts where this address never appears are skipped — only 49 are touched.

INDEX address_bloom address
  TYPE bloom_filter(0.0001)
  GRANULARITY 100

A sparse index only orders by the first key

-- a selective filter on a non-indexed column, run twice
SELECT count()                       -- 87 transfers over 30,000 ETH
FROM eth.transactions
WHERE block_timestamp >= '2023-06-01' AND block_timestamp < '2023-07-01'
  AND value > 30000e18
SETTINGS use_query_condition_cache = 1

The first run pays the scan and records the bitmap; every later query with that filter rides it. It can't go stale — parts are immutable, so a recorded bit stays valid; only brand-new parts are still unknown.

-- the table shows 10 rows per page, but every listing query asks for 100
SELECT … FROM eth.transactions
WHERE …
ORDER BY …
LIMIT 100;   -- one deterministic batch — identical text on every page flip

Apply only where they pay