Início / dba / Perguntas / 342353
Accepted
Mykola Shved
Asked: 2024-09-14 01:41:07 +0800 CST

Por que o PostgreSQL está lendo do heap e como melhorar o uso do cache?

  • 772

Tenho uma consulta SQL que executa lentamente no PostgreSQL 13.15 com 128 GB de memória. A consulta executa principalmente um Bitmap Heap Scan, e notei que muitas leituras estão vindo do heap em vez do cache. Aqui está um link para a consulta e o plano de execução.

Um índice que aparece frequentemente no plano se parece com este:

create index ix_contacts__addresses_value__normalized
    on contacts__addresses using gin (company_id, field_name, field_id, value__normalized);

Minhas configurações de memória:

•   shared_buffers = 32GB
•   work_mem = 64MB
•   effective_cache_size = 96GB
•   maintenance_work_mem = 1320MB

Questões:

  1. Por que a consulta está lendo tanto do heap?
  2. Como posso configurar o PostgreSQL para usar melhor a memória (shared_buffers, work_mem e outras configurações) para evitar leituras de heap desnecessárias?
  3. Devo considerar modificar os índices para reduzir o tempo de execução?

Eu apreciaria muito qualquer conselho sobre como otimizar o cache e o desempenho geral.

postgresql

1 respostas

  1. Best Answer

    Suas duas primeiras perguntas não fazem sentido, pelos motivos descritos por mustaccio.

    Para sua terceira pergunta, índices GIN multicolunas não são como índices btree multicolunas. Cada coluna tem que ser manipulada individualmente, e então o resultado combinado internamente. Então, em vez de pular apenas para as linhas que atendem a todas as 4 condições, ele primeiro precisa fazer listas de todas as linhas que atendem a cada uma das condições separadas e então determinar quais linhas estão em todas as quatro listas. Isso dá muito trabalho se qualquer uma das condições não for muito seletiva.

    Provavelmente você seria melhor atendido por um índice btree multicolunas em vez de (company_id, field_name, field_id), e então possivelmente um índice GIN em vez de apenas value__normalized, embora eu suspeite que o último não seja realmente necessário.

    Mas pode ser melhor incorporar o JSONB em um índice btree expressional como (company_id, field_name, field_id, (value__normalized->>'country')). Isso exigiria que você reescrevesse a consulta para testar com ->>em vez de com @>.

    • 0

relate perguntas