Início / dba / Perguntas / 162582
Accepted
Dave
Asked: 2017-01-30 13:24:39 +0800 CST

Existe uma maneira mais eficiente de pesquisar um registro, mas apenas se ele tiver associações?

  • 772

Estou usando o Postgres 9.5. Quero selecionar alguns registros, mas somente se eles tiverem pelo menos uma associação em outra tabela. Eu cheguei a esta consulta:

select distinct m.name, m.id 
from machines m, parts rt 
where m.id = rt.machine_id 
  and m.name like '%Name%';

mas não acho que isso seja particularmente eficiente, pois se houver 50.000 associações, acho que todas estão carregadas e depois "distintas", o que parece um pouco ineficiente. Existe uma maneira mais eficiente de verificar o carregamento de um registro, mas apenas se ele tiver uma ou mais associações?

postgresql performance

1 respostas

  1. Best Answer

    A forma como sua consulta está estruturada, você pode apenas escrevê-la usando uma EXISTSexpressão:

    SELECT
    m.name, m.id 
FROM
    machines m
WHERE
    m.name LIKE '%Name%'
    AND EXISTS (SELECT * 
                  FROM parts rt
                 WHERE rt.machine_id = m.id
               ) ;

    Se você deseja que a consulta seja eficiente, leve em consideração que a parte mais difícil provavelmente será verificar a máquina name. Considere indexar machines.nameusando um arquivo trigram index. Os índices trigramas permitem LIKE '%something%'condições de localização rápida.

    CREATE EXTENSION pg_trgm;  -- Only needed if extension not already installed
CREATE INDEX name_like_idx 
    ON machines 
    USING GIST (name gist_trgm_ops);

    Muito provavelmente, o planejador de banco de dados fará um plano de execução onde primeiro encontrará as máquinas que namesão LIKE '%Name%' (usando o name_like_idxíndice), então, para cada máquina neste subconjunto, ele verificará se há partes associadas a eles; se assim for, eles serão parte do resultado.

    Por questões de eficiência, assume-se também que existe um índice em parts.machine_id, pois será assim que o PostgreSQL verificará eficientemente a existência da parte. Se machine_idfor uma chave estrangeira , é sempre aconselhável indexá-la.

    Esta consulta não precisa DISTINCT(assumindo que os ids já são únicos).

    • 2

relate perguntas