title: postgres inbuilt vs user defined function performance
body: <p>I have to count the number of bits in postgresql on very large integer columns for which i wrote a postgresql funtion to count the number of bits in an integer.</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION bitcount(i integer) RETURNS integer AS $$
  DECLARE n integer;
    DECLARE bitCount integer;
  BEGIN
    bitCount := 0;
  
    LOOP
      IF i = 0 THEN
        EXIT;
      END IF;
      i := i &amp; (i-1);
      bitCount:= bitCount+1;
    END LOOP;
    RETURN bitCount;
  END
  $$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>but i found one more way to do this using pg's inbuilt functions as well
  like </p>
  
  <pre><code>SELECT char_length( replace(100::bit(31)::TEXT, '0', ''));
  </code></pre>
  
  <p>so i decided to compare performance of both of the ways
  so i used below queries to compare </p>
  
  <p>First</p>
  
  <pre><code>SELECT a.n, bitcount(a.n)
  from generate_series(1, 100000) as a(n);
  </code></pre>
  
  <p>Second</p>
  
  <pre><code>SELECT a.n, char_length( replace(a.n::bit(31)::TEXT, '0', ''))
  FROM generate_series(1, 100000) as a(n);
  </code></pre>
  
  <p>I was expecting that First method will outperform the second one
  but to my surprise both of them performed almost same. In fact on my machine second one always completed a few seconds faster even with large number of integers.</p>
  
  <p>can anyone explain me why second is almost as fast as first despite of using cast and string operation ?</p>