PostgreSQL聚合FILTER表达式为何无法使用索引？

你遇到的这个情况确实是PostgreSQL 9.5版本优化器的一个固有局限，并非操作失误或bug。咱们把问题拆解开来聊：

先回顾FILTER的实用特性 #

FILTER子句的核心优势是能在单个查询里完成多维度的过滤聚合，把过滤逻辑嵌入聚合函数而非依赖全局WHERE，比如Django文档里的示例：

SELECT 
  count('id') FILTER (WHERE account_type=1) as regular, 
  count('id') FILTER (WHERE account_type=2) as gold, 
  count('id') FILTER (WHERE account_type=3) as platinum 
FROM clients;

你的对比查询与执行计划差异 #

你用两个逻辑完全等价的查询做了测试，结果一致但执行效率天差地别：

查询1（全局WHERE过滤） #

select count(*) 
from main_search 
where created >= '2017-10-12T00:00:00.081739+00:00'::timestamptz 
  and created < '2017-10-13T00:00:00.081739+00:00'::timestamptz 
  and parent_id is null;

执行计划完美利用了main_search_created_parent_id_null_idx联合索引，走索引扫描（Index Scan），执行效率极高：

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1174.04..1174.05 rows=1 width=0) (actual time=5.077..5.077 rows=1 loops=1)
  -> Index Scan using main_search_created_parent_id_null_idx on main_search (cost=0.43..1152.69 rows=8540 width=0) (actual time=0.026..4.384 rows=9682 loops=1)
        Index Cond: ((created >= '2017-10-11 20:00:00.081739-04'::timestamp with time zone) AND (created < '2017-10-12 20:00:00.081739-04'::timestamp with time zone))
Planning time: 0.826 ms
Execution time: 5.227 ms
(5 rows)

查询2（FILTER子句过滤） #

select count('id') filter ( 
  where created >= '2017-10-12T00:00:00.081739+00:00'::timestamptz 
    and created < '2017-10-13T00:00:00.081739+00:00'::timestamptz 
    and parent_id is null
) as count 
from main_search;

结果虽然和查询1一致，但执行计划走了全表扫描（Seq Scan），执行时间差了几百倍：

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=178054.93..178054.94 rows=1 width=12) (actual time=1589.006..1589.007 rows=1 loops=1)
  -> Seq Scan on main_search (cost=0.00..146459.39 rows=4212739 width=12) (actual time=0.051..882.099 rows=4212818 loops=1)
Planning time: 0.051 ms
Execution time: 1589.070 ms
(4 rows)

原因分析 #

PostgreSQL 9.5的优化器还不具备将FILTER子句中的过滤条件下推到扫描阶段的能力，没办法提前用索引筛选符合条件的行。它会先全表扫描所有数据，再在聚合阶段应用FILTER里的条件进行计数，而不像全局WHERE那样先通过索引缩小数据范围再聚合。

这个问题在PostgreSQL 9.6及以上版本中得到了修复，优化器可以识别FILTER子句中的可下推条件，进而选择索引扫描提升效率。

9.5版本的临时解决方案 #

如果你暂时无法升级数据库，可以用以下两种方式替代：

• 用CASE WHEN模拟FILTER的多维度聚合效果，这种写法能正常利用索引：

  SELECT
    count(CASE WHEN account_type=1 THEN id END) as regular,
    count(CASE WHEN account_type=2 THEN id END) as gold,
    count(CASE WHEN account_type=3 THEN id END) as platinum
  FROM clients
  -- 可添加全局过滤条件
  

• 将FILTER条件拆成子查询，先筛选数据再聚合：

  SELECT count(id) as count
  FROM (
    SELECT id
    FROM main_search
    WHERE created >= '2017-10-12T00:00:00.081739+00:00'::timestamptz 
      and created < '2017-10-13T00:00:00.081739+00:00'::timestamptz 
      and parent_id is null
  ) as filtered_data;
  

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