Início / dba / Perguntas / 240518
Accepted
Arya
Asked: 2019-06-14 10:24:59 +0800 CST

índice para ILIKE no postgresql, desempenho inconsistente

  • 772

Tenho uma tabela com cerca de 64 milhões de linhas, criei o seguinte índice na address_urlcoluna

CREATE INDEX trgm_idx_address_link
    ON public.address_link USING gin
    (address_url COLLATE pg_catalog."default" gin_trgm_ops)
    TABLESPACE pg_default;

O servidor não está sob nenhuma carga no momento. E estou testando o desempenho do like.

Digamos que temos a seguinte consulta

SELECT *
FROM "address_link"
WHERE "address_url" ILIKE '%eden-hill%'
LIMIT 1

Às vezes, se eu executar isso pela primeira vez, pode levar 10 segundos. Se eu o executar novamente, ele será concluído em 0,195 segundos

Eu então criei um índice btree na mesma coluna para ver se ajudaria

CREATE INDEX address_link_address_url
    ON public.address_link USING btree
    (address_url COLLATE pg_catalog."default")
    TABLESPACE pg_default;

Mas não ajudou muito. Às vezes, a primeira vez que pesquiso uma palavra-chave leva 2 segundos, a segunda leva 0,02 segundos. Existe alguma coisa que eu possa fazer para tornar o desempenho mais consistente?

postgresql postgresql-11

2 respostas

  1. Best Answer

    Diferentes partes do índice precisam ser examinadas com base em quais trigramas estão presentes no padrão LIKE e, em seguida, diferentes partes da tabela precisam ser examinadas para verificar novamente o padrão para falsos positivos e para obter as outras colunas selecionadas para o verdadeiro positivos.

    A primeira vez para um determinado padrão, ele precisa buscar grandes partes do índice e da tabela do disco. Na segunda vez consecutiva com o mesmo padrão, esses dados já estão armazenados em cache na memória da vez anterior e, portanto, serão muito mais rápidos.

    Os detalhes disso podem ser vistos fazendo explain (analyze, buffers)a consulta, e especialmente fazendo isso comset track_io_timing=on

    Se você tiver RAM suficiente para armazenar em cache o índice inteiro e a tabela inteira na RAM, poderá usar pg_prewarm para colocá-los todos na memória antecipadamente, para que você não precise fazer isso aos poucos.

    • 2

  2. Para acelerar consultas 'like', você pode usar um índice varchar_pattern_ops(ou ).text_pattern_ops

    Aqui está a sintaxe:

    CREATE INDEX address_link_address_url_like
    ON public.address_link (address_url varchar_pattern_ops);

    Você pode achar esta página de documentação útil.

    • 1

relate perguntas