含算术运算符的PostgreSQL查询性能优化咨询

先看你的执行计划，问题很明显：当前计划是先全量扫描table_a的时间戳索引（扫了188976行），然后对每一行去table_b做嵌套循环查询，但table_b里几乎所有行都被过滤掉了（每循环一次都移除1行，最后返回0行）。这种“先扫大表再过滤小表”的逻辑完全搞反了，才导致耗时这么久。

你之前创建的单个表达式索引没起作用，是因为查询里的过滤条件是两个表达式的AND组合，而且查询的关联逻辑是基于b_id的，单独的单值索引无法被优化器用来同时满足关联+过滤的需求。下面是具体的优化步骤：

1. 调整执行计划的关联顺序：先过滤table_b再关联table_a #

我们可以强制优化器先找到符合过滤条件的table_b记录，再去关联table_a，这样能大幅减少循环次数。另外注意到你的where条件会自动过滤掉table_b为null的行，所以左外连接等价于内连接，可以直接替换：

select ta.id, ta.b_id, ta.timestamp
from table_a ta
join (
    select b_id
    from table_b
    where 
        coalesce( (cast("ok" ->> 'bar' as int) + cast("ko" ->> 'bar' as int)), 0) > 0
        and (cast("total" ->> 'bar' as int) - coalesce( (cast("ok" ->> 'bar' as int) + cast("ko" ->> 'bar' as int)), 0)) > 0
) tb on ta.b_id = tb.b_id
order by ta.timestamp desc
fetch next 25 rows only;

2. 创建针对性的联合索引 #

为了让子查询里的table_b过滤更快，应该创建包含b_id和两个过滤表达式的联合索引，让优化器能直接通过索引找到符合条件的b_id，不需要回表扫描全量数据：

-- 优先按过滤条件筛选，再取b_id
create index idx_table_b_bar_filter on table_b (
    coalesce( (cast("ok" ->> 'bar' as int) + cast("ko" ->> 'bar' as int)), 0),
    (cast("total" ->> 'bar' as int) - coalesce( (cast("ok" ->> 'bar' as int) + cast("ko" ->> 'bar' as int)), 0)),
    b_id
);

如果b_id的选择性本身很高，也可以把b_id放在索引前缀：

create index idx_table_b_bid_bar_filter on table_b (
    b_id,
    coalesce( (cast("ok" ->> 'bar' as int) + cast("ko" ->> 'bar' as int)), 0),
    (cast("total" ->> 'bar' as int) - coalesce( (cast("ok" ->> 'bar' as int) + cast("ko" ->> 'bar' as int)), 0))
);

3. 预计算表达式值（高频查询可选） #

如果这个查询是高频执行的，你可以在table_b里新增两个计算列，把JSON解析后的计算值预存起来，这样索引会更高效，查询也不需要每次都计算JSON字段：

-- 添加自动维护的计算列
alter table table_b add column bar_total_ok_ko int 
generated always as (coalesce( (cast("ok" ->> 'bar' as int) + cast("ko" ->> 'bar' as int)), 0)) stored;

alter table table_b add column bar_remaining int 
generated always as (cast("total" ->> 'bar' as int) - coalesce( (cast("ok" ->> 'bar' as int) + cast("ko" ->> 'bar' as int)), 0)) stored;

-- 创建计算列的联合索引
create index idx_table_b_bar_calculated on table_b (bar_total_ok_ko, bar_remaining, b_id);

然后修改查询语句使用计算列：

select ta.id, ta.b_id, ta.timestamp
from table_a ta
join table_b tb on ta.b_id = tb.b_id
where tb.bar_total_ok_ko > 0 and tb.bar_remaining > 0
order by ta.timestamp desc
fetch next 25 rows only;

数据库会自动维护计算列的值，查询时直接用预存结果，性能会有明显提升。

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