2.4 理解另外的B-树特性

函数索引

PostgreSQL允许创建函数索引。索引并不直接包括值，而是存储一个函数的输出。

CREATE TABLE t_test2 AS
SELECT
    *
FROM
t_test;


CREATE INDEX idx_cos ON t_test2 (cos(id));


ANALYZE;


EXPLAIN
SELECT
    id 
FROM 
    t_test2 
WHERE 
    id = 10;

                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on t_test2  (cost=0.00..71622.00 rows=1 width=4)
   Filter: (id = 10)
(2 rows)


EXPLAIN 
SELECT 
    id 
FROM 
    t_test2 
WHERE 
    cos(id) = 10;

                              QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using idx_cos on t_test2  (cost=0.43..8.45 rows=1 width=4)
   Index Cond: (cos((id)::double precision) = '10'::double precision)
(2 rows)

只有输出不变的函数才能被这样使用。age之类的函数不适合作索引，因为它们的输出不是常数。

减少空间消耗

postgres=# \di+

                            List of relations
 Schema |    Name    | Type  |  Owner   |  Table   | Size  | Description
--------+------------+-------+----------+----------+-------+-------------
 public | idx_cos    | index | postgres | t_test2  | 86 MB |
 public | idx_id     | index | postgres | t_test   | 86 MB |
 public | idx_name   | index | postgres | t_test   | 86 MB |
 public | idx_random | index | postgres | t_random | 86 MB |
(4 rows)

索引消耗了大量的空间。因此，过度索引和索引不足一样成为一个问题。索引不只是消耗空间，每一个INSERT和UPDATA也必须维护索引中的值。

如果在表中只有少数不同的值，部分索引是一种方案：

DROP INDEX idx_name ;


CREATE INDEX
    idx_name
ON
    t_test (name) 
WHERE
    name NOT in (
        'hans', 'paul'
    );


postgres=# \di+ idx_name

                            List of relations
 Schema |   Name   | Type  |  Owner   | Table  |    Size    | Description
--------+----------+-------+----------+--------+------------+-------------
 public | idx_name | index | postgres | t_test | 8192 bytes |
(1 row)

注意只有排除那些占据了表中很大一部分（至少25%左右）的值才有意义。部分索引的理想候选是性别（假设大部分是男性）、国籍（假设大部分人有相同的国籍）等。

在建立索引时添加数据

在索引正在被构建时不能修改其基表。CREATE INDEX命令会用一个SHARE锁锁住表来确保不会发生任何更改。

事务1	事务2
BEGIN;	BEGIN;
CREATE INDEX idx_name2 ON t_test (name);
	UPDATE t_test SET name = ‘hans2’ WHERE id = 1;
	等待事务1
COMMIT;	更新成功
	COMMIT;

该问题的解决方案是CREATE INDEX CONCURRENTLY命令。这个命令构建索引将会花掉更长的时间（通常至少是普通创建的两倍），但用户可以在创建索引期间正常地使用该表。

CREATE INDEX CONCURRENTLY
    idx_name2
ON
    t_test (name);

注意，这个命令不可以在事务块中使用。

同时，PostgreSQL不保证这个命令会成功，如果在系统上运行的操作出于某种原因与索引创建发生冲突，创建结束后会得到一个被标记为无效的索引（INVALID），这个索引仍然会导致更新变慢。如果所创建的是一个唯一索引，这个无效的索引还会导致插入数据时报插入重复值错误。

对于此，手工删除这个无效的索引即可。