Postgresql杂谈 07—Postgresql中的函数索引和部分索引

       本文笔者主要介绍下Postgresql中两种比较特殊的索引——函数索引和部分索引。函数索引，顾名思义，索引字段经过了某个函数的处理，以达到在某种特殊的场景下走索引的目的。而部分索引，就是针对表中的部分字段建立索引，而达到查询其中这部分数据走索引的目的。

一、函数索引

       函数索引，一种典型的应用场景，就是用表中的某一字符串类型的字段构建查询条件进行查询时，期望忽略大小写，笔者通过下面的例子来介绍下函数索引的用法。

       首先，创建一个users表，用来作为数据源：

create table users(id serial,name varchar(32),age int);

       创建一个function，用来向users表中插入随机字符串：

create or replace FUNCTION random_str() 
returns varchar(32)
as
$BODY$
DECLARE
  str varchar(128);
	rs varchar(32);
begin 
    str:='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
		rs:=substr(str,ceil(random()*52)::int,1)||substr(str,ceil(random()*52)::int,1)||substr(str,ceil(random()*52)::int,1);
		return rs;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

       接下来，向users表插入500W条数据：

postgres=# insert into users (name,age) select random_str(),ceil(random()*100) from generate_series(1,5000000);
INSERT 0 5000000

       我们从users表中查询名叫tom的用户，忽略大小写。

postgres=# explain (analyze,verbose,timing) select * from users where lower(name)='tom';
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..54704.48 rows=19760 width=12) (actual time=30.880..1639.766 rows=280 loops=1)
   Output: id, name, age
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on public.users  (cost=0.00..51728.48 rows=8233 width=12) (actual time=25.357..1452.396 rows=93 loops=3)
         Output: id, name, age
         Filter: (lower((users.name)::text) = 'tom'::text)
         Rows Removed by Filter: 1666573
         Worker 0: actual time=32.700..1360.812 rows=93 loops=1
         Worker 1: actual time=12.944..1364.588 rows=68 loops=1
 Planning Time: 2.196 ms
 Execution Time: 1640.027 ms
(12 rows)

       可以看到，在没有建立索引之前，从500W条数据中找到目标需要花费1640ms的时间。接下来，我们在users表上加上lower函数的索引。

 postgres=# create index users_name_lower_inx on users using btree(lower(name));
CREATE INDEX

       再继续上面的查询，发现这次只花了66ms，效率提升了将近30倍：

postgres=# explain (analyze,verbose,timing) select * from users where lower(name)='tom';
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.users  (cost=470.18..28438.54 rows=25000 width=12) (actual time=3.507..66.492 rows=280 loops=1)
   Output: id, name, age
   Recheck Cond: (lower((users.name)::text) = 'tom'::text)
   Heap Blocks: exact=279
   ->  Bitmap Index Scan on users_name_lower_inx  (cost=0.00..463.93 rows=25000 width=0) (actual time=0.407..0.408 rows=280 loops=1)
         Index Cond: (lower((users.name)::text) = 'tom'::text)
 Planning Time: 0.515 ms
 Execution Time: 66.752 ms
(8 rows)

        函数索引的原理并不复杂，它并不是在查找索引时进行函数的计算，而是在创建索引时就针对每行的索引列值进行了函数计算，然后再以此再建立BTree索引。

二、部分索引

       以上就是函数索引，现在我们继续学习下部分索引，这里列举Postgresql官方给的三个很好的例子：

• 排除部分普通数值

       创建一个访问日志access_log表，用来记录访问用户的IP地址和url等关键信息，访问的IP地址按照业务上划分可以分为内部地址和外部地址，而外部地址量比较多，而且我们比较关心。内部地址我们没有那么关心，现在为了节省磁盘空间和索引效率，只对我们关心的外网地址建立部分索引。

       首先，创建这个access_log表：

CREATE TABLE access_log (
    url varchar,
    client_ip inet,
    ...
);

       创建部分索引，排除内部的IP地址：

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND
           client_ip < inet '192.168.100.255');

       如下查询，会使用该索引：

SELECT *
FROM access_log
WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

如下查询，不会使用该索引：

SELECT *
FROM access_log
WHERE client_ip = inet '192.168.100.23';

• 排除部分不感兴趣的值

       假如有一个订单表，其中有一个布尔类型的参数billed表示订单是否已经付款，我们对未付款的订单比较感兴趣，并为此建立一个部分索引，建立索引的方式如下：

CREATE INDEX orders_unbilled_index ON orders (order_nr)
WHERE billed is not true;

       使用索引的一个有效的查询可能是：

SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;

       索引也可以用于完全不涉及order_nr的查询：

SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;

       但是，下面这个查询将不会使用该索引，因为查询条件中并没有使用billed is not true，订单有可能是未付款的也有可能时付款的。

SELECT * FROM orders WHERE order_nr = 3501;

• 设置一个部分唯一索引

        部分索引的第三个作用针对针对满足某一条件下的某些字段建立唯一性索引，比如有一个记录测试结果的表，当测试结果为成功时，要求每个课题和目标组合必须唯一；但是，如果测试结果为不成功时，没有这种要求。表的结构如下：

CREATE TABLE tests (
    subject text,
    target text,
    success boolean,
    ...
);

        创建的部分索引如下：

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

       当有少数成功测试和很多不成功测试时这是一种特别有效的方法。