主页 / dba / 问题 / 217087
Accepted
Erwin Brandstetter
Asked: 2018-09-09 07:56:16 +0800 CST

为什么 *not* 错误：索引行大小 xxxx 超过索引“foo”的最大值 2712？

  • 772

我们一再看到尝试索引值超过最大大小的列失败。Postgres 10 有这样的错误信息：

ERROR:  index row size xxxx exceeds maximum 2712 for index "foo_idx"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
       Consider a function index of an MD5 hash of the value, or use full text indexing.

例子：

等等。

现在，a_horse_with_no_name 演示了一个具有更大text值（10000 个字符）的案例，它似乎仍然适用UNIQUE于 Postgres 9.6 中的索引。引用他的测试用例：

create table tbl (col text);
create unique index on tbl (col);

insert into tbl
values (rpad(md5(random()::text), 10000, md5(random()::text)));

select length(val) from x;  -- 10000

没有错误，并且列值确实测试了 10000 个字符的长度。

最近是否发生了变化，或者这怎么可能？

postgresql index

1 个回答

  1. Best Answer

    简短的回答：压缩。

    默认情况下，数据类型text允许（无损！）压缩和存储：

    SELECT typstorage FROM pg_type WHERE typname = 'text';  -- 'x'

    手册关于pg_type.typstorage：

    p: Value must always be stored plain.
e: Value can be stored in a “secondary” relation (if relation has one, see pg_class.reltoastrelid).
m: Value can be stored compressed inline.
x: Value can be stored compressed inline or stored in “secondary” storage.

    x是可烘烤类型的通常选择。请注意，m值也可以移出到辅助存储，但只能作为最后的手段（e 并且x首先移动值）。

    用pg_column_size()代替 进行测试length()。确保测试实际表列（应用压缩）而不仅仅是输入值。看：

    CREATE TABLE tbl (id int, col text);
INSERT INTO tbl(id, col) VALUES 
   (1, rpad(md5('non_random'::text),     100, md5('non_random'::text)))
 , (2, rpad(md5('non_random'::text),    1000, md5('non_random'::text)))
 , (3, rpad(md5('non_random'::text),   10000, md5('non_random'::text)))
 , (4, rpad(md5('non_random'::text),  100000, md5('non_random'::text)))
 , (5, rpad(md5('non_random'::text),  500000, md5('non_random'::text)))
 , (6, rpad(md5('non_random'::text), 1000000, md5('non_random'::text))); 

SELECT id, left(col, 10) || ' ...' AS col
     , length(col) AS char_length
     , pg_column_size(col) AS compressed
     , pg_column_size(col || '') AS uncompressed
FROM   tbl ORDER BY id; 

    id | col            | char_length | compressed | uncompressed
---+----------------+-------------+------------+-------------
 1 | 67ad0f29fa ... |         100 |        101 |          104
 2 | 67ad0f29fa ... |        1000 |       1004 |         1004
 3 | 67ad0f29fa ... |       10000 |        160 |        10004
 4 | 67ad0f29fa ... |      100000 |       1191 |       100004
 5 | 67ad0f29fa ... |      500000 |       5765 |       500004
 6 | 67ad0f29fa ... |     1000000 |      11487 |      1000004

    SELECT pg_column_size(rpad(md5('non_random'::text), 1000000, md5('non_random'::text)));

    pg_column_size
--------------
       1000004

    db<>在这里摆弄

    请注意如何使用 noop 表达式强制将值从其存储格式中解压缩：pg_column_size(col || '')。

    第 5 行太大而无法容纳索引元组（即使有压缩）并触发标题中的错误消息。

    第 6 行会太大，甚至无法容纳索引页面并触发相关的错误消息：

    错误：索引行需要 11504 字节，最大大小为 8191

    生成的测试值rpad()具有重复模式，可以进行大规模压缩。即使是很长的琴弦也很容易适应最大值。这样压缩后的大小。

    有关的：

    长答案

    我进行了更广泛的测试，篡改了存储内部结构以验证我的理解。仅用于测试目的！

    dbfiddle 不允许对系统目录进行写访问。但是查询是为了“在家”尝试。

    • 16

相关问题