PostgreSQL索引分类

PostgreSQ 支持空间和倒排索引

• 普通索引
  • 也就是二级索引 索引和数据是分开存储的
  • 索引查找数据即需要访问索引，又需要访问表，而表的访问是随机 I/O。
  • 查询效率 o(nlog(n))
• 哈希索引
  • 只能用用于 == 查看
  • 查询效率 o(1)
• 通用搜索树（Generalized Search Tree）GiST
• R 树（radix tree）结构用于空间 区域 间距
• 查询效率 O(k)(k为字符串集合中最长的字符串长度）
• SP-GiST 索引
• SP-GiST 代表空间分区 GiST，
• 主要用于 GIS、多媒体、电话路由以及 IP 路由等数据的索引
• 查询效率 O(k)
• GIN 索引
• 代表广义倒排索引（generalized inverted indexes） 用于搜索
• 可以用于 hstore、array、jsonb 以及 range 数据类型
• 查询效率 O(1)
• BRIN 索引
• 代表块区间索引（block range indexes）
• 存储了连续物理范围区间内的数据摘要信息
• 查询效率 O(nlog(n)B+ Tree）
• 唯一索引
• 多列索引
• 函数索引
• 部分索引
• 覆盖索引

普通索引的 示例

CREATE TABLE test (
  id integer,
  name text
);

insert into test
select v,'val:'||v from generate_series(1, 10000000) v;

# 没有索引
explain analyze
SELECT name FROM test WHERE id = 10000;
QUERY PLAN 


#####  全表 Seq Scan on Execution Time
Seq Scan on test  (cost=10000000000.00..10000179055.00 rows=1 width=11) (actual time=4.174..922.096 rows=1 loops=1)
  Filter: (id = 10000)
  Rows Removed by Filter: 9999999
Planning Time: 3.370 ms
Execution Time: 922.213 ms   

# 加入索引

CREATE INDEX test_id_index ON test (id);

##### 使用索引 Index Scan  Execution Time
Index Scan using test_id_index on test  (cost=0.43..8.45 rows=1 width=11) (actual time=5.917..5.920 rows=1 loops=1)
  Index Cond: (id = 10000)
Planning Time: 25.992 ms
Execution Time: 9.869 ms

空间索引示例

create table points(p point);

create index on points using gist(p);

insert into points(p) values
  (point '(1,1)'), (point '(3,2)'), (point '(6,3)'),
  (point '(5,5)'), (point '(7,8)'), (point '(8,6)');
	
# 矩形有区域的内的点
 select * from points where p <@ box '(2,1),(7,4)';
 
### 最近的两个点
 select * from points order by p <-> point '(4,7)' limit 2;


#### ip 查询
CREATE TABLE ip_addresses (
    id SERIAL PRIMARY KEY,
    ip_range inet
);

INSERT INTO ip_addresses (ip_range) VALUES
('192.168.0.0/16'),
('10.0.0.0/8'),
('172.16.0.0/12');

### 开启拓展
psql postgres
CREATE EXTENSION btree_gist;

CREATE INDEX ip_range_idx ON ip_addresses USING spgist(ip_range inet_ops);
## 范围查询
SELECT *
FROM ip_addresses
WHERE ip_range >>= '192.168.100.0/24';

           

覆盖索引示例

CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT, order_date DATE, total_amount FLOAT);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1001, '2023-01-01', 150.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1002, '2023-01-02', 200.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1001, '2023-01-03', 100.00);


CREATE INDEX covering_index ON orders (customer_id) INCLUDE (order_date, total_amount);


SELECT order_date, total_amount FROM orders WHERE customer_id = 1001;

块范围索引 示例

CREATE TABLE sales (id SERIAL PRIMARY KEY, order_date DATE);
INSERT INTO sales (order_date) VALUES ('2023-01-01');
INSERT INTO sales (order_date) VALUES ('2023-02-01');
INSERT INTO sales (order_date) VALUES ('2023-03-01');

CREATE INDEX brin_index ON sales USING BRIN (order_date);

CREATE INDEX brin_index ON sales USING BRIN (order_date);

倒排索引 示例

CREATE TABLE books (id SERIAL PRIMARY KEY, title TEXT);
INSERT INTO books (title) VALUES ('Harry Potter and the Sorcerer''s Stone');
INSERT INTO books (title) VALUES ('The Great Gatsby');
INSERT INTO books (title) VALUES ('To Kill a Mockingbird');


CREATE INDEX gin_index ON books USING GIN (to_tsvector('english', title));

SELECT * FROM books WHERE to_tsvector('english', title) @@ to_tsquery('english', 'Potter');

部分索引 示例

CREATE TABLE t (a int, b int, c int);

#  基于字段 a 和 b 创建了多列索引，同时利用INCLUDE在索引的叶子节点存储了字段 c 的值
CREATE UNIQUE INDEX idx_t_ab ON t USING btree (a, b) INCLUDE (c);
insert into t 
select v,v+100,v+200 from generate_series(1, 10000000) v;

### 查询
explain analyze
select a, b, c 
from t 
where a = 100 and b = 200;
QUERY PLAN  

#### 
Index Only Scan using idx_t_ab on t  (cost=0.43..4.45 rows=1 width=12) (actual time=0.389..0.391 rows=1 loops=1)
  Index Cond: ((a = 100) AND (b = 200))
  Heap Fetches: 0
Planning Time: 5.027 ms
Execution Time: 0.949 ms