PostgreSQL 9.4.1---聚集函数的优化详解 ---聚集函数与索引

创建数据：
CREATE TABLE t_key(id INT PRIMARY KEY, k1 INT NOT NULL,
k2 INT NULL, k3p1 INT, k3p2 INT, col INT NULL
);

CREATE UNIQUE INDEX k1_idx_uqi ON t_key(k1);
CREATE INDEX k2_idx ON t_key(k2);
CREATE INDEX k3_idx ON t_key(k3p1, k3p2);

INSERT INTO t_key VALUES (1,1,1,1,1,1),(2,2,2,1,2,2),(3,3,3,1,3,3),(4,4,4,2,1,4),
(5,5,5,2,2,5),(6,6,6,2,3,6),(7,7,7,3,1,7),(8,8,8,3,3,8),(9,9,9,4,1,9); 

一 条件列有无索引决定着是否能够优化
E1：条件列无索引，不可优化
postgres=# EXPLAIN SELECT MIN(col), MAX(col) FROM t_key WHERE col>3;
QUERY PLAN
-------------------------------------------------------------
Aggregate (cost=-1.#J..-1.#J rows=1 width=4)
-> Seq Scan on t_key (cost=0.00..22.91 rows=-1 width=4)
Filter: (col > 3)
(3 rows)

E2：条件列有索引(此例是普通索引)，执行MIN和MAX，可优化 
postgres=# EXPLAIN SELECT MIN(k2), MAX(k2) FROM t_key WHERE k2<3;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Result (cost=-1.#J..-1.#J rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.15..-1.#J rows=1 width=4)
-> Index Only Scan using k2_idx on t_key (cost=0.15..-1.#J rows=-1 width=4)
Index Cond: ((k2 IS NOT NULL) AND (k2 < 3))
InitPlan 2 (returns $1)
-> Limit (cost=0.15..-1.#J rows=1 width=4)
-> Index Only Scan Backward using k2_idx on t_key t_key_1 (cost=0.15..-1.#J rows=-1 width=4)
Index Cond: ((k2 IS NOT NULL) AND (k2 < 3))
(9 rows)

E4：支持使用主键、唯一索引等进行优化
postgres=# EXPLAIN SELECT MIN(k2), MAX(k2) FROM t_key WHERE k1>3;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Result (cost=-1.#J..-1.#J rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.15..-1.#J rows=1 width=4)
-> Index Scan using k2_idx on t_key (cost=0.15..-1.#J rows=-1 width=4)
Index Cond: (k2 IS NOT NULL)
Filter: (k1 > 3)
InitPlan 2 (returns $1)
-> Limit (cost=0.15..-1.#J rows=1 width=4)
-> Index Scan Backward using k2_idx on t_key t_key_1 (cost=0.15..-1.#J rows=-1 width=4)
Index Cond: (k2 IS NOT NULL)
Filter: (k1 > 3)
(11 rows)