PostgreSQL使用参数时忽略Null输入值的写法优化问题

你的判断完全正确：两种写法逻辑完全等价，你推荐的($1 IS NULL OR 列 = $1)写法可读性远高于CASE嵌套的写法，是目前业界通用的可选参数过滤入门写法。

除了这个写法之外，还有两类更优的方案，可以根据你的使用场景选择：

1. 极简写法（适合小表、对性能要求不高的场景） #

如果你的过滤字段有NOT NULL约束（你给出的样表中isactive、column_a都符合这个要求），可以进一步简化为COALESCE的写法：

SELECT
  tbl_id,
  column_a,
  isactive
FROM tbl
WHERE isactive = COALESCE($1, isactive)
  AND column_a = COALESCE($2, column_a)

如果过滤字段允许为NULL，可以用PostgreSQL专属的IS NOT DISTINCT FROM运算符兼容NULL值匹配：

SELECT
  tbl_id,
  column_a,
  isactive
FROM tbl
WHERE isactive IS NOT DISTINCT FROM COALESCE($1, isactive)
  AND column_a IS NOT DISTINCT FROM COALESCE($2, column_a)

这种写法比OR的写法更简洁，但是可读性稍弱，适合熟悉SQL语法的团队内部使用。

2. 高性能写法（适合大表、对查询延迟敏感的场景） #

前面提到的OR写法、COALESCE写法都属于「固定SQL结构」的实现，查询优化器会生成一个通用的执行计划适配所有参数传入情况，无法针对具体传入的参数选择最优索引，在数据量较大的时候会出现性能问题。
这种场景下最优的方案是动态拼接SQL，根据传入的参数动态生成WHERE条件：
举个PostgreSQL中存储过程里动态SQL的样例：

CREATE OR REPLACE FUNCTION query_tbl(p_isactive bool DEFAULT NULL, p_column_a text DEFAULT NULL)
RETURNS SETOF tbl AS $$
DECLARE
  v_sql text := 'SELECT tbl_id, column_a, isactive FROM tbl WHERE 1=1';
BEGIN
  IF p_isactive IS NOT NULL THEN
    v_sql := v_sql || format(' AND isactive = %L', p_isactive);
  END IF;
  IF p_column_a IS NOT NULL THEN
    v_sql := v_sql || format(' AND column_a = %L', p_column_a);
  END IF;
  RETURN QUERY EXECUTE v_sql;
END;
$$ LANGUAGE plpgsql;

如果是在应用层拼接SQL，注意一定要用参数绑定的方式传入值，不要直接拼接字符串，避免SQL注入风险。这种写法每次执行的SQL都是仅包含必要过滤条件的固定结构，优化器可以选择对应字段的索引，性能比固定结构的写法高1~2个数量级。

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