Error: index row size exceeds btree version 4 maximum for index
错误 #
🌐 Error
1index row size exceeds btree version 4 maximum 2704 for index "idx_name"总结 #
🌐 Summary
PG 对 B 树元组（=行）的大小有上限。至少需要在一个 8Kb 页上存放 3 个 B 树元组。这是不能改变的。
🌐 PG has a limit on a B-tree tuple(=row) size. It needs to fit at least 3 B-tree tuples on a 8Kb page. That could not be changed.
B 树行可以是单个属性，也可以是多个属性。这些情况最好分别处理。
🌐 B-tree row can be a single attribute or multiple attributes. These cases are better addressed separately.
B 树是由多个属性构建的 #
🌐 B-tree is built with multiple attributes
具有多个属性的B树只有在可能的SELECT查询使用多个属性，并且这些属性包含索引中的第一个属性时，性能才会优于单独几个属性。也就是说，可以按第1、第2、第3属性查询，但不能按第2、第3和第5个索引属性查询。
🌐 B-tree with multiple attributes will perform better than several only in case the likely SELECT queries use several attributes that include the first attributes that are in the index. I.e. select by 1-st, 2-nd, 3-d but not by 2-nd, 3-d and 5-th index attributes.
当多属性 B 树适合的另一种情况是，当我们的 INSERT/UPDATE 工作量可与 SELECT 工作量相当时（通常 SELECT 还是更频繁）。这样我们可以通过在 INSERT/UPDATE 时只更新一个索引而不是多个索引来提升速度，但代价是 SELECT 性能下降。
🌐 The other case when multiple attributes B-tree is good is when we have INSERT/UPDATE workload that is comparable to SELECT load (generally SELECTS a way more often). Then we can save speed-up updating a single index instead of several at INSERT/UPDATE at cost of SELECT performance decrease.
但很可能我们有多个属性的B树索引是因为某些自动化工具，而不是故意的。即使没有提到的错误，最好还是为其中的每个属性单独建立单属性索引。然后删除多属性的B树索引。当我们遇到这个错误时，这是必须的，也是唯一的解决办法。
🌐 But most likely we have multiple attributes B-tree index due to some automation tool, not by intention. Even without the mentioned error it's best to build separate single-attribute indexes for each attribute from it. Then drop multiple attributes B-tree index. This is a must and the only solution when we have this error though.
B 树是建立在一个非常长的单一属性上的 #
🌐 B-tree is built on a single attribute that is very long
如果索引是建立在文本、JSON 列等上，也可能会这样。虽然在这些数据类型上建立 B 树并不被禁止，但效果也不好。为什么呢？
🌐 This can be if the index is built on text, JSON column etc. It's not prohibited to build B-tree on these datatypes, but it's also ineffective. Why?
衡量索引效率的一个方法是索引条目数量与该数据类型所有可能值空间宽度的比值。比如，如果索引中有 100000 个不同的 int32 值，那么比值就是 1/40000。如果我们有长度为 2704 字节（B 树索引的最大值）的文本，我们几乎无法想象需要多少不同的值才能得到一个可比的比值。也就是说，为如此长的值建立索引会在索引中存储大量冗余信息。
🌐 One of the measures of index efficiency is the ratio of index entries to the width of all possible values space for this datatype. If we have say 100000 distinct values of int32 in the index then the ratio is 1/40000. If we have text with length of 2704 bytes (maximum for B-tree index) we can hardly imagine the number of distinct values that gives us even a comparable ratio. That said indexing of that long values stores much redundancy in the index.
解决方法：使用某种哈希将值转换到更小的空间。例如 md5。建立一个函数索引（=按表达式索引）:
🌐 The solution: use some kind of hashing to transfer the values to much narrower space. I.e. md5. Build a functional index (=index by expression):
1create index on table_name (MD5(column_name));而不是：
🌐 instead of:
1create index on table_name (column_name);那么你必须修改你的 SELECT 语句，使其使用相同的函数（否则函数索引在 SELECT 查询中不会被使用）。也就是说，
🌐 Then you must modify you SELECTs to be using the same function (otherwise the functional index will not be used in SELECT queries). I.e.
1select * from table_name where MD5(column_name) = MD5('search_value');而不是
🌐 instead of
1select * from table_name where column_name = 'search_value';对于一些支持部分包含查询的非文本数据类型——比如检查 JSON 中是否存在某个键值对，或者实现 tsvector 短语搜索——可以使用 GiST 或 GIN 索引。这些索引类型作用于比被索引内容更窄的值空间。
🌐 For some non-text datatypes that support queries by partial inclusion—such as checking whether a key-value pair exists in JSON, or implementing tsvector phrase search—use GiST or GIN indexes. These index types operate on a narrower value space than the full content being indexed.