bit_count在PostgreSQL中的作用

Question

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

一个真正的问题是bit_count函数在PostgreSQL中的缺乏。此功能在即将推出的版本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相比，它的性能非常差。

用黑客的快感戏法，但是AFAIK在PostgreSQL中是不可能的，因为>>>运算符(也)不可用。

问题：有一种解决方案/解决方案可与MySQL性能方面的解决方案媲美吗？

更新1

我能找到的最好的解决方案是基于这就是答案

首先创建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倍(测试的wit在相同的硬件上完成相同的200 k phash值的数据集)。

Answer 1

函数bit_count可以从PostgreSQL版本14开始使用，请参阅位串函数和运算符。

示例：

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));

Answer 2

问:有一种解决方案/解决方案可以与MySQL的性能相媲美吗？

为了获得类似的速度，应该使用编译后的C函数。如果您可以编译C代码，请参见例如重量

代码本身非常简单。

基于将bit_count字符串中的0字符计数为文本，结果似乎比bit(64)函数快10倍。

示例：

plpgsql函数：

test=> select sum(bit_count(x)) from generate_series(1,1000000) x;
   sum   
---------
 9884999
(1 row)

Time: 2442,340 ms

C职能：

test=> select sum(hamming_weight(x::int8)) from generate_series(1,1000000) x;
   sum   
---------
 9884999
(1 row)

Time: 239,749 ms

Answer 3

如果您试图计算知觉散列或类似LSH位串的hamming距离，则此问题可能与此回答密切相关。

如果您正在专门寻找一种在PostgreSQL数据库上执行hamming距离查询的预先构建的方法，那么这可能是解决方法：hamming距离搜索的扩展。