Início / dba / Perguntas / 336156
Accepted
Justin Lowen
Asked: 2024-02-23 05:18:40 +0800 CST

PostgreSQL aplicando restrições dependentes de ordem em uma consulta

  • 772

Existe uma prática recomendada para impor a ordem em que as condições de pesquisa são aplicadas? Vimos algumas consultas em que o otimizador reordenou as operações e passou linhas por meio de uma condição e/ou função de pesquisa inesperada e inválida.

Por exemplo:

SELECT * 
FROM table_1
join table_2 on table_1.primary_id = table_2.primary_id
join table_3 on table_2.secondary_id = table_3.secondary_id
WHERE table_3.value is json parseable and table_3.value->>'foo' = bar

Às vezes, essa consulta é otimizada de forma que a pesquisa JSON aconteça antes de garantir que o valor seja analisável em JSON.

Uma situação semelhante pode ocorrer durante a coerção dos tipos:

SELECT * 
FROM table_1
join table_2 on table_1.primary_id = table_2.primary_id and table_2.category_name = 'Foo'
join table_3 on table_2.secondary_id = table_3.secondary_id
-- first do regex to ensure we get a parseable int, then check range
-- column is stored as text as it can be mixed with other non-numerical inputs
WHERE table_3.value ~ E'^\\d{1,9}$' and table_3.value::int < :max_allowed_value_setting;

Parece que a condição de pesquisa de coerção de tipo pode ser reordenada antes da condição de pesquisa regex e/ou que os valores que devem ser filtrados table_2.category_name = 'Foo'são passados ​​primeiro pela conversão de número inteiro porque a ordem de junção foi alterada de table_1 -> table_2 -> table_3para table_1 -> table_3 -> table_2.

Sei que o otimizador de consulta pode reorganizar algumas coisas, mas ainda não tenho certeza de quando isso é aplicável e quando pode causar um problema. O otimizador não tem conhecimento das condições de pesquisa que dependem umas das outras, o que pode levar a erros durante a execução.

Quais partes de uma instrução SQL no PostgreSQL podem ser reorganizadas e existe uma prática recomendada (ou melhor) para lidar com esses tipos de cenários?

Editar: atualize a terminologia para usar condições de pesquisa em vez de restrições

postgresql

3 respostas

  1. Best Answer

    Você pode colocar uma barreira do otimizador:

    WITH q AS MATERIALIZED (
   SELECT ...
   WHERE table_3.value is json parseable
)
SELECT ...
FROM q
WHERE q.value->>'foo' = bar
    • 1

  2. Ao contrário de C, onde o operador "&&" implica avaliação de curto-circuito em uma ordem específica, não existe tal coisa em SQL e o otimizador é livre para avaliar suas condições em qualquer ordem.

    Para evitar a avaliação de uma expressão sob certas condições você pode usar CASE:

    CREATE TABLE foo( t TEXT );
INSERT INTO foo VALUES ('123'),('hello');
SELECT t, CASE WHEN t~E'^\\d{1,9}$' THEN t::INT ELSE NULL END from foo;
   t   | case
-------+------
 123   |  123
 hello | Null
    • 1

  3. O estilo de pensamento "Se, Mas, Talvez e A menos ..." não é bom para os bancos de dados lidarem.
    Se um campo deve conter JSON, ele sempre deverá conter JSON, mesmo que seja uma construção vazia, ou deverá ser NULL, para indicar adequadamente a ausência de um valor significativo.

    Se você puder garantir que table_3.value sempre conterá JSON, poderá descartar completamente a cláusula "é json analisável" - suponho que a pesquisa JSON retornará falso em vez de gerar um erro.

    SELECT * 
FROM table_1
JOIN table_2 ON table_1.primary_id = table_2.primary_id
JOIN table_3 ON table_2.secondary_id = table_3.secondary_id
WHERE table_3.value->>'foo' = bar ;

    Se isso não funcionar, você poderá forçar o banco de dados a fazer uma parte da consulta antes da outra, mas apenas como último recurso.

    SELECT * 
FROM 
( SELECT 
    table_1.x
  , table_2.y
  , table_3.value as value  
  , . . . 
  FROM table_1
  JOIN table_2 ON table_1.primary_id = table_2.primary_id
  JOIN table_3 ON table_2.secondary_id = table_3.secondary_id
  WHERE table_3.value IS JSON PARSEABLE 
) t0 
WHERE t0.value->>'foo' = bar ;
    • 1

relate perguntas