Início / dba / Perguntas / 217087
Accepted
Erwin Brandstetter
Asked: 2018-09-09 07:56:16 +0800 CST

Por que *não* ERRO: o tamanho da linha do índice xxxx excede o máximo de 2712 para o índice "foo"?

  • 772

Temos visto repetidamente tentativas fracassadas de indexar colunas com valores que excedem um tamanho máximo. O Postgres 10 tem esse tipo de mensagem de erro:

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.

Exemplos:

etc.

Agora, a_horse_with_no_name demonstrou um caso com valores muito maiores text(10.000 caracteres) que ainda parece funcionar com um UNIQUEíndice no Postgres 9.6. Citando seu caso de teste:

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

Nenhum erro e o valor da coluna realmente testado com um comprimento de 10.000 caracteres.

Houve mudanças recentes ou como isso é possível?

postgresql index

1 respostas

  1. Best Answer

    Resposta curta: compressão.

    O tipo de dados textpermite compactação (sem perdas!) e armazenamento fora de linha por padrão:

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

    O manual sobre 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é a escolha usual para tipos torrados. Observe que mos valores também podem ser movidos para o armazenamento secundário, mas apenas como último recurso ( e e xos valores são movidos primeiro).

    Teste com pg_column_size()em vez de length(). Certifique-se de testar as colunas reais da tabela (com compactação aplicada) e não apenas os valores de entrada. Ver:

    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<>fique aqui

    Observe como o valor é forçado a ser descompactado de seu formato de armazenamento com a expressão noop: pg_column_size(col || '').

    A 5ª linha seria muito grande para caber na tupla do índice (mesmo com compactação) e acionaria a mensagem de erro no título.

    A 6ª linha seria grande demais para caber até mesmo na página de índice e acionar a mensagem de erro relacionada:

    ERRO: a linha do índice requer 11504 bytes, o tamanho máximo é 8191

    Os valores de teste gerados com rpad()padrões de repetição, que permitem compactação massiva. Mesmo cordas muito longas ainda se encaixam facilmente no max. tamanho após a compressão desta forma.

    Relacionado:

    Resposta longa

    Executei testes mais extensos, adulterando os internos de armazenamento para verificar meu entendimento. Apenas para fins de teste!

    dbfiddle não permite acesso de gravação aos catálogos do sistema. Mas as consultas estão aí para tentar "em casa".

    • 16

relate perguntas