背景
PostgreSQL内置了GIN索引，支持全文检索，支持数组检索等多值数据类型的检索。
在全文检索中，不包含某个关键字能用到索引吗？
实际上GIN是倒排索引，不包含某个关键字的查询，实际上是跳过主tree上面的TOKEN的扫描。
只要被跳过的TOKEN包含了大量数据，那么就是划算的。PostgreSQL是基于CBO的执行计划优化器，所以会自动选择最优的索引。
例子1，全文检索不包含查询
1、创建测试表
postgres=# create table notcontain (id int, info tsvector);
CREATE TABLE
2、创建生成随机字符串的函数
CREATE OR REPLACE FUNCTION
gen_rand_str(integer)
RETURNS text
LANGUAGE sql
STRICT
AS $function$
select string_agg(a[(random()*6)::int+1],'') from generate_series(1,$1), (select array['a','b','c','d','e','f',' ']) t(a);
$function$;
3、插入100万测试数据
postgres=# insert into notcontain select generate_series(1,1000000), to_tsvector(gen_rand_str(256));
4、创建全文索引（GIN索引）
create index idx_notcontain_info on notcontain using gin (info);
5、查询某一条记录
postgres=# select * from notcontain limit 1;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | 1
info | 'afbbeeccbf':3 'b':16 'bdcdfd':2 'bdcfbcecdeeaed':8 'bfedfecbfab':7 'cd':9 'cdcaefaccdccadeafadededddcbdecdaefbcfbdaefcec':14 'ceafecff':6 'd':17,18 'dbc':12 'dceabcdcbdca':10 'dddfdbffffeaca':13 'deafcccfbcdebdaecda':11 'dfbadcdebdedbfa':19 'eb':15 'ebe':1 'febdcbdaeaeabbdadacabdbbedfafcaeabbdcedaeca':5 'fedeecbcdfcdceabbabbfcdd':4
6、测试不包含某个关键字
数据库自动选择了全表扫描，没有使用GIN索引。
为什么没有使用索引呢，我前面解释了，因为这个关键字的数据记录太少了，不包含它时使用索引过滤不划算。
（当包含它时，使用GIN索引就非常划算。包含和不包含是相反的过程，成本也是反的）
select * from notcontain t1 where info @@ to_tsquery ('!eb');
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where info @@ to_tsquery ('!eb');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on postgres.notcontain t1 (cost=0.00..318054.51 rows=950820 width=412) (actual time=0.016..1087.463 rows=947911 loops=1)
Output: id, info
Filter: (t1.info @@ to_tsquery('!eb'::text))
Rows Removed by Filter: 52089
Buffers: shared hit=55549
Planning time: 0.131 ms
Execution time: 1134.571 ms
(7 rows)
7、强制关闭全表扫描，让数据库选择索引。
可以看到，使用索引确实是慢的，我们大多数时候应该相信数据库的成本规划是准确的。（只要成本因子和环境性能匹配足够的准，这些都是可以校准的，有兴趣的同学可以参考我写的因子校准方法。）
postgres=# set enable_seqscan=off;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where info @@ to_tsquery ('!eb');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on postgres.notcontain t1 (cost=82294981.00..82600120.25 rows=950820 width=412) (actual time=1325.587..1540.145 rows=947911 loops=1)
Output: id, info
Recheck Cond: (t1.info @@ to_tsquery('!eb'::text))
Heap Blocks: exact=55549
Buffers: shared hit=171948
-> Bitmap Index Scan on idx_notcontain_info (cost=0.00..82294743.30 rows=950820 width=0) (actual time=1315.663..1315.663 rows=947911 loops=1)
Index Cond: (t1.info @@ to_tsquery('!eb'::text))
Buffers: shared hit=116399
Planning time: 0.135 ms
Execution time: 1584.670 ms
(10 rows)
例子2，全文检索不包含查询
这个例子造一份倾斜数据，这个TOKEN包含了大量的重复记录，通过不包含过滤它。看看能否使用索引。
1、生成测试数据
postgres=# truncate notcontain ;
TRUNCATE TABLE
postgres=# insert into notcontain select generate_series(1,1000000), to_tsvector('abc');
INSERT 0 1000000
2、测试不包含ABC的检索
数据库自动选择了索引扫描，跳过了不需要检索的数据块。
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where info @@ to_tsquery ('!abc');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on postgres.notcontain t1 (cost=220432.15..220433.71 rows=1 width=21) (actual time=107.936..107.936 rows=0 loops=1)
Output: id, info
Recheck Cond: (t1.info @@ to_tsquery('!abc'::text))
Buffers: shared hit=268
-> Bitmap Index Scan on idx_notcontain_info (cost=0.00..220432.15 rows=1 width=0) (actual time=107.933..107.933 rows=0 loops=1)
Index Cond: (t1.info @@ to_tsquery('!abc'::text))
Buffers: shared hit=268
Planning time: 0.183 ms
Execution time: 107.962 ms
(9 rows)
3、强制使用全表扫描，发现性能确实不如索引扫描，也验证了我们说的PostgreSQL是基于成本的优化器，自动选择最优的执行计划。
postgres=# set enable_bitmapscan =off;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where info @@ to_tsquery ('!abc');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Seq Scan on postgres.notcontain t1 (cost=0.00..268870.00 rows=1 width=21) (actual time=1065.436..1065.436 rows=0 loops=1)
Output: id, info
Filter: (t1.info @@ to_tsquery('!abc'::text))
Rows Removed by Filter: 1000000
Buffers: shared hit=6370
Planning time: 0.059 ms
Execution time: 1065.449 ms
(7 rows)
例子3，普通类型BTREE索引，不等于检索
这个例子是普通类型，使用BTREE索引，看看是否支持不等于的索引检索。
测试方法与GIN测试类似，使用倾斜和非倾斜两种测试数据。
1、非倾斜数据的不包含查询，使用索引过滤的记录非常少。
目前内核层面没有实现BTREE索引的不包含检索。（虽然技术上是可以通过INDEX SKIP SCAN来实现的，跳过不需要扫描的BRANCH节点）
postgres=# truncate notcontain ;
TRUNCATE TABLE
postgres=# insert into notcontain select generate_series(1,1000000);
INSERT 0 1000000
postgres=# create index idx1 on notcontain (id);
CREATE INDEX
postgres=# set enable_bitmapscan =on;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where id<>1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Seq Scan on postgres.notcontain t1 (cost=0.00..16925.00 rows=999999 width=36) (actual time=0.011..110.592 rows=999999 loops=1)
Output: id, info
Filter: (t1.id <> 1)
Rows Removed by Filter: 1
Buffers: shared hit=4425
Planning time: 0.195 ms
Execution time: 156.013 ms
(7 rows)
postgres=# set enable_seqscan=off;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where id<>1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on postgres.notcontain t1 (cost=10000000000.00..10000016925.00 rows=999999 width=36) (actual time=0.011..110.964 rows=999999 loops=1)
Output: id, info
Filter: (t1.id <> 1)
Rows Removed by Filter: 1
Buffers: shared hit=4425
Planning time: 0.062 ms
Execution time: 156.461 ms
(7 rows)
2、更换SQL写法，可以实现索引检索。但实际上由于不是使用的INDEX SKIP SCAN，所以需要一个JOIN过程，实际上效果并不佳。
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where not exists (select 1 from notcontain t2 where t1.id=t2.id and t2.id=1);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Anti Join (cost=0.85..25497.28 rows=999999 width=36) (actual time=0.023..277.639 rows=999999 loops=1)
Output: t1.id, t1.info
Merge Cond: (t1.id = t2.id)
Buffers: shared hit=7164
-> Index Scan using idx1 on postgres.notcontain t1 (cost=0.42..22994.22 rows=1000000 width=36) (actual time=0.009..148.520 rows=1000000 loops=1)
Output: t1.id, t1.info
Buffers: shared hit=7160
-> Index Only Scan using idx1 on postgres.notcontain t2 (cost=0.42..3.04 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)
Output: t2.id
Index Cond: (t2.id = 1)
Heap Fetches: 1
Buffers: shared hit=4
Planning time: 0.223 ms
Execution time: 322.798 ms
(14 rows)
postgres=# set enable_mergejoin=off;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where not exists (select 1 from notcontain t2 where t1.id=t2.id and t2.id=1);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Hash Anti Join (cost=3.05..27053.05 rows=999999 width=36) (actual time=0.060..251.232 rows=999999 loops=1)
Output: t1.id, t1.info
Hash Cond: (t1.id = t2.id)
Buffers: shared hit=4432
-> Seq Scan on postgres.notcontain t1 (cost=0.00..14425.00 rows=1000000 width=36) (actual time=0.011..84.659 rows=1000000 loops=1)
Output: t1.id, t1.info
Buffers: shared hit=4425
-> Hash (cost=3.04..3.04 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)
Output: t2.id
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=4
-> Index Only Scan using idx1 on postgres.notcontain t2 (cost=0.42..3.04 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1)
Output: t2.id
Index Cond: (t2.id = 1)
Heap Fetches: 1
Buffers: shared hit=4
Planning time: 0.147 ms
Execution time: 297.127 ms
(18 rows)
postgres=# set enable_seqscan=off;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from notcontain t1 where not exists (select 1 from notcontain t2 where t1.id=t2.id and t2.id=1);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Anti Join (cost=3.48..35622.27 rows=999999 width=36) (actual time=0.036..324.401 rows=999999 loops=1)
Output: t1.id, t1.info
Hash Cond: (t1.id = t2.id)
Buffers: shared hit=7164
-> Index Scan using idx1 on postgres.notcontain t1 (cost=0.42..22994.22 rows=1000000 width=36) (actual time=0.017..149.383 rows=1000000 loops=1)
Output: t1.id, t1.info
Buffers: shared hit=7160
-> Hash (cost=3.04..3.04 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)
Output: t2.id
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=4
-> Index Only Scan using idx1 on postgres.notcontain t2 (cost=0.42..3.04 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)
Output: t2.id
Index Cond: (t2.id = 1)
Heap Fetches: 1
Buffers: shared hit=4
Planning time: 0.141 ms
Execution time: 369.749 ms
(18 rows)
3、PostgreSQL还支持多核并行，所以全表扫描还可以暴力提升性能。
如果记录数非常多，使用并行扫描，性能提升非常明显。
postgres=# create unlogged table ptbl(id int);
CREATE TABLE
postgres=# insert into ptbl select generate_series(1,100000000);
postgres=# alter table ptbl set (parallel_workers =32);
\timing
非并行查询：
postgres=# set max_parallel_workers_per_gather =0;
postgres=# select count(*) from ptbl where id<>1;
count
----------
99999999
(1 row)
Time: 11863.151 ms (00:11.863)
并行查询：
postgres=# set max_parallel_workers_per_gather =32;
postgres=# select count(*) from ptbl where id<>1;
count
----------
99999999
(1 row)
Time: 610.017 ms
使用并行查询后，性能提升非常明显。
例子4，普通类型partial BTREE索引，不等于检索
对于固定的不等于查询，我们可以使用PostgreSQL的partial index功能。
create table tbl (id int, info text, crt_time timestamp, c1 int);
select * from tbl where c1<>1;
insert into tbl select generate_series(1,10000000), 'test', now(), 1;
insert into tbl values (1,'abc',now(),2);
create index idx_tbl_1 on tbl(id) where c1<>1;
cool，使用PARTIAL INDEX，0.03毫秒，在1000万数据中进行不等于检索。
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where c1<>1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_tbl_1 on postgres.tbl (cost=0.12..1.44 rows=1 width=21) (actual time=0.015..0.015 rows=1 loops=1)
Output: id, info, crt_time, c1
Buffers: shared hit=1 read=1
Planning time: 0.194 ms
Execution time: 0.030 ms
(5 rows)
小结
1、PostgreSQL内置了GIN索引，支持全文检索、支持数组等多值类型的搜索。
2、PostgreSQL使用基于成本的执行计划优化器，会自动选择最优的执行计划，在进行不包含检索时，PostgreSQL会自动选择是否使用索引扫描。
3、对于BTREE索引，理论上也能实现不等于的搜索（INDEX SKIP SCAN），目前内核层面还没有实现它，目前可以通过调整SQL的写法来使用索引扫描。
4、PostgreSQL还支持多核并行，所以全表扫描还可以暴力提升性能。 如果记录数非常多，使用并行扫描，性能提升非常明显。
5、PostgreSQL支持partial index，可以用于分区索引，或者部分索引。对于固定条件的不等于查询，效果非常显著。
