通过 pg_roaringbitmap 优化标签查询的话，有没有必要做高频标签映射和结合普通复合索引？

[shendaowu]

我问了 DeepSeek ，它说 roaringbitmap 类型的值的元素的取值范围如果很大的话会影响效率。就是类似 INSERT INTO tag_content_bitmaps (tag_id, content_bitmap) VALUES(1, rb_build(ARRAY[1,100000])) 的内容查询效率会低于 INSERT INTO tag_content_bitmaps (tag_id, content_bitmap) VALUES(1, rb_build(ARRAY[1,2])) 。然后我就想到了一些花活。

首先，使用关系型数据库建立（标签 id ，内容 id ）复合索引之后如果标签的使用率不高的话，搜索效率还是可以的，但是如果标签的使用率很高，那么好像是因为区分度过低，所以搜索的效率会很低。但是 pg_roaringbitmap 对于高使用率的标签来说速度好像还是挺快的。但是就像前面第一段说的，这个也不是免费的。所以我不太敢全部标签都塞到 roaringbitmap 类型里。然后是具体的花活。

标签 id,标签名字,使用率
1,苹果,0.3
2,香蕉,0.01
3,梨,0.15
高频标签 id,原标签 id
1,1 //这个对应苹果
2,3 //这个对应梨

不过这还有问题，就是如何禁止高频标签建立索引，因为反正也不会用，还会占地方。可能最高效的方法是将低频标签全都复制到一个新表里？不过这个很反模式，也会导致维护麻烦。我问过 DeepSeek ，它给的方法感觉效率不怎么高的样子。

结合普通复合索引就是如果要搜索的标签同时包含高频标签和低频标签。那么就先在 roaringbitmap 里搜索那些高频标签，然后在结果中搜索满足低频标签的内容。或者可能反过来搜索效率更高？ DeepSeek 说前者效率更高。

这个高频和低频的分界线有没有比较好的建议？我目前定的是 3%。因为 DeepSeek 说 1% 到 5% 的使用率的标签建立索引还是可以的。

roaringbitmap 相关的表结构：

CREATE TABLE tag_content_bitmaps (
tag_id BIGINT PRIMARY KEY,
content_bitmap roaringbitmap
);
CREATE TABLE content_tag_bitmaps (
content_id BIGINT PRIMARY KEY,
tag_bitmap roaringbitmap
);

或者我这套花活是不是没什么大用？有没有更好的实现加速标签查询的方法？另外最好是 CPU 和内存要求低点，ES 之类的我怕钱包吃不消。

我跟 DeepSeek 聊的内容： https://chat.deepseek.com/share/dkuyx547fddet8vwwi 。

最后，再加点我个人的问题，答不答看你心情。这套花活我一个野生程序员能不能把持住？实际会不会很复杂？另外具体的 SQL 我应该肯定是写不明白了，我肯定会让 AI 写。但是我会写测试。我感觉这个测试还是比较好写的，用那种标准的运行两套代码对比结果就行了。一套简单低效几乎不会出错，一套复杂高效可能有错。

[Ketteiron]

过度设计(或更可能是错误设计)。deepseek 没有理解标签搜索是倒排，当然很大程度上被你的第一个提问给误导了。

tag_content_bitmaps 这是倒排索引
content_tag_bitmaps 这是正排索引
你打算通过高频标签 ID 映射解决性能问题，但表一的性能几乎没有改进，影响表一查询的是 content_bitmap 之中的 content_id ，与 tag_id 无关；优化表二没有实际意义。比较有意义的优化，可能是保证 content_id 得是连续的。

性能上限由数据结构决定，如果你想要真正高的性能，需要为特定量级和场景重新设计结构和算法。
为什么要用 roaringbitmap ？因为可以节省下处理高频低频的开发时间拿去做别的事，我觉得你的问题是 XY 问题。

如何保证 ID 映射表的缓存一致性，当一个标签从高频变低频或者反过来，需要迁移数据吗，我觉得你还没想过这些问题。

看了下过往帖子，你应该用 GIN 。先别幻想"如何优化亿级数据"，先把你的程序跑起来。

[shendaowu]

@Ketteiron #1 我才发现我好像是轻信 deepseek 了，它给我的 SQL 代码里根本就没用 tag_content_bitmaps 。它给我解释说它用那个 tag_content_bitmaps 做索引我就信了，没细看。不过没索引查询速度也能达到（标签 id ，内容 id ）复合索引的二十分之一我也是醉了。这个时间的降低好像更麻痹我了。我再去拷问一下 deepseek 。感谢大佬。

[shendaowu]

@Ketteiron #1

我试过数组类型 + GIN 实现标签，效率跟（标签 id ，内容 id ）复合索引比效率好像没差多少。不知道是不是我没弄明白。或者你说的是用 jsonb ？

我也就现在时间多点，后期我就没多少时间开发网站了。后期我要搞钱养活自己，每天不会分出多少时间维护网站。所以我想让网站抗造一点。

[shendaowu]

@shendaowu #2 搜索时间达到（标签 id ，内容 id ）复合索引的二十分之一。不是速度。我怎么就管不住我这手呢？

[shendaowu]

@Ketteiron #1 我问过 deepseek 了，它说 content_tag_bitmaps 不用索引全表扫描也会比（标签 id ，内容 id ）复合索引快。确实快了，某种数据分布只有二十分之一。不同的数据分布指每个内容的标签量，内容量和独立标签量。内容数量和每个内容的标签量的乘积是固定的，独立标签量是内容最大标签量的 1.5 倍。某种数据分布（标签 id ，内容 id ）复合索引 11 秒多，pg_roaringbitmap 598 毫秒。不过我才反应过来这个优势好像没什么用？是我不想用的数据分布。我想用的数据分布 pg_roaringbitmap 好像还比符合索引慢点？我这坑爹的脑子之前怎么没发现这个问题？我用了它给的同时用两个表的 SQL ，还没有单独用一个表的快。不过可能是标签分布的原因。我测试的数据是所有标签的使用率应该都是二分之三。我还要进行很多无聊的性能测试。再次感谢吧，如果没有你的质疑我可能会很晚才发现问题。

[Ketteiron]

@shendaowu #3 如果是单标签，b-tree 可能会比 GIN 快，但取多标签交集复合索引需要多次索引扫描，GIN 只是位运算会快很多，千万量级配合 intarray 只需要几毫秒。如果数据量太少，可能会走全表扫描。另外不知道你是否使用了 intarray 。
GIN 比 RBM 稍慢，原因是 GIN 支持事务一致性。RBM 适合复杂查询，例如 (Tag A OR Tag B) AND (Tag C) AND NOT (Tag D)。如果你的测试只是 count(*) 的话是不公平的，少掉了回表。
如果想要根据 Bitmap 里的内容来过滤行，会碰到这个问题：
https://github.com/ChenHuajun/pg_roaringbitmap/issues/36
RBM 还有个问题，如果标签太多了，如果标签还支持打分、顶/踩，写操作会成为瓶颈。

综合比较的话，RBM 用算法换来了极致的读取速度，适合用在复杂读多、写少的场景
其余场景用 GIN 更好

[shendaowu]

@Ketteiron #6

大佬不想回就不用会了，一直白嫖你的回复挺不好意思的。

大佬你把我 san 值搞低了。因此我基本决定将来把这东西搞到一个单独的服务器里跟其他实验性的功能坐一桌了。我计划主服务器只放置一些核心、稳定的功能。实验性的服务器放置一些非核心、不稳定的功能，并进行名声最不好的那种敏捷开发。

我的需求复杂读确实多，写应该也不少。但是我刚才测试 update 只有五毫秒，这应该不算高吧？ 3333 行，每行的 roaringbitmap 三万元素。元素代表的标签一共 4500 种。deepseek 说一到十秒都正常，我没搜到多少正常。我懒得自己测试所谓的正常更新时间，感觉变数太多。

我没用 intarray 插件，就是普通的 integer[] 类型加个 GIN 索引。我先去测试 intarray 插件的性能了。另外看你的回复我隐约觉得 intarray 只擅长查询全部包含一系列标签的内容。我会测试这个。我之前别的测试完全没测试这个，看来大概率要全重测一遍。

另外附上我计划的一种查询（ AI 生成的）：

WITH target_tags AS (
SELECT tag_bitmap FROM content_tag_bitmaps WHERE content_id = 123 -- 这个忘了让 AI 限制搜索的元素的个数了，不过我记得好像加不加限制速度都差不多。
)
SELECT
content_id,
rb_cardinality(rb_and(tag_bitmap, (SELECT tag_bitmap FROM target_tags))) AS common_tag_count
FROM
content_tag_bitmaps
WHERE
content_id != 123
AND rb_and_cardinality(tag_bitmap, (SELECT tag_bitmap FROM target_tags)) > 0
ORDER BY
common_tag_count DESC
LIMIT 10;

[shendaowu]

@Ketteiron #6

大佬如果你愿意回复的话别回复我 7 楼问的那些问题了。我又改变主意了。我想目前只关注传统的（内容 id ，标签 id ）的实现方式了。更高效的搜索以后再说。因我我发现 deepseek 说 intarray 实现的标签系统也不支持频繁的写入。然后我前面说的后期没多少时间也许可以通过敏捷开发解决一点。实在不行我还有大杀器，公开下载数据，允许用户本地给自己搜索或者帮别人搜索。我基本没有靠这个赚钱的想法，所以应该有更多的应对方法。

我现在主要想知道为了高效实现复杂标签查询，有哪些可以 tradeoff 掉的特性？我问过 deepseek 了，它给了一些东西： https://chat.deepseek.com/share/0nxwlfnzszgo9ps8va 。不知道大佬有没有什么补充的？或者能不能给个比较好的 tradeoff 方案？如果不能的话，我就只能各种方案同时测试试出比较好的方案了。我目前比较看好的是 pg_roaringbitmap 放到从服务器上，然后从服务器可写，deepseek 说从服务器可以写。然后每天夜里自动将前一天更新的（内容 id ，标签 id ）内容批量写入到一个带 roaringbitmap 的表里。deepseek 说可以。

[Ketteiron]

@shendaowu #8 别考虑太多，随便哪个都能用。
世界上最强大、使用人数最多的标签系统，莫过于 ehentai 附带的标签系统
早在十多年前它就支持各种复杂查询
https://ehwiki.org/wiki/Gallery_Searching
php+mysql5 都能撑得起全世界的流量，技术、架构无足轻重，先付诸想法才最重要

而且你整天问 deepseek 怕是对技术提升没有多大帮助，deepseek 基本上只会顺着你的话瞎编，你的任何预设都会成为它的边界
你发的这几个 deepseek 分享链接要么充满正确的废话，要么在瞎编，对于你写代码没有任何帮助

我简单说一点，就算你有三百万个标签，支持所有复杂组合查询(命名空间、与或非、精确/模糊匹配)，就算用 mysql 也没关系，你先找到六位数以上的用户再说。

[shendaowu]

@Ketteiron #9

我之前还真尝试看过 danbooru 的代码。不过我放弃了，看不懂 Ruby ，尝试让 GitHub Copilot 给我找标签搜索相关的代码好像也不太对劲。ehentai 对搜索的标签数量有很大的限制吧？我希望搜索就算不到 100 个最少也得 50 个比较好，而且还是全 OR 连接的比较多。再加上单个内容最大三万标签，我怕这个组合会出现非线性的资源需求增长，就是横纵扩展服务器都难以解决。那样的话初期用户很开心，并且用习惯了的话，后期数据量大了用户变得失望我感觉我的站就会烂掉了。

在这种事上我不太敢乐观，毕竟我没见过类似像我这么用的，而且如果最终出岔子了，我的整个站都可能会出问题。所谓的如果失败的损失很大就悲观，如果失败的损失不大就乐观。公开下载数据这种大杀器我还是不太想用。就算我基本不想赚钱，这招也会有其他的坏处。

ehentai 开源吗？我没搜到。或者有点大致的实现思路的也行，这个我也没搜到。

我之前对 deepseek 的回答一般都是比较怀疑的，不过我以后还是听你的减少问这种设计方面的技术问题吧。但是类似找轮子之类的问题我还是比较信任 deepseek 的，它都不知道的话我自己基本就不会找了。