Início / dba / Perguntas / 261149
Accepted
Pawel Zieminski
Asked: 2020-03-04 16:06:50 +0800 CST

Índice parcial do Postgres btree no array jsonb -> expressão do array parece estar corrompida para tabelas maiores na versão 9.5.x

  • 772

Estou analisando a indexação de atributos jsonb e estou vendo algo suspeito com o Postgres 9.5.x, mas não em versões superiores. Abaixo está o que eu fiz que acionou os erros de consulta estranhos. Pode ser que eu esteja fazendo algo errado, mas ver este trabalho nas versões mais recentes do Postgres me faz pensar que é um bug no 9.5.x (eu tentei até a versão 9.5.21).

Estou vendo isso consistentemente com o tamanho da tabela de cerca de 1 milhão de linhas e superior.

O json na coluna jsonb contém atributos que representam diferentes tipos de json de valor único e matrizes. Eu tenho string, boolean, number integer, number float e data formatada string. O erro que vejo é com <o operador array para array inteiro (não tentei todos eles). A partir do erro, parece que a column -> 'attribute'parte da expressão falha ao recuperar a parte correta do valor jsonb e, digamos, para uma matriz int obtém a matriz de string próxima etc. Isso realmente muda entre as execuções, pois os dados são aleatórios.

A estrutura do json na propertiescoluna é fixa (determinística) para cada valor de typecolumn . Portanto, cada linha onde type = 8 sempre tem uma matriz de inteiros em properties -> 'r'. type = 7tem a matriz em properties -> 'q', type = 9tem a matriz em properties -> 's', etc. Em outras palavras, typeé um tipo lógico em termos de estrutura (ou "esquema") de json em propertiese todas as linhas com o mesmo typevalor têm estrutura json homogênea em termos de nomes de nós e tipos de valor (os próprios valores são aleatórios). Também agora as matrizes são sempre de comprimento 3.

Isso é um inseto? Ou estou fazendo algo errado?

CREATE TABLE test1 (
  id SERIAL PRIMARY KEY,
  type INTEGER NOT NULL,
  properties jsonb
);

-- generates test data wherein the json structure of "properties" column varies by "type" column
INSERT INTO test1 (type, properties)
SELECT
  s.type AS type,
  json_build_object(CHR(s.type + 100), md5(random() :: TEXT),
                    CHR(s.type + 101), (random() * 100)::INTEGER,
                    CHR(s.type + 102), (random() * 10)::DOUBLE PRECISION,
                    CHR(s.type + 103), random()::INTEGER::BOOLEAN ,
                    CHR(s.type + 104), to_char(to_timestamp((random() * 1500000000)::DOUBLE PRECISION), 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'),
                    CHR(s.type + 105), ARRAY[md5(random() :: TEXT), md5(random() :: TEXT), md5(random() :: TEXT)],
                    CHR(s.type + 106), ARRAY[(random() * 100)::INTEGER, (random() * 100)::INTEGER, (random() * 100)::INTEGER],
                    CHR(s.type + 107), ARRAY[(random() * 10)::DOUBLE PRECISION, (random() * 10)::DOUBLE PRECISION, (random() * 10)::DOUBLE PRECISION],
                    CHR(s.type + 108), ARRAY[random()::INTEGER::BOOLEAN, random()::INTEGER::BOOLEAN, random()::INTEGER::BOOLEAN],
                    CHR(s.type + 109), ARRAY[
                      to_char(to_timestamp((random() * 1500000000)::DOUBLE PRECISION), 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'),
                      to_char(to_timestamp((random() * 1500000000)::DOUBLE PRECISION), 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'),
                      to_char(to_timestamp((random() * 1500000000)::DOUBLE PRECISION), 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')
                    ]
  ) AS properties
FROM (SELECT (random() * 10) :: INT AS type
      FROM generate_series(1, 1000000)) s;

CREATE OR REPLACE FUNCTION jsonb_array_int_array(JSONB)
  RETURNS INTEGER[] AS
$$
DECLARE
  result INTEGER[];
BEGIN
  IF $1 ISNULL
  THEN
    result := NULL;
  ELSEIF jsonb_array_length($1) = 0
  THEN
    result := ARRAY [] :: INTEGER[];
  ELSE
    SELECT array_agg(x::INTEGER) FROM jsonb_array_elements_text($1) t(x) INTO result;
  END IF;
  RETURN result;
END;
$$
  LANGUAGE plpgsql
  IMMUTABLE;

--  properties -> 'r' field of type 8 is always an array of integers
CREATE INDEX test1_properties_r_int_array_index ON test1 USING btree (jsonb_array_int_array(properties -> 'r')) WHERE type = 8;

-- this works
SELECT count(*) FROM test1 WHERE type = 8 AND jsonb_array_int_array(properties -> 'r') < ARRAY[50];

-- this fails
SELECT count(*) FROM test1 WHERE type = 8 AND jsonb_array_int_array(properties -> 'r') < ARRAY[100];

-- but
DROP INDEX test1_properties_r_int_array_index;
-- now it works
SELECT count(*) FROM test1 WHERE type = 8 AND jsonb_array_int_array(properties -> 'r') < ARRAY[100];

-- also
CREATE INDEX test1_properties_r_int_array_index ON test1 USING gin (jsonb_array_int_array(properties -> 'r')) WHERE type = 8;

-- works here too
SELECT count(*) FROM test1 WHERE type = 8 AND jsonb_array_int_array(properties -> 'r') < ARRAY[100];

Obrigado pela ajuda.

Editar:

Aqui estão alguns esclarecimentos sobre como ele falha. Acabei de reexecutar o acima e a consulta falha da seguinte forma

sql> SELECT count(*) FROM test1 WHERE type = 8 AND jsonb_array_int_array(properties -> 'r') < ARRAY[100]
[2020-03-04 00:46:20] [22P02] ERROR: invalid input syntax for integer: "1.73782130237668753"
[2020-03-04 00:46:20] Where: SQL statement "SELECT array_agg(x::INTEGER) FROM jsonb_array_elements_text($1) t(x)"
[2020-03-04 00:46:20] PL/pgSQL function jsonb_array_int_array(jsonb) line 12 at SQL statement

Eu verifiquei o valor aleatório da mensagem de erro

SELECT id AS txt FROM test1 WHERE position('1.73782130237668753' IN properties::text) > 0;

e descobri que a linha que causou o erro na verdade é typeigual a 7 e não 8 como na cláusula where da consulta. Portanto, parece que a condição do índice não é satisfeita na linha que está sendo retornada.

Aqui está o plano para a consulta com falha

Aggregate  (cost=69293.65..69293.66 rows=1 width=0)
  ->  Bitmap Heap Scan on test1  (cost=1228.78..69208.38 rows=34111 width=0)
        Recheck Cond: ((jsonb_array_int_array((properties -> 'r'::text)) < '{100}'::integer[]) AND (type = 8))
        ->  Bitmap Index Scan on test1_properties_r_int_array_index  (cost=0.00..1220.25 rows=34111 width=0)
              Index Cond: (jsonb_array_int_array((properties -> 'r'::text)) < '{100}'::integer[])

Edição 2:

Após a resposta de Laurenz Albe, realizei o seguinte teste. Eu defini uma nova função

CREATE OR REPLACE FUNCTION jsonb_array_int_array2(json_value JSONB, actual_type INTEGER, expected_type INTEGER)
  RETURNS INTEGER[] AS
$$
DECLARE
  result INTEGER[];
BEGIN
  IF actual_type <> expected_type THEN
    RAISE EXCEPTION 'unexpected type % instead of %', actual_type, expected_type;
  END IF;

  IF $1 ISNULL OR actual_type <> expected_type
  THEN
    result := NULL;
  ELSEIF jsonb_array_length(json_value) = 0
  THEN
    result := ARRAY [] :: INTEGER[];
  ELSE
    SELECT array_agg(x::INTEGER) FROM jsonb_array_elements_text(json_value) t(x) INTO result;
  END IF;
  RETURN result;
END;
$$
  LANGUAGE plpgsql
  IMMUTABLE;

Eu redefini o índice e reestruturei a consulta da seguinte forma

CREATE INDEX test1_properties_r_int_array_index ON test1 USING btree (jsonb_array_int_array2(properties -> 'r', type, 8)) WHERE type = 8;

SELECT count(*) FROM test1 WHERE type = 8 AND jsonb_array_int_array2(properties -> 'r', type, 8) < ARRAY[100];

E agora eu recebo

[2020-03-04 09:47:34] [P0001] ERROR: unexpected type 7 instead of 8

O que indica que uma etapa é executada em todas as linhas, não apenas naquelas em que type = 8. É talvez isso do plano

Recheck Cond: ((jsonb_array_int_array((properties -> 'r'::text)) < '{50}'::integer[]) AND (type = 8))

Se esta for a ordem da avaliação é possível reverter e verificar type = 8antes jsonb_array_int_array((properties -> 'r'::text)?

Também pelo desempenho (uma vez que eu removo a verificação de exceção e executo novamente), parece que toda a tabela é verificada.

Isso é esperado?

Edição 3:

Percebi que isso agora se tornou uma pergunta diferente e a excelente e detalhada resposta de Laurenz Albe aborda a questão original de "por que não funciona". A questão agora é como trabalhar melhor o esquema original que eu estava procurando. Acho que vou ter que destilá-lo em uma pergunta separada.

Obrigada!

Aliás, como Laurenz previu, consegui reproduzir o problema no Postgres 10.x com mais dados.

Edição 4:

Para o registro, isso não é específico para matrizes. Qualquer conversão de valores neste cenário irá eventualmente falhar com tabelas grandes. Então, dado que properties ->> 'm'é sempre um número inteiro quando type = 8isso também não é seguro

CREATE INDEX test1_properties_m_int_index ON test1 (((properties ->> 'm')::INTEGER)) WHERE type = 8;

e a consulta

SELECT count(*) FROM test1 WHERE type = 8 AND (properties ->> 'm')::INTEGER < 50;

falha com

[2020-03-05 09:35:24] [22P02] ERROR: invalid input syntax for integer: "["a1c815126aa058706476b21f37f60038", "450513bd0f25abf8bd39b1b4645a1427", "e51acc579414985eaa59d9bdc3dc8187"]"

A lição aqui é que, se o esquema json não estiver fixo na coluna da tabela, seja qual for a conversão feita, ela deverá antecipar qualquer entrada jsonb durante varreduras indiscriminadas de partes da tabela.

postgresql index

1 respostas

  1. Best Answer

    Essa é uma pergunta interessante, então vou tentar dar uma boa resposta.

    Resumindo, o problema é a definição de sua função, que faz suposições infundadas sobre o tipo de objeto JSON com o qual tem que lidar.

    Explicação do erro:

    O erro que se obtém ao executar seu exemplo não é determinístico; depende dos números aleatórios no seu exemplo. Eu recebo isso, por exemplo:

    ERROR:  cannot get array length of a non-array
CONTEXT:  PL/pgSQL function jsonb_array_int_array(jsonb) line 6 at IF

    Mas a causa é a mesma.

    Observe que seu plano de execução usa um Bitmap Index Scan . Ou seja, o PostgreSQL constrói um bitmap na memória que indica quais linhas da tabela satisfazem a condição de índice. A segunda etapa, o Bitmap Heap Scan , acessa as linhas reais da tabela.

    Você pode imaginar que esse bitmap consome memória. Agora, a quantidade de memória para um bitmap é limitada pelo parâmetro de configuração work_mem. Se work_memfor muito pequeno para conter um bitmap que contenha um bit por linha da tabela, o PostgreSQL degradará parcialmente para um " bitmap com perdas " que contém apenas um bit por bloco de 8 KB, indicando se o bloco contém uma linha correspondente ou não. Você pode ver isso na EXPLAIN (ANALYZE)saída, mas não no seu caso, porque a consulta falha.

    Se você tiver um bitmap com perdas, todas as linhas em um bloco indicado pelo bitmap devem ser verificadas novamente para filtrar os falsos positivos, então sua função é chamada para argumentos que não estão no índice .

    O erro é causado por

    ELSEIF jsonb_array_length($1) = 0

    ou

    SELECT array_agg(x::INTEGER) FROM jsonb_array_elements_text($1) t(x) INTO result;

    Ambas as linhas assumem que o argumento é uma jsonbmatriz e a segunda tenta converter os elementos da matriz em integer. O erro real que você obtém depende da linha que é processada por jsonb_array_int_array.

    O problema como tal não está conectado a uma determinada versão do PostgreSQL, e é uma coincidência que você o veja na 9.5. Talvez algo tenha mudado ao lidar com o work_memlimite ou quando um bitmap fica com perdas, talvez os números aleatórios sejam diferentes.

    Prova da minha teoria:

    Aumente work_meme você verá que o erro desaparece magicamente, porque o bitmap resultante não apresenta mais perdas.

    Solução:

    Altere sua função para que ela não falhe para valores JSON que não sejam matrizes de inteiros.

    • 3

relate perguntas