PostgreSQL自定义比特计数函数与内置方案性能差异疑问

这是个很有意思的观察！你原本以为手动实现的位运算循环会更快，但实际内置函数组合的表现反而不相上下甚至更优，核心原因在于PostgreSQL内置函数和PL/pgSQL函数的底层执行机制差异，具体可以拆解成这几点：

1. 内置函数是原生C实现，PL/pgSQL是解释执行 #

PostgreSQL的绝大多数内置函数（比如bit()类型转换、replace()、char_length()）都是用原生C语言编写并编译成机器码的，执行时直接调用底层指令，几乎没有额外开销。而你的bitcount函数是PL/pgSQL编写的，PL/pgSQL是一种解释型语言——每次函数调用时，PostgreSQL都要先解析函数内的语句、维护执行上下文、处理变量操作，这些额外的解释开销会抵消掉位运算算法本身的优势。

2. PL/pgSQL函数的调用开销被放大 #

对于这种需要调用10万次的场景，PL/pgSQL函数的单次调用开销会被显著放大。每次调用bitcount时，都要经历函数入参传递、上下文初始化、循环的解释执行步骤；而内置函数组合是作为查询的一部分直接在执行器中处理，不需要频繁的函数调用上下文切换，整体的执行流水线更高效。

3. 内置类型转换和字符串操作的底层优化远超预期 #

你可能觉得"转成bit类型再转文本、替换0、计算长度"是绕路的操作，但这些步骤在PostgreSQL底层都做了极致优化：

• a.n::bit(31)是直接在内存中对整数进行二进制位的批量转换，不需要逐位处理；
• replace(..., '0', '')和char_length()都是针对字符串的高效批量操作，底层用的是高度优化的字符串处理函数，比PL/pgSQL的逐次循环位运算要快得多。

4. 查询优化器对内置函数的友好性 #

PostgreSQL的查询优化器可以识别并优化内置函数的组合调用，比如可能会将类型转换、字符串操作合并成一个更紧凑的执行计划；而自定义PL/pgSQL函数对优化器来说是一个"黑盒"，无法进行深入的优化，只能按函数定义的逻辑逐次执行。

补充验证：如果换成C语言自定义函数会怎样？ #

如果把你的位计数逻辑用C语言写成PostgreSQL的扩展函数，性能一定会超过内置函数组合——因为你的算法本身（通过i & (i-1)清除最低位1来计数）的时间复杂度是O(k)，k是1的位数，而内置方案是固定的O(31)（因为转成bit(31)）。但PL/pgSQL的解释执行开销太大，导致优势无法体现。

你的自定义PL/pgSQL函数： #

CREATE OR REPLACE FUNCTION bitcount(i integer) RETURNS integer AS $$ 
DECLARE 
    n integer; 
    bitCount integer; 
BEGIN 
    bitCount := 0; 
    LOOP 
        IF i = 0 THEN EXIT; END IF; 
        i := i & (i-1); 
        bitCount:= bitCount+1; 
    END LOOP; 
    RETURN bitCount; 
END $$ LANGUAGE plpgsql;

对比的两个查询： #

1. 自定义函数查询：

SELECT a.n, bitcount(a.n) from generate_series(1, 100000) as a(n);

2. 内置函数组合查询：

SELECT a.n, char_length( replace(a.n::bit(31)::TEXT, '0', '')) FROM generate_series(1, 100000) as a(n);

内容的提问来源于stack exchange，提问作者sumit