如何在不重复条件的前提下优化PostgreSQL横向查询

你的orders表需要筛选出存在quantity或product_id为NULL的订单，并列出具体问题类型。原查询结果正确但触发全表扫描，手动加WHERE子句又会重复条件，这里提供两种无需重复条件且能利用索引的改写方式：

方法一：用EXISTS过滤目标行 #

select o.id, o.customer_id, issues.issue
from orders o
where exists (
  select 1
  where o.quantity is null or o.product_id is null
)
cross join lateral (
  select 'missing_quantity' as issue where o.quantity is null
  union all
  select 'missing_product_id' where o.product_id is null
) issues;

• WHERE EXISTS子句会先筛选出存在问题的订单，PostgreSQL可以借助已有的orders_quantity_idx和orders_product_id_idx索引快速定位这些行，执行高效的位图堆扫描或索引扫描，避免全表遍历。
• 横向子查询部分完全保留原逻辑，不需要重复写判断条件，保证代码简洁性的同时，能为每个订单列出对应的所有问题类型。

方法二：用数组收集问题类型 #

select o.id, o.customer_id, unnest(issues) as issue
from orders o
cross join lateral (
  select array_remove(
    array[
      case when o.quantity is null then 'missing_quantity' end,
      case when o.product_id is null then 'missing_product_id' end
    ],
    null
  ) as issues
) t
where array_length(t.issues, 1) > 0;

• 用数组和CASE表达式收集当前订单的所有问题类型，array_remove移除数组中的NULL值，再通过WHERE子句过滤掉没有问题的订单。
• 同样无需重复条件，PostgreSQL会自动利用索引过滤符合条件的行，避免全表扫描。

你可以用EXPLAIN ANALYZE执行上述查询，确认执行计划中是否使用了目标索引，验证扫描效率的提升。

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