我们正在将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转换为String并计算“1”的个数),但与MySQL相比,它的性能非常糟糕。
Java uses a trick from Hacker's Delight,但AFAIK这在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倍(在相同的硬件上使用完全相同的200 k phash值数据集进行测试)。
3条答案
按热度按时间vkc1a9a21#
函数bit_count自PostgreSQL版本14起可用,请参见Bit String Functions and Operators。
示例:
结果是3。
注意,函数是为bit和bit variing类型定义的。所以如果你想用整数值来使用它,你需要强制转换。
示例:
结果为B'1101'
结合两个示例:
vc9ivgsu2#
问题:是否有与MySQL性能相当的解决方案/变通方案?
为了获得类似的速度,应该使用编译过的C函数。如果你能编译C代码,请参见https://github.com/dverite/postgresql-functions/tree/master/hamming_weight
代码本身非常简单。
根据将
bit(64)
字符串中的0
字符计数为文本,结果似乎比bit_count
函数快10倍。示例:
plpgsql函数:
C功能:
ebdffaop3#
如果您正在尝试计算感知散列或类似LSH位串的汉明距离,则此问题可能与此answer密切相关
如果您正在寻找一种在PostgreSQL数据库上执行汉明距离查询的预构建方法,那么这可能是解决方法:hamming distance search的扩展名