Message-ID: <b3f1a6e3-1791-4cb1-a066-d13c842aeba9@gmail.com>
Date: Mon, 1 Jun 2026 11:30:17 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: hashjoins vs. Bloom filters (yet again)
To: Tomas Vondra <tomas@vondra.me>
References: <5cd8c20c-14b5-4b0d-bedc-69bf714e87eb@vondra.me>
Content-Language: en-US
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>, andrew@dunslane.net
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <5cd8c20c-14b5-4b0d-bedc-69bf714e87eb@vondra.me>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/b3f1a6e3-1791-4cb1-a066-d13c842aeba9%40gmail.com>
Precedence: bulk

Postgres is still gaining ground in this area. It’s helpful to see how other
databases handle these challenges.

On 30/05/2026 02:55, Tomas Vondra wrote:
> The patches got stuck mostly because deciding if it makes sense to
> build/use the Bloom filter is somewhat hard. For cases where 100% of the
> tuples have a match it's pointless - it's just pure cost, no benefit.
> The regressions are relatively small, though (<10%).

We ran into the same problem when trying to estimate the number of 'generated'
NULLs on the nullable side. So, it makes sense to focus on the estimation method
for 'unmatched' tuples as a separate task.

> However, chances are the filter is too big. We can't get work_mem, the
> join is already using that for the hash table etc. We can maybe use a
> fraction of it, and that may not be enough to fit the "perfect" filter.
> We could bail out and not use any Bloom filter at all, but that seems a
> bit silly. Maybe we can't fit the 2% filter, but 5% of 10% would be OK?

Looking at DuckDB’s code, using bloom filters during hash table construction
solves this issue.