Início / dba / Perguntas / 157944
Accepted
Zilk
Asked: 2016-12-13 10:54:10 +0800 CST

Por que o PostgreSQL não usa um índice quando OR é usado nas condições?

  • 772

Estrutura do banco de dados resumida:

  • a tabela principal é cases(cerca de 136k linhas)
  • cada caso pode ter 0 - n linhas de referência na tabelacase_contacts
  • cada contato de caso faz referência a um contato principal na tabelacontacts
  • um contato de caso também pode fazer referência a um subcontato secundário, também na tabelacontacts
  • os contatos têm seus nomes em contacts.v_fullname, que é indexado com um índice trigrama

O objetivo é encontrar casos em que o nome de um contato ou subcontato contenha a string "teste":

SELECT  c.id,
        c.number
  FROM  cases c
        JOIN case_contacts caco ON caco.case_id = c.id
        JOIN contacts con_main ON con_main.id = caco.contact_id
        LEFT JOIN contacts con_sub ON con_sub.id = caco.subcontact_id
 WHERE  con_main.v_fullname ILIKE '%test%'
        OR con_sub.v_fullname ILIKE '%test%'

Esta consulta ( plano de consulta ) retorna o resultado correto, mas não utiliza o índice do trigrama. Demora cerca de 330ms.

Remover qualquer uma das condições de correspondência ( plano de consulta ) ou fazer com que apontem para a mesma tabela ( plano de consulta ) remove o problema de desempenho. Ambos usam o índice do trigrama e são executados em menos de 1ms, mas não resolvem a tarefa dada.

Como posso fazer com que o PostgreSQL use meu índice?

Simplifiquei este exemplo ao mínimo necessário para demonstrar o efeito. A consulta real é muito mais complexa (e parcialmente gerada automaticamente), portanto, usar uma UNION de duas consultas com apenas uma correspondência de texto cada seria muito difícil, se é que é possível.

Estou usando o PostgreSQL 9.5.5.
O esquema ainda está aberto para modificações (até certo ponto).

Conforme solicitado, mais informações sobre os índices:

dbname=# \di+ *contacts*
                                         List of relations
 Schema |               Name               | Type  | Owner |     Table     |  Size
--------+----------------------------------+-------+-------+---------------+---------
 public | case_contacts_case_id_idx        | index | x     | case_contacts | 4544 kB
 public | case_contacts_contact_id_idx     | index | x     | case_contacts | 4544 kB
 public | case_contacts_id_case_id_idx     | index | x     | case_contacts | 4544 kB
 public | case_contacts_idx                | index | x     | case_contacts | 9608 kB
 public | case_contacts_pkey               | index | x     | case_contacts | 4544 kB
 public | case_contacts_reference_trgm_idx | index | x     | case_contacts | 4960 kB
 public | case_contacts_subcontact_id_idx  | index | x     | case_contacts | 4544 kB
 public | case_contacts_type_idx           | index | x     | case_contacts | 6208 kB
 public | case_contacts_unique_types_idx   | index | x     | case_contacts | 5464 kB
 public | contacts_parent_id_id_idx        | index | x     | contacts      | 456 kB
 public | contacts_parent_id_idx           | index | x     | contacts      | 360 kB
 public | contacts_pkey                    | index | x     | contacts      | 360 kB
 public | contacts_v_fullname_trgm_idx     | index | x     | contacts      | 1560 kB
(13 rows)

É assim que o índice em contacts.v_fullname é criado:

CREATE INDEX contacts_v_fullname_trgm_idx ON contacts USING GIN (v_fullname gin_trgm_ops);
postgresql index

1 respostas

  1. Best Answer

    Não posso realmente responder à sua pergunta, porque realmente não sei por que , mas encontrei uma maneira de fazer o PostgreSQL fazer mais ou menos o que acho que você deseja. Eu testei sua situação com um cenário de simulação simplificado e usando o PostgreSQL 9.6.1 (o mais recente até hoje). Eu obtenho os mesmos resultados.

    A boa notícia é: se você pode alterar a maneira como faz sua consulta, tem algumas opções que usam o índice do trigrama.

    A primeira consiste em mover a condição nos subcontatos. Nesse caso, o índice do trigrama é usado para uma das situações (mas não para a outra):

    SELECT
    c.id, c.number
FROM  
    cases c
    JOIN case_contacts caco ON caco.case_id = c.id
    JOIN contacts con_main ON con_main.id = caco.contact_id
    LEFT JOIN 
    (
        SELECT
            * 
        FROM
            contacts  
        WHERE
            v_fullname ilike '%test%' 
    ) AS con_sub ON con_sub.id = caco.subcontact_id
WHERE  
    con_main.v_fullname ILIKE '%test%'
    or con_sub.id is not null /* if the left join gave an answer, it's got '%test%' */ ;

    Muito poucos testes com dados simulados (onde aprox. 0,1%, 2,5%, 5% ou 25% do v_fullname contém '%test%') mostram que a diferença nos tempos de execução é minúscula. [Meu disco é SSD, um HD real pode se comportar de forma bem diferente.] Na verdade, isso deveria ser verificado com um sistema real com dados reais... mas parece que usar o índice trigrama ou não, não faz muita diferença.

    O PostgreSQL não é excepcionalmente bom em estimar quantas linhas aparecerão pesquisando "como '%test%'", mas parece não importar qual plano decidir usar.

    Existe outra opção, que (com minha pequena experiência) funciona um pouco mais rápido na maioria dos casos, e muito mais rápido quando a porcentagem de '%test%' é baixa. Esta opção significa usar um CTE para "pré-filtrar" os contatos (e usa o índice do trigrama uma vez, porque não precisa usar duas vezes):

    WITH filtered_contacts AS
(
SELECT
    *
FROM
    contacts
WHERE
    v_fullname ilike '%test%'
)
SELECT
    c.id, c.number
FROM  
    cases c
    JOIN case_contacts caco ON caco.case_id = c.id
    JOIN filtered_contacts con_main ON con_main.id = caco.contact_id
    LEFT JOIN filtered_contacts con_sub ON con_sub.id = caco.subcontact_id
WHERE
    con_main.v_fullname ILIKE '%test%'
    or con_sub.id is not null /* we need this test again, or we'll miss rows */ ;
    • 2

relate perguntas