title: bit_count function in PostgreSQL
body: <p>We are in the process of migrating a MySQL 5.7 database to PostgreSQL 9.6.</p>
  
  <p>A real issue is the lack of <code>bit_count</code> function in PostgreSQL. This function is also not available in the upcoming version 10.</p>
  
  <p>Current MySQL code snippet (simplified):</p>
  
  <pre><code>-- 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) &lt; 7
  order by hd;
  </code></pre>
  
  <p>We tried a naive solution (converting the BIGINT to a String and counting the "1"'s), but it performs terribly compared to MySQL.</p>
  
  <p>Java <a href="https://stackoverflow.com/a/7950608/868941">uses a trick from Hacker's Delight</a>, but AFAIK this is not possible with PostgreSQL, because the <code>&gt;&gt;&gt;</code> operator is (also) not available.</p>
  
  <p><strong>Question</strong>: Is a there solution/workaround available <em>comparable with MySQL performance wise</em>?</p>
  
  <p><strong>UPDATE 1</strong> </p>
  
  <p>Best solution i could find is based on <a href="https://stackoverflow.com/a/5459637/868941">this SO answer</a>:</p>
  
  <p>First create <code>bit_count</code> function:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION bit_count(value bigint) 
  RETURNS numeric 
  AS $$ SELECT SUM((value &gt;&gt; bit) &amp; 1) FROM generate_series(0, 63) bit $$
  LANGUAGE SQL IMMUTABLE STRICT;
  </code></pre>
  
  <p>Now we can use almost the same SQL as with MySQL:</p>
  
  <pre><code>-- 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) &lt; 7
  order by hd;
  </code></pre>
  
  <p><strong>UPDATE 2</strong> </p>
  
  <p>Based on @a_horse_with_no_name comment, i tried this function:</p>
  
  <pre><code>-- 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;
  </code></pre>
  
  <p>However, this is still 5 - 6 times slower than MySQL (tested wit exact the same data set of 200k phash values on the same hardware).</p>