PostgreSQL关联交易动作表多条件匹配查询优化问题

你可以把两个过滤条件合并到同一个聚合查询中，仅需扫描一次transactionaction表即可完成所有校验，写法如下：

SELECT * from transaction
WHERE transactionid in (
    SELECT transactionid
    FROM public.transactionaction
    group by transactionid
    having array_agg(actiontype) @> array[2,3]
       AND BOOL_OR(actiontype = 1 AND value is not null)
)

这里用到的BOOL_OR是聚合函数，只要分组内有任意一条记录满足条件就会返回true，刚好匹配「存在value非空的动作1」的需求。

更灵活的扩展写法 #

如果后续需要新增更多带value校验的动作规则，可以把所有动作统一转换为唯一标识后做数组包含校验，不用额外新增聚合条件，示例如下（当前需求是同时满足：value非空的动作1、动作2、动作3）：

SELECT * from transaction
WHERE transactionid in (
    SELECT transactionid
    FROM public.transactionaction
    group by transactionid
    having array_agg(
        CASE
            WHEN actiontype = 1 AND value IS NOT NULL THEN '1_not_null'
            WHEN actiontype = 1 AND value IS NULL THEN '1_null'
            ELSE actiontype::VARCHAR
        END
    ) @> array['1_not_null','2','3']
)

高可读性写法 #

如果不熟悉数组包含语法，可以用FILTER子句拆分每个校验条件，逻辑更直观：

SELECT * from transaction
WHERE transactionid in (
    SELECT transactionid
    FROM public.transactionaction
    group by transactionid
    having COUNT(*) FILTER (WHERE actiontype = 2) > 0
       AND COUNT(*) FILTER (WHERE actiontype = 3) > 0
       AND COUNT(*) FILTER (WHERE actiontype = 1 AND value IS NOT NULL) > 0
)

以上方案都避免了多次扫描同一张表，配合你已创建的actiontype索引，执行效率会比原写法更高。

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