postgresql - PostgreSQL 中的 bit_count 函数

Question

我们正在将 MySQL 5.7 数据库迁移到 PostgreSQL 9.6。

一个真正的问题是 PostgreSQL 中缺少 bit_count 函数。即将发布的 10 版本也没有此功能。

当前的 MySQL 代码片段(简化):

-- mysql specific, tested with 5.7.19
select code,phash,bit_count(phash ^ -9187530158960050433) as hd 
from documents 
where phash is not null and bit_count(phash ^ -9187530158960050433) < 7
order by hd;

我们尝试了一种简单的解决方案(将 BIGINT 转换为字符串并计算“1”)，但与 MySQL 相比，它的性能非常糟糕。

Java uses a trick from Hacker's Delight ，但是据我所知，这对于 PostgreSQL 是不可能的，因为 >>> 运算符(也)不可用。

问题:是否有可用的解决方案/变通方法可与 MySQL 性能相媲美？

更新 1

我能找到的最佳解决方案是基于 this SO answer :

首先创建bit_count函数:

CREATE OR REPLACE FUNCTION bit_count(value bigint) 
RETURNS numeric 
AS $$ SELECT SUM((value >> bit) & 1) FROM generate_series(0, 63) bit $$
LANGUAGE SQL IMMUTABLE STRICT;

现在我们可以使用与 MySQL 几乎相同的 SQL:

-- postgresql specific, tested with 9.6.5
select code,phash,bit_count(phash # -9187530158960050433) as hd 
from documents 
where phash is not null and bit_count(phash # -9187530158960050433) < 7
order by hd;

更新 2

根据@a_horse_with_no_name 的评论，我尝试了这个功能:

-- fastest implementation so far. 10 - 11 x faster than the naive solution (see UPDATE 1)
CREATE OR REPLACE FUNCTION bit_count(value bigint) 
RETURNS integer 
AS $$ SELECT length(replace(value::bit(64)::text,'0','')); $$
LANGUAGE SQL IMMUTABLE STRICT;

但是，这仍然比 MySQL 慢 5 到 6 倍(在相同硬件上使用完全相同的 200k phash 值数据集进行测试)。

Answer 1

函数 bit_count 从 PostgreSQL 版本 14 开始可用，请参阅 Bit String Functions and Operators .

例子:

select bit_count(B'1101');

结果是 3。

请注意，该函数是为位和位变化类型定义的。所以如果你想将它与整数值一起使用，你需要转换。

例子:

select cast (cast (1101 as text) as bit varying);

结果是 B'1101'。

结合这两个例子:

select bit_count(cast (cast (1101 as text) as bit varying));