Início / dba / Perguntas / 334044
Accepted
sujeet
Asked: 2023-12-14 10:46:08 +0800 CST

Indexando coluna JSON inteira versus indexando campo JSON específico no PostgreSQL

  • 772

Eu tenho uma tabela PostgreSQL nomeada item_logcom uma logcoluna do tipo jsonbque contém vários atributos. Um desses atributos é delete_log. Estou consultando esta tabela com base no created_atcampo e na presença de non-nullvalores no delete_logatributo.

SELECT item_id
FROM item_log
WHERE created_at >= CURRENT_DATE - INTERVAL '15 days'
  AND (log->>'delete_log') IS NOT NULL;

Estou considerando diferentes estratégias de indexação para otimizar minhas consultas:

Aqui está o que eu tentei,

Índice criado emcreated_at : A consulta ficou mais rápida quando não coloquei (log->>'delete_log') IS NOT NULLna consulta a condição esperada. Assim que coloquei essa condição ou mesmo selecionei esse campo demorou em torno de 40 minutos.

Acho que a lentidão da consulta ocorre devido à inclusão do delete_log. Quando este campo é consultado, ele inicia a leitura da TOASTtabela e realiza deTOASToperações, o que aumenta significativamente o tempo de I/O.

Agora, para otimizar a consulta, tenho algumas opções:

  • Adicionar índice composto em(created_at, log->>delete_log)
  • Adicionar índice (log->>delete_log)porque já tenho índice ativado created_at?

Além disso, tenho outra confusão.

  • Que tipo de índice devo escolher log->>delete_log?
  • E como isso difere da indexação de todo o logcampo em termos de desempenho ou armazenamento para a consulta acima?
postgresql

2 respostas

  1. Best Answer

    Indexar todo o JSON com um índice GIN não ajudaria muito na sua consulta.

    O melhor índice é um índice parcial que indexa apenas as linhas que possuem um valor diferente de NULL delete_log:

    CREATE INDEX ON item_log (created_at) WHERE log->>'delete_log' IS NOT NULL;

    Esse índice é pequeno e não precisa ser modificado para linhas que não atendem à WHEREcondição. A desvantagem é que esse índice pode ser útil apenas para essa consulta única e você deseja o menor número possível de índices em uma tabela que recebe muitos INSERTs. Portanto, se você tiver outras consultas nessa tabela, a melhor solução geral seria usar um índice menos específico que não seja a escolha perfeita para a consulta em questão, mas que também possa suportar suas outras consultas.

    • 2

  2. O índice GIN no jsonb é grande e não pode ser usado para idxcol->> 'a' IS NOT NULLconsultas. Você pode querer isso por outros motivos, mas não fará nada para esta consulta.

    O índice composto btree (created_at, (log->>delete_log))é bom. Isso evitará a necessidade de deTOAST os dados (desde que a lista de seleção seja exatamente o que você mostra). Não será particularmente eficiente dentro do índice, porque nenhuma das colunas é testada quanto à igualdade simples; portanto, será necessário verificar toda a parte do índice que atende à condição de desigualdade, removendo individualmente as linhas que falham em IS NOT NULL. Mas se tudo o que você precisa fazer é evitar o deTOAST, será suficiente.

    Índices separados em (created_at)e ((log->>delete_log))podem funcionar, sendo usados ​​combinando com uma operação BitmapAnd. Mas é provável que o planejador deixe de fazer isso. O problema é que o planejador não leva em consideração o custo do deTOAST, então se o único ponto de incluir o índice extra for evitar o deTOAST, ele pode não se preocupar com isso. Da mesma forma, se você tiver os dois índices (created_at, (log->>delete_log))e (created_at)talvez não veja o valor em usar o maior dos dois, é melhor evitar ter esse índice de coluna única, pois é um incômodo atraente.

    • 1

relate perguntas