Postgresql Bitstring index for multicolumns

宏哥 发布于 2012/06/26 15:06
阅读 293
收藏 1

@mark35http://stackoverflow.com/questions/11126180/exclusion-constraint-on-a-bitstring-column-with-bitwise-and-operator

能否给点建议.

关于关系表记录,和单字段通过bitset 记录关系之间的对于存储,以及index,性能方面的.

运用场景在于 ,多选项的记录. 是否值得用bitstring 来取代关系表作为有限制的多选记录

加载中
0
宏哥
宏哥
@mark35  ?. 等你空的时候看看,我先试试看
0
mark35
mark35
研究中
0
宏哥
宏哥

引用来自“mark35”的答案

研究中

我在考虑将其应用于一个主数据,对应多个选项的情况.如果能用这个,就可以在单字段上而不需要额外的关联表进行记录,并且,如果支持在bit上进行索引,速度一定是极快的.

POSTGRES Never disappoint you 

0
mark35
mark35

引用来自“宏哥”的答案

引用来自“mark35”的答案

研究中

我在考虑将其应用于一个主数据,对应多个选项的情况.如果能用这个,就可以在单字段上而不需要额外的关联表进行记录,并且,如果支持在bit上进行索引,速度一定是极快的.

POSTGRES Never disappoint you 

我之前的做法是字段定义成int4[]类型,不区分位权,多选值sort后插入(不sort也不影响结果)。对于查询构造出查询条件ARRAY[v1, v2],然后用 col @> ARRAY[v1, v2] 来查询。

如果用bitstring按位保存,应该更省空间。

0
宏哥
宏哥

引用来自“mark35”的答案

引用来自“宏哥”的答案

引用来自“mark35”的答案

研究中

我在考虑将其应用于一个主数据,对应多个选项的情况.如果能用这个,就可以在单字段上而不需要额外的关联表进行记录,并且,如果支持在bit上进行索引,速度一定是极快的.

POSTGRES Never disappoint you 

我之前的做法是字段定义成int4[]类型,不区分位权,多选值sort后插入(不sort也不影响结果)。对于查询条件是多值,就用 col @> value 来查询。

如果用bitstring按位保存,应该更省空间。

我也考虑过此方案,这样需要额外的约束来阻止你在int4[]当中保存了重复记录.

int4[]这种数组情况,你索引如何建立.

0
mark35
mark35

引用来自“宏哥”的答案

引用来自“mark35”的答案

引用来自“宏哥”的答案

引用来自“mark35”的答案

研究中

我在考虑将其应用于一个主数据,对应多个选项的情况.如果能用这个,就可以在单字段上而不需要额外的关联表进行记录,并且,如果支持在bit上进行索引,速度一定是极快的.

POSTGRES Never disappoint you 

我之前的做法是字段定义成int4[]类型,不区分位权,多选值sort后插入(不sort也不影响结果)。对于查询条件是多值,就用 col @> value 来查询。

如果用bitstring按位保存,应该更省空间。

我也考虑过此方案,这样需要额外的约束来阻止你在int4[]当中保存了重复记录.

int4[]这种数组情况,你索引如何建立.

我是直接创建一个普通btree索引。我的应用不限制重复,只需要查找包含指定(单个或多个)值的情况。

刚才测试了下,创建unique索引即可防止重复记录。但需要考虑是否要有位权的情况。如果不存在位权,那么入库数据先sort处理。如果你应用需要位权,那么入库值就需要先格式化出所有位,这样在格式化时较为麻烦,但查询时可以指定该字段的片段(下标)。没测试过指定下标查询的索引处理如何。

 

宏哥
宏哥
Thanks and wait for your evaluation.
0
mark35
mark35

30W记录,typeids 创建了普通btree索引,已ANALYZE。

explain analyze select * from cdb_threads where typeids = ARRAY[2,55]
Index Scan using "threads.typeids" on cdb_threads  (cost=0.00..8.83 rows=2 width=222) (actual time=0.034..0.036 rows=1 loops=1)
  Index Cond: (typeids = '{2,55}'::integer[])
Total runtime: 0.076 ms

explain analyze select * from cdb_threads where typeids @> ARRAY[2,55]

Seq Scan on cdb_threads  (cost=0.00..57154.50 rows=312 width=222) (actual time=250.078..297.224 rows=1 loops=1)
  Filter: (typeids @> '{2,55}'::integer[])
Total runtime: 297.267 ms

 

 

宏哥
宏哥
我最终没有选择bit string. 多选我仍然用关联表记录,不同的是. 我建立一个主表,对于不同的breakdown, 衍生子表,继承的方式,相当于分区,但是暂时没有对存储进行隔离
0
宏哥
宏哥

引用来自“mark35”的答案

30W记录,typeids 创建了普通btree索引,已ANALYZE。

explain analyze select * from cdb_threads where typeids = ARRAY[2,55]
Index Scan using "threads.typeids" on cdb_threads  (cost=0.00..8.83 rows=2 width=222) (actual time=0.034..0.036 rows=1 loops=1)
  Index Cond: (typeids = '{2,55}'::integer[])
Total runtime: 0.076 ms

explain analyze select * from cdb_threads where typeids @> ARRAY[2,55]

Seq Scan on cdb_threads  (cost=0.00..57154.50 rows=312 width=222) (actual time=250.078..297.224 rows=1 loops=1)
  Filter: (typeids @> '{2,55}'::integer[])
Total runtime: 297.267 ms

 

 

是不是说明 array 有点类似复合索引,并且和顺序有关, 而 contains 的逻辑,导致扫描,无法使用btree
0
mark35
mark35

引用来自“宏哥”的答案

引用来自“mark35”的答案

30W记录,typeids 创建了普通btree索引,已ANALYZE。

explain analyze select * from cdb_threads where typeids = ARRAY[2,55]
Index Scan using "threads.typeids" on cdb_threads  (cost=0.00..8.83 rows=2 width=222) (actual time=0.034..0.036 rows=1 loops=1)
  Index Cond: (typeids = '{2,55}'::integer[])
Total runtime: 0.076 ms

explain analyze select * from cdb_threads where typeids @> ARRAY[2,55]

Seq Scan on cdb_threads  (cost=0.00..57154.50 rows=312 width=222) (actual time=250.078..297.224 rows=1 loops=1)
  Filter: (typeids @> '{2,55}'::integer[])
Total runtime: 297.267 ms

 

 

是不是说明 array 有点类似复合索引,并且和顺序有关, 而 contains 的逻辑,导致扫描,无法使用btree

嗯,感觉有点类似复合索引。对于 = 的比较符走索引,对于 @> 包含操作符不选择索引。 如果可以确定位权,那么就可以把 @> 包含条件改成 = 的条件,这样就可以利用索引了。

我这儿的例子是必须包含typeid值,所以无法用bitstring的方式。那种方式对于有限分类的情况索引效率应该更高(还是只能用 = 比较符)。

返回顶部
顶部