postgresql - 连接具有潜在 NULL 值的多个列的性能

Question

假设我们有下表

CREATE TABLE my_table
(
    record_id SERIAL,
    column_1 INTEGER,
    column_2 INTEGER,
    column_3 INTEGER,
    price NUMERIC
);

有以下数据

INSERT INTO my_table (column_1, column_2, column_3, price) VALUES
(1,    NULL,    1,    54.99),
(1,    NULL,    1,    69.50),
(NULL, 2,       2,    54.99),
(NULL, 2,       2,    69.50),
(3,    3,       NULL, 54.99),
(3,    3,       NULL, 69.50);

现在我们做类似的事情

CREATE TABLE my_table_aggregations AS
SELECT
    ROW_NUMBER() OVER () AS aggregation_id,
    column_1,
    column_2,
    column_3
FROM my_table
GROUP BY 
    column_1,
    column_2,
    column_3;

我现在要做的是为my_table中的每个record_id分配一个aggregation_id。现在因为我有 NULL 值，所以我不能简单地通过 t1.column_1 = t2.column_1 加入，因为 NULL = NULL 是 NULL，所以加入将排除这些记录。

现在我知道我应该使用这样的东西

SELECT
    t.record_id,
    agg.aggregation_id
FROM my_table t
JOIN my_table_aggregations agg ON 
(
    ((t.column_1 IS NULL AND agg.column_1 IS NULL) OR t.column_1 = agg.column_1) AND
    ((t.column_2 IS NULL AND agg.column_2 IS NULL) OR t.column_2 = agg.column_2) AND
    ((t.column_3 IS NULL AND agg.column_3 IS NULL) OR t.column_3 = agg.column_3)
);

这里的问题是我正在处理数以亿计的记录，并且在连接中使用 OR 似乎需要很长时间才能运行。

还有一个替代方案，就是这样

SELECT
    t.record_id,
    agg.aggregation_id
FROM my_table t
JOIN my_table_aggregations agg ON 
(
    COALESCE(t.column_1, -1) = COALESCE(agg.column_1, -1) AND
    COALESCE(t.column_2, -1) = COALESCE(agg.column_2, -1) AND
    COALESCE(t.column_3, -1) = COALESCE(agg.column_3, -1)
);

但问题是我假设这些列中没有任何值是 -1。

请注意，这是一个示例，我很清楚我可以使用 DENSE_RANK 来获得相同的结果。因此，让我们假装这不是一个选项。

是否有一些非常棒的方法可以绕过必须使用 COALESCE 但保持其性能优于使用 OR 的正确方法？我运行测试，COALESCE 比 OR 快 10 倍以上。

我在 Greenplum 数据库上运行它，所以我不确定这种性能差异在标准 Postgres 数据库上是否相同。

Answer 1

由于我的 NULLIF 解决方案存在性能问题，而您使用 COALESCE 的速度要快得多，我想知道您是否可以尝试调整该解决方案来处理 -1 的问题。为此，您可以尝试强制转换以避免错误匹配。我不确定性能会受到什么影响，但它看起来像:

SELECT
    t.record_id,
    agg.aggregation_id
FROM my_table t
JOIN my_table_aggregations agg ON 
(
    COALESCE(cast(t.column_1 as varchar), 'NA') =
        COALESCE(cast(agg.column_1 as varchar), 'NA') AND
    COALESCE(cast(t.column_2 as varchar), 'NA') =
        COALESCE(cast(agg.column_2 as varchar), 'NA') AND
    COALESCE(cast(t.column_3 as varchar), 'NA') =
        COALESCE(cast(agg.column_3 as varchar), 'NA')
);