postgresql Postgres case语句优化

xwmevbvl 于 11个月前发布在 PostgreSQL

关注(0)|答案(1)|浏览(161)

考虑下表，其中每列都有值数组
| G1| G2| G3|......这是什么？|G20|
| --|--|--|--|--|
| 一、三、四|二四五|二、一、五|......这是什么？|二十七、四、五|
| 一、八、四|三六一八|八四五|......这是什么？|十七、四、五|
| 一、九、二|一、四、五|二、六、八|......这是什么？|七、四、三十|
现在，给定一个输入数组，我想获取所有列上交叉点最多的一行。候选行允许的最大不匹配数量作为查询的输入。
我当前的查询如下所示

select * from 
(select 
    (CASE when (g1 @> <input_arr1>) then 0 else 1 end +
     CASE when (g2 @> <input_arr2>) then 0 else 1 end +
     CASE when (g3 @> <input_arr3>) then 0 else 1 end 
     ...
     CASE when (g20 @> <input_arr4>) then 0 else 1) as mismatches
)as t where t.mismatches < 3;

字符串
现在我们知道了允许的最大不匹配数，一旦我们知道不匹配达到最大限制，是否有办法停止进一步的交集操作。例如，如果g1，g2，g3不相交，我们知道mismatches已经是3，在这一点上，有没有一种方法可以避免对更多的case语句进行求值。我知道求值发生在select子句上，在那里我们不能添加条件break。但只是想知道我们是否可以通过重新设计查询或表来实现这一点。

postgresql

来源：https://stackoverflow.com/questions/77684133/postgres-case-statement-optimization

1条答案

按热度按时间

wgx48brx1#

你可以将case表达式放入where子句中，然后使用一个限制，例如：

WITH input_arrays AS (
    SELECT 
        array[1,3,4] AS input_arr1, 
        array[2,4,5] AS input_arr2, 
        array[1,8,4] AS input_arr3, 
        array[1,4,5] AS input_arr4
)
SELECT *
FROM your_data, input_arrays
WHERE 
    CASE WHEN g1  @> input_arr1 THEN 0 ELSE 1 END 
  + CASE WHEN g2  @> input_arr2 THEN 0 ELSE 1 END 
  + CASE WHEN g3  @> input_arr3 THEN 0 ELSE 1 END 
  + CASE WHEN g20 @> input_arr4 THEN 0 ELSE 1 END < 3
LIMIT 1;

字符串
参见this fiddle
然而，这可能无法实现预期的“突破”

QUERY PLAN
Limit  (cost=0.00..0.15 rows=1 width=256)
  ->  Seq Scan on your_data  (cost=0.00..25.60 rows=173 width=256)
        Filter: ((((CASE WHEN (g1 @> '{1,3,4}'::integer[]) THEN 0 ELSE 1 END + CASE WHEN (g2 @> '{2,4,5}'::integer[]) THEN 0 ELSE 1 END) + CASE WHEN (g3 @> '{1,8,4}'::integer[]) THEN 0 ELSE 1 END) + CASE WHEN (g20 @> '{1,4,5}'::integer[]) THEN 0 ELSE 1 END) < 3)

型
一个程序性的方法可能会起作用，例如：

CREATE OR REPLACE FUNCTION find_matching_row(
    input_arr1 INT[],
    input_arr2 INT[],
    input_arr3 INT[],
    input_arr4 INT[]
)
RETURNS INT AS $$
DECLARE
    result_row your_data%ROWTYPE;
BEGIN
    FOR result_row IN SELECT * FROM your_data
    LOOP
        IF      (result_row.g1 @> input_arr1 AND
                result_row.g2 @> input_arr2 AND
                result_row.g3 @> input_arr3 AND
                result_row.g20 @> input_arr4) THEN
            CONTINUE; -- Skip to the next iteration if the condition is not met
        END IF;

        -- If the code reaches here, a row is a "mismatch"
        RETURN 1;
    END LOOP;

    RETURN 0; -- No row met the criteria
END;
$$ LANGUAGE plpgsql;

型
但是游标方法可能比查询方法慢，并且您可能会失去灵活性。
参见this fiddle
如果在where子句中不使用case表达式，你可以使用布尔逻辑，如下所示。注意，这种性质的每个表达式g1 @> input_arr1返回true或false，因为你想要不匹配，那么你需要一个false变成true，这样就可以通过NOT(g1 @> input_arr1)实现-所以如果所有4个输入数组都不匹配，那么所有 predicate 都是true：

WITH input_arrays AS (
    SELECT 
        array[1,3,4] AS input_arr1, 
        array[2,4,5] AS input_arr2, 
        array[1,8,4] AS input_arr3, 
        array[1,4,5] AS input_arr4
)
SELECT *
FROM your_data, input_arrays
WHERE NOT(g1   @> input_arr1) 
  AND NOT(g2   @> input_arr2)
  AND NOT(g3   @> input_arr3)
  AND NOT(g20  @> input_arr4)
LIMIT 1;

型

赞(0）回复(0）举报 11个月前

我来回答

postgresql Postgres case语句优化

1条答案

相关问题

热门标签

最新问答