Postgresql中的filter

Postgresql中的filter

代码例子

-- 建立表
postgres=# create table t(i int);
CREATE TABLE

-- 插入数据
postgres=# insert into t(i) select i from generate_series(1, 10000) t(i);
INSERT 0 10000

-- 创建索引，为了后面的演示
postgres=# create index on t(i);
CREATE INDEX

-- 使用where 进行统计
postgres=# select count(*) from t where i > 10 and i < 100;
 count 
-------
    89

-- 在where的情况下再做filter
postgres=# select
count(*) cnt,
count(*) filter(where i > 20 and i < 60) cnt2 from t where i > 10 and i < 100;
 cnt | cnt2 
-----+------
  89 |   39 

应用场景

1. 在一定的条件下统计，然后同时统计另外一个条件。
2. 譬如：多个班的同学成绩，要统计一个班的统计，也要统计这个班不及格的同学的人数。譬如：在过车数据中，要统计一天的数据(where)，也要统计这一天中高峰期的过车数据(filter)

关于索引情况，结论: filter是不走索引的

-- where中使用了索引
postgres=# explain select count(*) from t where i > 10 and i < 100;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Aggregate  (cost=10.29..10.30 rows=1 width=8)
   ->  Index Only Scan using t_i_idx on t  (cost=0.29..10.07 rows=89 width=0)
         Index Cond: ((i > 10) AND (i < 100))

-- 同样是这个条件，filter不走索引
postgres=# explain select count(*) filter(where i > 10 and i < 100) from t;
                         QUERY PLAN                          
-------------------------------------------------------------
 Aggregate  (cost=220.00..220.01 rows=1 width=8)
   ->  Seq Scan on t  (cost=0.00..145.00 rows=10000 width=4) 

以filter等价的功能

-- cnt1, cnt2 等价
postgres=# select 
                count(*) cnt,
                count(*) filter(where i > 10 and i < 100) cnt1,
                count(case when i > 10 and i < 100 then 1 else null end) cnt2
           from 
                t;
---
  cnt  | cnt1 | cnt2 
-------+------+------
 10000 |   89 |   89 

结论：filter 比原来的case when实现更加优雅简单