#1959 [Ideas] Bloom filter performance: four experiments New discussion created by leborchuk Description This is a research issue. The deliverable for each task is a short report with numbers and a recommendation — not a patch. Code is welcome as evidence, but "we measured X, therefore do Y" is the output we want. A negative result is a successful outcome and must be reported with the same rigour as a positive one: "we tried this, it gained 0.8%, not worth the complexity" is valuable and closes the question permanently. Each of the four tasks below is independent and can be a separate PR/report. Read "How to produce results we can act on" before starting any of them. The current implementation, in facts | Aspect | Anser today | Where | | ------------------ | ---------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------- | | sizing target | 2 bytes (16 bits) per key | bloom_create(), src/backend/lib/bloomfilter.c:96-101 | | bitset floor / cap | 1 MB floor, 64 MB cap | same, plus ANSER_RF_MIN_BYTES / ANSER_RF_MAX_BYTES in gpcontrib/anser/src/anserplan.c:50-51 | | bitset rounded | down to a power of two | my_bloom_power() | | hash functions | optimal_k() = round(0.693 × m/n), capped at 10 | bloomfilter.c:MAX_HASH_FUNCS, optimal_k() | | per-element cost | one hash_any_extended() + k cheap derivations + k random bitset accesses | k_hashes() | | union of parts | for (i...) abits[i] \|= pbits[i]; — one byte at a time | AnserBloomFoldPartInPlace(), gpcontrib/anser/src/anserfilter.c | | union lock | runs while holding AnserChannelLock EXCLUSIVE | AnserStorePayloadDSM(), gpcontrib/anser/src/anser.c:1868 (the Assert) | | wire format | raw header + raw bitset, uncompressed, binary bytea | AnserBloomSerializePart(), sent by anserclient.c | | give up early | never | — | Cloudberry already answers three of these questions differently The core runtime filter (

src/backend/executor/nodeRuntimeFilter.c

) uses

bloom_create_aggresive()

(

bloomfilter.c

), and its choices are the opposite of Anser's: | Anser (bloom_create) | core RF (bloom_create_aggresive) | | | -------------------- | -------------------------------- | ---------------------------------------------------------------------------------------------------------- | | bits per key | 16 | 9 (total_elems * 9 / 8 bytes) | | floor / cap | 1 MB / 64 MB | 128 KB / 2 MB | | hash functions | up to 10 (optimal_k) | 2 or 3, hard-coded, "balance false positive rate and calculation costs" | | refuses to build | never | when bits_per_elem < 1.6 → returns NULL, "too many elements" | | abandons mid-build | never | yes: inner_threshold = bloom_total_bits(bf) / 1.6, then build_suspend = true (nodeRuntimeFilter.c:246,311) | Start every task by explaining why Anser differs from this, and treat the aggressive profile as a candidate answer that someone in this codebase already committed to. Do not invent new thresholds before measuring against that one. How to produce results we can act on This section is the actual hard part of this issue. Results that don't follow it will be sent back, not because of process for its own sake, but because we cannot make a shipping decision from them. 1. Build discipline (most common way to waste a week) • Never benchmark a build configured with

--enable-cassert

or

--enable-debug

only. Assertions make PostgreSQL 20-40% slower and distort exactly the kind of tight loop you are measuring. Configure a separate optimized build (

-O2

, no cassert) and paste the

configure

line into your report. • Record the compiler and version. Auto-vectorization differs between GCC and Clang and between versions; a result without a compiler version is not reproducible. • Pin a baseline commit hash and re-measure the baseline in the same session as the variant. Never compare against numbers from yesterday's machine state. 2. Measure at two layers, always both | Layer | How | Answers | | ------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ---------------------------- | | Micro | a SQL-callable C benchmark added to gpcontrib/anser/src/anser_test.c (follow the existing anser_test_bloom_* helpers), e.g. anser_test_bench_fold(bytes, iters) returning elapsed ms | "is the OR faster?" | | End-to-end | query wall time on a demo/real cluster, TPC-DS with SF=100 | "does the query get faster?" | A micro-benchmark win that does not move end-to-end time is a negative result — report it as such. That happens constantly and is fine. Keep benchmark helpers out of the regression expected output; they are nondeterministic. They are for manual runs. Notes about TPC-DS Scale factor 100 minimum. SF 1 and SF 10 are not acceptable evidence: the tables fit in memory, the filter is pure overhead, and the result tells us nothing about production. Use SF 1000 if the hardware allows, and state the cluster shape (hosts × segments per host). You could use special extension to run TPC-DS https://github.com/avamingli/pg_tpcds Step 1 — find out which queries Anser even touches. The plan pass only injects into one join shape, so most of the 99 queries will be untouched and their timings are noise. Establish the injected subset first: Report that list — it is a result in its own right, and if it is empty, stop and open a bug instead of benchmarking. Expect the star-schema fact/dimension joins (q3, q7, q13, q17, q19, q25, q29, q42, q43, q46, q52, q55, q64, q68, q72, q79, q88, q96, q98 are the usual candidates), but use your measured list, not this one. Step 2 — run the full suite, 3 iterations, `anser.runtime_fi on, everything else identical. Report per-query medians. Step 3 — report in three parts: 1. Injected queries — the detail table: | Query | baseline (s) | variant (s) | Δ% | consumer wait (ms) | | --- | --- | --- | --- | --- | --- | | q19 | | | | | | 1. Non-injected queries — one line: worst regression observed, of que… apache/cloudberry