索引
- B-tree
btree，适合任意单值类型，可用于=, >, <, >=, <=以及排序。
选择性越好(唯一值个数接近记录数)的列，越适合b-tree。
当被索引列存储相关性越接近1或-1时，数据存储越有序，范围查询扫描的HEAP PAGE越少。
CREATE INDEX name ON table USING BTREE (column);
- Hash
当字段超过单个索引页的1/4时，不适合b-tree索引。如果业务只有=的查询需求，使用hash index效率更高
CREATE INDEX name ON table USING HASH (column);
-
Bitmap
-
gin(Generalized Inverted Index, 通用倒排索引)
| （column value: row IDs tree | list）。适合多值列，也适合单值列。例如数组、全文检索、JSON、HSTORE等类型。 |
多值列搜索：包含、相交、不包含。
单值列搜索：等值。
适合多列组合索引(col1,col2,coln)，适合任意列组合搜索。
jsonb_ops和jsonb_path_ops GIN 索引之间的技术区别是前者为数据中的每一个键和值创建独立的索引项， 而后者值为该数据中的每个值创建索引项
- gist
适合数据有交错的场景，例如 全文检索、range类型、空间类型(点、线、面、多维对象… …)。
空间类型，支持几何搜索（包含、相交、上、下、左、右等）。支持KNN排序。
全文检索类型、范围类型，支持包含搜索。
- sp-gist
SP-GiST类似GiST，是一个通用的索引接口，但是SP-GIST使用了空间分区的方法，使得SP-GiST可以更好的支持非平衡数据结构，例如quad-trees, k-d tree, radis tree.
空间分区索引类型，适合不平衡数据集(例如xxxyyyzzz??????组成的VALUE，xxx, yyy, zzz，每个值包含一些数据集，每个数据集的数据量不平衡可能导致TREE不平衡)。
sp-gist索引结构，可以用于解决此类不平衡数据的倾斜问题
- brin
BRIN 索引是块级索引，BRIN适合单值类型，当被索引列存储相关性越接近1或-1时，数据存储越有序，块的边界越明显，BRIN索引的效果就越好。
例如时序数据，在时间或序列字段创建BRIN索引，进行等值、范围查询时效果很棒。
-- 创建表
create table brin_test(time timestamp);
-- 插入数据,大概800万条
insert into brin_test(time) select generate_series(now(), now() + '100 days', '1 second');
-- 查看数据量
select count(*) from brin_test ;
-- 查看数据关联性
SELECT correlation from pg_stats WHERE tablename = 'brin_test' AND attname = 'time';
-- 不使用索引
test=# explain (analyze,buffers,costs,timing) select * from brin_test where time between '2019-11-05 11:35:04.362988' and '2019-11-05 11:55:04.362988';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..93351.76 rows=1211 width=8) (actual time=0.403..181.644 rows=1201 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=5715 read=7645
-> Parallel Seq Scan on brin_test (cost=0.00..92230.66 rows=505 width=8) (actual time=116.999..177.322 rows=400 loops=3)
Filter: (("time" >= '2019-11-05 11:35:04.362988'::timestamp without time zone) AND ("time" <= '2019-11-05 11:55:04.362988'::timestamp without time zone))
Rows Removed by Filter: 2879600
Buffers: shared hit=16146 read=22085
Planning time: 0.037 ms
Execution time: 187.565 ms
(10 rows)
-- b-tree索引
test=# create index idx_brin_test_time on brin_test(time);
CREATE INDEX
test=# \di+
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+--------------------+-------+----------+-----------+--------+-------------
public | idx_brin_test_time | index | postgres | brin_test | 185 MB |
(1 row)
-- b-tree范围查找
test=# explain (analyze,buffers,costs,timing) select * from brin_test where time between '2019-11-05 11:35:04.362988' and '2019-11-05 11:55:04.362988';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_brin_test_time on brin_test (cost=0.43..49.66 rows=1211 width=8) (actual time=0.039..0.223 rows=1201 loops=1)
Index Cond: (("time" >= '2019-11-05 11:35:04.362988'::timestamp without time zone) AND ("time" <= '2019-11-05 11:55:04.362988'::timestamp without time zone))
Heap Fetches: 1201
Buffers: shared read=12
Planning time: 0.109 ms
Execution time: 0.275 ms
(6 rows)
-- 创建brin索引
test=# create index idx_brin_test_time on brin_test using brin(time);
CREATE INDEX
test=# \di+
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+--------------------+-------+----------+-----------+-------+-------------
public | idx_brin_test_time | index | postgres | brin_test | 56 kB |
(1 row)
--使用brin查找
test=# explain (analyze,buffers,costs,timing) select * from brin_test where time between '2019-11-05 11:35:04.362988' and '2019-11-05 11:55:04.362988';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on brin_test (cost=16.33..37738.47 rows=1211 width=8) (actual time=1.444..2.946 rows=1201 loops=1)
Recheck Cond: (("time" >= '2019-11-05 11:35:04.362988'::timestamp without time zone) AND ("time" <= '2019-11-05 11:55:04.362988'::timestamp without time zone))
Rows Removed by Index Recheck: 27727
Heap Blocks: lossy=128
Buffers: shared hit=63 read=74
-> Bitmap Index Scan on idx_brin_test_time (cost=0.00..16.03 rows=28896 width=0) (actual time=0.082..0.082 rows=1280 loops=1)
Index Cond: (("time" >= '2019-11-05 11:35:04.362988'::timestamp without time zone) AND ("time" <= '2019-11-05 11:55:04.362988'::timestamp without time zone))
Buffers: shared hit=9
Planning time: 0.098 ms
Execution time: 3.012 ms
(10 rows)
- rum
rum 是一个索引插件，由Postgrespro开源，适合全文检索，属于GIN的增强版本。
增强包括：
1、在RUM索引中，存储了lexem的位置信息，所以在计算ranking时，不需要回表查询（而GIN需要回表查询）。
2、RUM支持phrase搜索，而GIN无法支持。
3、在一个RUM索引中，允许用户在posting tree中存储除ctid（行号）以外的字段VALUE，例如时间戳。
这使得RUM不仅支持GIN支持的全文检索，还支持计算文本的相似度值，按相似度排序等。同时支持位置匹配，例如（速度与激情，可以采用”速度” <2> “激情” 进行匹配，而GIN索引则无法做到）
-
bloom bloom索引接口是PostgreSQL基于bloom filter构造的一个索引接口，属于lossy索引，可以收敛结果集(排除绝对不满足条件的结果，剩余的结果里再挑选满足条件的结果)，因此需要二次check，bloom支持任意列组合的等值查询 bloom存储的是签名，签名越大，耗费的空间越多，但是排除更加精准。有利有弊
-
zombodb
zombodb是PostgreSQL与ElasticSearch结合的一个索引接口，可以直接读写ES。
-- Install the extension:
CREATE EXTENSION zombodb;
-- Create a table:
CREATE TABLE products (
id SERIAL8 NOT NULL PRIMARY KEY,
name text NOT NULL,
keywords varchar(64)[],
short_summary phrase,
long_description fulltext,
price bigint,
inventory_count integer,
discontinued boolean default false,
availability_date date
);
-- insert some data
-- Index it:
CREATE INDEX idx_zdb_products
ON products
USING zombodb(zdb('products', products.ctid), zdb(products))
WITH (url='http://localhost:9200/', shards=5, replicas=1);
-- Query it:
SELECT *
FROM products
WHERE zdb('products', ctid) ==> 'keywords:(sports,box) or long_description:(wooden w/5 away) and price < 100000';
- bitmap bitmap索引是Greenplum的索引接口，类似GIN倒排，只是bitmap的KEY是列的值，VALUE是BIT（每个BIT对应一行），而不是行号list或tree 当某个字段的唯一值个数在100到10万之间(超出这个范围，不建议使用bitmap)时，如果表的记录数特别多，而且变更不频繁（或者是AO表），那么很适合BITMAP索引，bitmap索引可以实现快速的多个或单个VALUE的搜索。因为只需要对行号的BITMAP进行BIT与或运算，得到最终的BITMAP，从最终的BITMAP映射到行进行提取。
bitmap与btree一样，都支持 等于，大于，小于，大于等于，小于等于的查询。
部分索引
PostgreSQL允许用户创建部分索引，例如业务上只关心激活用户，所以可以只对激活用户创建索引
create table test(id int, info text, crt_time timestamp, active boolean);
create index idx_test_id on test(id) where active;
select * from test where active and id=?; -- 可以使用部分索引
表达式索引
表达式索引也是PostgreSQL特有的特性，例如用户的数据需要转换后查询，例如某些设备上传的地理坐标的坐标系不符合国标，需要转换为国内的空间坐标来查询。
那么可以针对这类字段，创建表达式索引，将转换过程放到表达式中，查询时也使用表达式进行查询。
create table t_express (id int, pos geometry);
create index idx_t_express_1 on t_express using gist ( ( ST_Transform(pos, 26986) ) );
select * from t_express order by ST_Transform(pos, 26986) <-> ST_Transform(ST_GeomFromText('POINT(108.50000000001 22.8)', 4326), 26986) limit 10;
其他
- 添加数据 ```sql create table test_indexscan(id int, info text); insert into test_indexscan select (random()*5000000)::int,md5(random()::text) from generate_series(1,100000); create index idx_test_indexscan_id on test_indexscan (id);
- 查看表和索引大小
```sql
test=# \dt+ test_indexscan
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------------+-------+----------+-------+-------------
public | test_indexscan | table | postgres | 33 MB |
test=# \di+ idx_test_indexscan_id
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-----------------------+-------+----------+----------------+-------+-------------
public | idx_test_indexscan_id | index | postgres | test_indexscan | 14 MB |
- 查看索引块大小(每块8k)
test=# SELECT relpages FROM pg_class where relname = 'idx_test_indexscan_id';
relpages
----------
1771
(1 row)
- 查看列值关联性
test=# SELECT correlation from pg_stats WHERE tablename = 'test_indexscan' AND attname = 'id';
correlation
-------------
0.00864677
(1 row)
索引选择
- 必要约束 1、如果用户要创建 unique 索引，那么只能选择btree索引接口。
2、某些类型指支持某类索引接口，例如数组类型、全文检索类型，只支持GIN索引。GIS类型只支持gist或sp-gist索引
- 选择性 如果某个类型支持多种索引接口，那么到底选择哪种接口比较好呢？
和数据的选择性是有关系的。
1、选择性差（例如1亿记录，有100-10万 条唯一值），建议使用gin或bitmap索引。
2、选择性好（例如1亿记录，有8000万唯一值），建议使用btree或hash索引
- 数据分布 1、对于数据值与行号呈现较强的线性相关特性时，加入用户没有按该列顺序输出的需求。则建议使用brin块级索引。
2、当列长度超过数据块的1/3时，不能使用btree，建议使用hash索引。或者使用表达式btree索引，建少索引entry的大小
- 查询需求
如果某个类型支持多种索引接口，那么到底选择哪种接口比较好呢？
和数据的查询需要也是有关系的。
1、范围查询、排序查询、等值查询
可以使用btree, brin.
2、仅仅有等值查询
可以使用btree, hash
3、有多个列的任意组合查询需求
可以使用bitmap, gin, btree等索引接口
4、有包含、相交等查询需求
可以使用gin等索引接口
5、有距离、距离排序、相交、包含、贯穿等查询需求
可以使用gist等索引接口
- 性能要求
当一个列支持多种索引接口时，应该选择哪个索引接口，和业务对性能的要求也有关系。
例如，某个列同时支持btree和brin索引，应该选哪个呢？
除了前面提到的线性相关性，还需要考虑业务的查询要求，以及对性能的要求，BTREE对写入性能影响比BRIN大很多，但是明细查询速度，排序速度，limit输出等，都比GIN好很多。
但是当重复值较多时，建议使用GIN，因为它是将元素值作为索引KEY，将行号作为VALUE的倒排索引。
一些参数参考
pg_class.reltuples，行数
pg_stats.n_distinct，唯一值个数或比例
pg_stats.correlation，列的线性相关性
select tablename,attname,n_distinct,correlation from pg_stats where tablename='tbl_label';
n_distinct解释
-1表示唯一，也就是说这个列的每一行都不一样.
>=1时，表示这个列有多少唯一值.
<1时，表示这个列的 唯一值数量/总数.
correlation解释
表示该列与数据堆存储的线性相关性, 1表示正向完全相关。越接近0表示数据分布越离散。<0表示反向相关。