Início / dba / Perguntas / 325832
Accepted
Macchiatow
Asked: 2023-04-10 23:41:39 +0800 CST

Índice multichave no Postgres

  • 772

Estou lutando para otimizar a criação correta do índice para minha estrutura de dados. Estou tentando indexar uma grande quantidade de dados (~ 100B registros) do tipo de dados json:

{
  "email": "[email protected]",
  "first_domain": "q.w.e.r.t.y.com"
  "second_domain": "a.s.d.f.g.h.j.com"
}

Uma consulta simples pode parecer

SELECT * 
from table 
WHERE email = '[email protected]'

OU

SELECT * 
from table 
WHERE first_domain LIKE '%.r.t.y.com'
   OR second_domain LIKE '%.r.t.y.com'

Anteriormente, eu tokenizaria todos os domínios e os colocaria juntos em uma matriz no mongo

{
  "values": ["[email protected]", "moc.y.t.r.e.w.q", moc.j.h.g.f.d.s.a]
}

Mas, tendo uma quantidade de dados, o Mongo não pode armazenar todos os valores na memória e o desempenho de gravação diminui.

Então, estou planejando tentar o índice Postgres e GIN. Por favor, sugira-me uma criação de índice adequada para tal situação.

postgresql-performance

1 respostas

  1. Best Answer

    A primeira consulta seria escrita corretamente assim:

    select *
from the_table
where the_json_column ->> 'email' = '[email protected]'

    Isso pode ser suportado com um índice B-Tree:

    create index on the_table ((the_json_column ->> 'email'))

    Como a condição LIKE usa o caractere curinga no lado esquerdo, você precisará de um índice de trigrama (após instalar a pg_trgmextensão)

    create index on the_table using gin ((the_json_column ->> 'first_domain') gin_trgm_ops);
create index on the_table using gin ((the_json_column ->> 'second_domain') gin_trgm_ops);

    Novamente, você precisa usar a sintaxe correta para extrair os valores da coluna JSON:

    SELECT * 
from the_table 
WHERE the_json_column ->> 'first_domain' LIKE '%.r.t.y.com'
   OR the_json_column ->> 'second_domain' LIKE '%.r.t.y.com'
    • 1

relate perguntas