Início / dba / Perguntas / 206884
Accepted
Vérace
Asked: 2018-05-17 03:35:06 +0800 CST

Melhor abordagem para aplicar uma longa lista de padrões LIKE?

  • 772

Como um seguimento a esta pergunta , eu tenho um de minha autoria.

A pergunta original diz respeito ao uso de uma CASEinstrução com > de 100 opções e a instrução deve ser usada em 4 lugares - então obviamente o SQL é bastante complicado. A pergunta do OP dizia respeito ao SQL Server 2012, minha pergunta, no entanto, é sobre o PostgreSQL.

Na minha resposta , propus o uso de a VIEWcomo uma solução "one-stop-shop" - ou seja, declare o VIEWuma vez, use-o em qualquer lugar - e isso se aplica a qualquer consulta no futuro também e a qualquer variante dela.

Outro pôster (@AndriyM) propôs o uso de a CROSS APPLYpara resolver o problema, que é outra solução. A sintaxe do PostgreSQL éJOIN LATERAL

Em seguida, adicionei um CTE ( Common Table Expression ) à minha resposta original como outra solução possível.

Então, o OP agora tem 5 opções:

  1. CASE
  2. VIEW
  3. JOIN LATERAL( CROSS APPLYpara SQL Server)
  4. CTE
  5. Separate table

Excluí a opção de alterar os dados subjacentes, pois, frequentemente neste fórum, consultores/DBAs/programadores não têm permissão para alterar os dados subjacentes - torna as respostas mais interessantes também!

Obviamente, uma CASEexpressão com > 100 opções (x4) é terrivelmente complicada e complexa - mas quando é uma boa ideia usar CASEe em que ponto ela se torna menos em vez de mais?

Na minha opinião (e não apenas porque é a minha resposta!), a VIEWé a solução ideal - é simples, funcionará para todos os RDBMS e é permanente e funcionará para todas as consultas agora e no futuro, caso o OP deseje modificar a consulta .

A JOIN LATERALconstrução funcionará também como uma espécie de tabela derivada, que é praticamente o que a CTEtambém é. Ambos podem ser usados ​​na mesma linha na mesma consulta.

Qual das 5 abordagens é melhor/melhor e em que ponto a técnica (facilidade de uso, velocidade, otimização do plano de consulta) se inclina a favor da solução específica?

postgresql performance

1 respostas

  1. Best Answer

    Eu usaria uma tabela de tradução em uma LATERALsubconsulta . Demonstração (Postgres 10+):

    CREATE TABLE ac_translate (
   ord_nr int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
 , like_pattern text NOT NULL
 , target text NOT NULL
);

INSERT INTO ac_translate(like_pattern, target) VALUES 
   ('AIR NEW Z%'       , 'AIR NEW ZEALAND')  -- rows in order of precedence!
 , ('AIR BP%'          , 'AIR BP')
 , ('ADDICTION ADVICE%', 'ADDICTION ADVICE')
 , ('AIA%'             , 'AIA')
;

    Ver:

    Manipule ord_nrpara ajustar as prioridades.

    Consulta:

    SELECT COALESCE(act.target, ac.accountName) AS accountname
     , SUM(ac.charge_amount) AS gstexcl
FROM   account_code ac
LEFT   JOIN LATERAL (
   SELECT a1.target
   FROM   ac_translate a1
   WHERE  ac.accountname LIKE a1.like_pattern
   ORDER  BY a1.ord_nr
   LIMIT  1
   ) act ON true
GROUP BY 1;

    Ou com uma subconsulta correlacionada :

    SELECT COALESCE(
        (SELECT a1.target
         FROM   ac_translate a1
         WHERE  ac.accountname LIKE a1.like_pattern
         ORDER  BY a1.ord_nr
         LIMIT  1), ac.accountName) AS accountname
     , SUM(ac.charge_amount) AS sum_amount
FROM   account_code ac
GROUP BY 1;

    Isso é fácil de manusear, mantém a longa lista de opções fora do código e a coloca em uma tabela onde pode ser mantida adequadamente. E é moderadamente rápido.

    Não podemos usar facilmente um plain LEFT JOIN ac_translatejá que CASEatravessa padrões para retornar o single , first match. Não podemos simplesmente juntar a um conjunto, que pode retornar várias correspondências se um padrão for o prefixo do outro, como 'AIR%' e 'AIR N%'. Portanto, usamos um número de pedido na tabela de tradução para priorizar correspondências na subconsulta.

    A ELSEcláusula na pergunta referenciada resolve para o valor original. Isso é implementado com COALESCEaqui. Basicamente, isso combina as virtudes das duas principais respostas por lá.

    Para completar, entrei GROUP BY 1como outra maneira de evitar repetir expressões longas (o que não é mais necessário aqui). Ver:

    Velocidade

    O desempenho se deteriora com o número de linhas na tabela de tradução, pois o Postgres é forçado a percorrer todas elas sequencialmente e avaliar a LIKEexpressão. Se isso não for mais rápido o suficiente, precisamos de index support , mas a expressão não é "sargable" - a expressão que precisamos indexar está à direita do operador e não há COMMUTATORfor LIKE. Detalhes:

    Há uma solução alternativa, no entanto. Meu exemplo requer que os padrões tenham pelo menos 3 caracteres principais ( 3 é minha escolha arbitrária). Adicione uma CHECKrestrição na tabela de tradução para impor essa regra e um índice de expressão no trigrama inicial:

    CREATE INDEX ac_translate_left_idx ON ac_translate (left(like_pattern, 3));

    Adapte a consulta:

    SELECT COALESCE(act.target, ac.accountName) AS accountname
     , SUM(ac.charge_amount) AS gstexcl
FROM   account_code ac
LEFT   JOIN LATERAL (
   SELECT a1.target
   FROM   ac_translate a1
   WHERE  left(ac.accountname, 3) = left(a1.like_pattern, 3)
   AND    ac.accountname LIKE a1.like_pattern
   ORDER  BY a1.ord_nr
   LIMIT  1
   ) act ON true
GROUP BY 1;

    Com linhas suficientes na tabela de tradução (e estimativas favoráveis ​​e configurações de custo), o Postgres usará uma varredura de índice muito rápida para reduzi-la aos poucos candidatos (se houver) e filtrar apenas o restante com a LIKEexpressão. Deve escalar bem. Eu adicionei a EXPLAINsaída ao violino como prova de conceito:

    db<>fique aqui

    • 6

relate perguntas