如何避免多SQL查询中重复时间状态判断逻辑的代码冗余

我的数据库里有20多张表都定义了current_since::timestamptz和current_until::timestamptz列，行的有效性规则如下：

• 若current_since为null，该行是草稿
• 若current_since非null且current_until为null，该行从current_since时刻起永久有效
• 若两列均有值，该行在两个时刻之间有效

每张表都在(current_since, current_until)上建了btree索引。

当前查询方式及性能 #

我需要筛选指定时间（用户自定义或当前时间）的有效行，当前用的条件：

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM record
WHERE
    :timestamp BETWEEN record.current_since AND record.current_until
    OR (
        record.current_since <= :timestamp
        AND record.current_until IS NULL
    )

开启SET enable_seqscan = OFF;、以NOW()作为:timestamp时，执行计划如下：

Bitmap Heap Scan on record  (cost=23.97..42.32 rows=374 width=300) (actual time=0.056..0.246 rows=374 loops=1)
  Recheck Cond: (((now() >= current_since) AND (now() <= current_until)) OR ((current_since <= now()) AND (current_until IS NULL)))
  Filter: (((now() >= current_since) AND (now() <= current_until)) OR ((current_since <= now()) AND (current_until IS NULL)))
  Heap Blocks: exact=9
  Buffers: shared hit=11
  ->  BitmapOr  (cost=23.97..23.97 rows=374 width=0) (actual time=0.040..0.041 rows=0 loops=1)
        Buffers: shared hit=2
        ->  Bitmap Index Scan on idx_current_at  (cost=0.00..11.89 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=1)
              Index Cond: ((current_since <= now()) AND (current_until >= now()))
              Buffers: shared hit=1
        ->  Bitmap Index Scan on idx_current_at  (cost=0.00..11.89 rows=374 width=0) (actual time=0.034..0.034 rows=374 loops=1)
              Index Cond: ((current_since <= now()) AND (current_until IS NULL))
              Buffers: shared hit=1
Planning:
  Buffers: shared hit=4
Planning Time: 0.194 ms
Execution Time: 0.324 ms

代码冗余问题 #

多表关联时，重复写条件会导致代码冗余：

SELECT * FROM record
    LEFT JOIN comment
        ON comment.record_id = record.id
        AND (
           :timestamp BETWEEN comment.current_since AND comment.current_until
            OR (
                comment.current_since <= :timestamp
                AND comment.current_until IS NULL
            )    
        )
    LEFT JOIN attachment
        ON attachment.comment_id = comment.id
        AND (
            :timestamp BETWEEN attachment.current_since AND attachment.current_until
            OR (
                attachment.current_since <= :timestamp
                AND attachment.current_until IS NULL
            )    
        )
WHERE
    :timestamp BETWEEN record.current_since AND record.current_until
    OR (
        record.current_since <= :timestamp
        AND record.current_until IS NULL
    )

我想避免重复写这些条件，后续规则变更时不用逐个修改查询，但不想牺牲性能。时间参数不是固定NOW()，常是用户指定的历史时间。

尝试的存储函数及性能问题 #

我写了一个返回布尔值的存储函数：

CREATE FUNCTION is_current(
    at TIMESTAMP WITH TIME ZONE,
    current_since TIMESTAMP WITH TIME ZONE,
    current_until TIMESTAMP WITH TIME ZONE
)
    RETURNS BOOLEAN
    IMMUTABLE
    LANGUAGE plpgsql
AS
$$
BEGIN
    IF current_since IS NULL THEN
        RETURN FALSE;
    END IF;

    IF current_since <= at AND current_until IS NULL THEN
        RETURN TRUE;
    END IF;

    RETURN at BETWEEN current_since AND current_until;
END
$$;

用函数替代条件的查询：

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM record WHERE
    is_current(:timestamp, record.current_since, record.current_until)

但性能暴跌，执行计划：

Seq Scan on record  (cost=10000000000.00..10000000107.17 rows=125 width=300) (actual time=27.852..28.052 rows=374 loops=1)
  Filter: is_current(now(), current_since, current_until)
  Buffers: shared hit=9
Planning Time: 0.117 ms
JIT:
  Functions: 2
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 0.400 ms, Inlining 3.039 ms, Optimization 16.067 ms, Emission 8.698 ms, Total 28.204 ms
Execution Time: 28.509 ms

附加问题 #

存储函数性能暴跌是不是因为它阻止查询执行器直接访问列，导致频繁调用函数处理布尔结果？

1. 附加问题解答 #

是的，你的判断完全正确。PostgreSQL的查询优化器无法“看透”PL/pgSQL函数内部的逻辑，没办法把函数调用拆解成能利用索引的列条件。它只能对每一行调用一次函数做判断，这就导致了全表扫描（Seq Scan），完全用不上你建的(current_since, current_until)索引，性能自然暴跌。

另外，虽然你标记了函数为IMMUTABLE，但PL/pgSQL函数本身的执行开销也比原生SQL表达式大，每一行都要触发函数调用、执行内部的IF分支，这也是性能下降的原因之一。

2. 替代方案：用SQL函数（而非PL/pgSQL） #

把函数改成SQL函数，优化器可以内联函数逻辑，将其转化为原生SQL表达式，这样就能正常使用索引了：

CREATE OR REPLACE FUNCTION is_current(
    at TIMESTAMP WITH TIME ZONE,
    current_since TIMESTAMP WITH TIME ZONE,
    current_until TIMESTAMP WITH TIME ZONE
)
RETURNS BOOLEAN
IMMUTABLE
LANGUAGE sql
AS $$
SELECT
    current_since IS NOT NULL
    AND current_since <= at
    AND (current_until IS NULL OR at <= current_until);
$$;

这个SQL函数的逻辑和你之前的PL/pgSQL函数完全一致，但优化器可以直接把函数调用展开成对应的条件，和你手动写的条件效果一样，能正常使用索引。

测试一下这个函数的查询执行计划，应该会和你手动写条件时的计划一致，性能不会下降。

3. 代码冗余的解决 #

使用上面的SQL函数后，多表关联查询可以简化成：

SELECT * FROM record
    LEFT JOIN comment
        ON comment.record_id = record.id
        AND is_current(:timestamp, comment.current_since, comment.current_until)
    LEFT JOIN attachment
        ON attachment.comment_id = comment.id
        AND is_current(:timestamp, attachment.current_since, attachment.current_until)
WHERE
    is_current(:timestamp, record.current_since, record.current_until)

后续如果有效性规则变更，只需要修改is_current函数的定义，所有用到该函数的查询都会自动生效，不用逐个修改。

内容的提问来源于stack exchange，提问作者Jan Klan