在Postgres的位操作 [英] Bitwise operations in Postgres

问题描述

我有以下表格：

types | id | name
------+----+----------
         1 | A
         2 | B
         4 | C
         8 | D
         16| E
         32| F

和

vendors | id | name     | type
--------+----+----------+-----
           1 | Alex     | 2     //type B only
           2 | Bob      | 5     //A,C
           3 | Cheryl   | 32    //F
           4 | David    | 43    //F,D,A,B
           5 | Ed       | 15    //A,B,C,D
           6 | Felix    | 8     //D
           7 | Gopal    | 4     //C
           8 | Herry    | 9     //A,D
           9 | Iris     | 7     //A,B,C
           10| Jack     | 23    //A,B,C,E

我现在想查询：

select id, name from vendors where type & 16 >0 //should return Jack as he is type E
select id, name from vendors where type & 7 >0 //should return Ed, Iris, Jack
select id, name from vendors where type & 8 >0 //should return David, Ed, Felix, Herry 

什么是表类型和厂商在Postgres最好的指​​数？我可能有几百万行的供应商。此外，什么是使用这种方法，按位与多对多关系使用第三表进行比较的权衡？哪个更好呢？

What is the best possible index for tables types and vendors in postgres? I may have millions of rows in vendors. Moreover, what are the tradeoffs of using this bitwise method compared with Many To Many relation using a 3rd table? Which is better?

推荐答案

使用可以使用部分索引来解决的事实，与＆amp;是不是可转位运算符（据我所知）：

Use can use partial indices to work around the fact that "&" isn't an indexable operator (afaik):

CREATE INDEX vendors_typeA ON vendors(id) WHERE (type & 2) > 0;
CREATE INDEX vendors_typeB ON vendors(id) WHERE (type & 4) > 0;

当然，你需要在每次添加一个新的类型时添加的新指标。这是原因的数据扩大到然后可以正确索引的关联表之一。你总是可以编写触发器还保持一个位掩码表，但使用许多一对多表实际上保持正常的数据，因为这将是更清晰。

Of course, you'll need to add a new index every time you add a new type. Which is one of the reasons for expanding the data into an association table which can then be indexed properly. You can always write triggers to maintain a bitmask table additionally, but use the many-to-many table to actually maintain the data normally, as it will be much clearer.

如果缩放和性能的整个评价说：我可能有几百万行，你做的还不够，开始去为这种优化。首先创建一个适当的结构清晰的模式，以后优化它有关它如何执行实际的统计数据的基础上。

If your entire evaluation of scaling and performance is to say "I may have millions of rows", you haven't done enough to start going for this sort of optimisation. Create a properly-structured clear model first, optimise it later on the basis of real statistics about how it performs.

这篇关于在Postgres的位操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！