PostgreSQL索引与查询计划_postgresql查询已建立的索引-优快云博客

本文链接：https://blog.youkuaiyun.com/trayvontang/article/details/145036008

文章目录

创建索引
PostgreSQL扫描方式
查询计划示例(explain)
附录A-测试数据

创建索引

create index idx_name on table_name(column_name);

-- 创建唯一索引
create unique index uqe_idx_name on table_name(column_name);

-- 创建并发创建索引，不阻塞表更新、插入、删除操作
create index concurrently idx_name on table_name(column_name);

-- 索引不存在才创建
create index if not exists idx_name on table_name(column_name);

-- 使用using指定索引类型，默认是B-tree，其他类型：Hash、GiST、SP-GiST、GIN、BRIN
create index idx_name on table_name using btree(column_name);

-- 指定降序还是升序，若字段存在null值，是把null值放在前面还是最后
create index idx_name on table_name(column_name desc nulls first)

-- 指定索引表空间
create index idx_name on table_name(column_name) tablespace tablesapce_name;

修改索引：

-- 索引重新命名
alter index idx_name rename TO new_idx_name

-- 索引迁移表空间
alter index name set tablespace tablespace_name

-- 删除索引
drop index idx_name

PostgreSQL扫描方式

顺序扫描:没有索引，全表扫描
IndexScan：先通过索引扫描，然后根据索引回表，像MySQL的二级索引
IndexOnlyScan：查询的字段就是索引字段，不用回表，只需要索引就可以，像MySQL的聚簇索引
BitmapScan：通常是多个条件过滤，满足条件的标1，最终找出bitmap为1的回表

查询计划示例(explain)

我们创建索引是为了优化查询，那我们如何衡量我们创建的效果如何呢？

我们可以通过explain关键字来查看查询计划。

准备表

CREATE TABLE IF NOT EXISTS business_log(
	id bigint NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
	title varchar(32) NULL,
	start_time timestamp NULL,
	end_time timestamp NULL,
	time_spend int NULL DEFAULT -1,
	request_ip varchar(32) NULL,
	operator varchar(30) NULL,
	status smallint NULL DEFAULT 1
);

create index idx_log_start_time on business_log(start_time);
create index idx_log_operator on business_log(operator);
create index idx_log_business_desc on business_log(title);

部分测试数据可以参考后面的附录。

我们得到结构如下的表：

表结构

顺序扫描

explain select * from business_log where id>1950;

PostgreSQL 顺序扫描查询计划

更详细的信息：

explain (analyze,buffers,verbose) select * from business_log where id>1950;

PostgreSQL查询计划

索引扫描

我们只需要做一点点改动，就能让查询变成索引扫描。

explain (analyze,buffers,verbose) select id from business_log where id>1950;

把*改成id就可以。

PostgreSQL索引扫描

为什么呢？

因为我们查询的id通过索引就能查到，不用再回表了。

位图扫描

explain (analyze,buffers,verbose) select * from business_log where start_time >='2025-01-01 00:00:00';

PostgreSQL位图扫描

索引信息

-- 查看索引的大小
select pg_size_pretty(pg_relation_size('idx_log_start_time'));

-- 索引的利用率，关键是idx_scan字段表示通过这个索引扫描的次数，如果这个值比较小，那可能这个索引利用率就不高
select * from pg_stat_user_indexes where schemaname='public' and relname='business_log' and indexrelname = 'idx_log_start_time';

监控统计信息

查询计划官方文档

查询计划配置

附录A-测试数据

INSERT INTO public.business_log (title,start_time,end_time,time_spend,request_ip,"operator",status) VALUES
	 ('测试数据-1','2024-11-10 14:15:19.151','2024-11-10 14:16:16.151',57,NULL,'用户-4',1),
	 ('测试数据-7','2024-11-16 14:15:19.151','2024-11-16 14:16:24.151',65,NULL,'用户-1',1),
	 ('测试数据-7','2024-10-31 14:15:19.151','2024-10-31 14:15:25.151',6,NULL,'用户-0',1),
	 ('测试数据-0','2024-11-11 14:15:19.151','2024-11-11 14:15:45.151',26,NULL,'用户-2',1),
	 ('测试数据-6','2024-12-20 14:15:19.151','2024-12-20 14:16:13.151',54,NULL,'用户-1',1),
	 ('测试数据-8','2024-10-01 14:15:19.151','2024-10-01 14:16:12.151',53,NULL,'用户-0',1),
	 ('测试数据-7','2024-10-30 14:15:19.151','2024-10-30 14:16:01.151',42,NULL,'用户-3',1),
	 ('测试数据-6','2024-11-10 14:15:19.151','2024-11-10 14:16:45.151',86,NULL,'用户-0',1),
	 ('测试数据-2','2024-12-06 14:15:19.151','2024-12-06 14:16:43.151',84,NULL,'用户-4',1),
	 ('测试数据-0','2024-11-24 14:15:19.151','2024-11-24 14:16:02.151',43,NULL,'用户-4',1);
INSERT INTO public.business_log (title,start_time,end_time,time_spend,request_ip,"operator",status) VALUES
	 ('测试数据-3','2024-10-01 14:15:19.151','2024-10-01 14:15:29.151',10,NULL,'用户-3',1),
	 ('测试数据-1','2024-11-24 14:15:19.151','2024-11-24 14:16:26.151',67,NULL,'用户-4',1),
	 ('测试数据-2','2024-11-07 14:15:19.151','2024-11-07 14:15:26.151',7,NULL,'用户-1',1),
	 ('测试数据-9','2024-11-23 14:15:19.151','2024-11-23 14:16:30.151',71,NULL,'用户-3',1),
	 ('测试数据-0','2024-10-13 14:15:19.151','2024-10-13 14:15:26.151',7,NULL,'用户-3',1),
	 ('测试数据-2','2024-11-09 14:15:19.151','2024-11-09 14:16:50.151',91,NULL,'用户-4',1),
	 ('测试数据-0','2024-11-03 14:15:19.151','2024-11-03 14:16:46.151',87,NULL,'用户-4',1),
	 ('测试数据-9','2024-11-12 14:15:19.151','2024-11-12 14:16:40.151',81,NULL,'用户-4',1),
	 ('测试数据-4','2024-12-30 14:15:19.151','2024-12-30 14:15:57.151',38,NULL,'用户-1',1),
	 ('测试数据-1','2024-11-11 14:15:19.151','2024-11-11 14:15:57.151',38,NULL,'用户-0',1);
INSERT INTO public.business_log (title,start_time,end_time,time_spend,request_ip,"operator",status) VALUES
	 ('测试数据-2','2024-11-12 14:15:19.151','2024-11-12 14:16:44.151',85,NULL,'用户-0',1),
	 ('测试数据-2','2024-10-24 14:15:19.151','2024-10-24 14:16:50.151',91,NULL,'用户-4',1),
	 ('测试数据-0','2024-12-09 14:15:19.151','2024-12-09 14:16:03.151',44,NULL,'用户-4',1),
	 ('测试数据-6','2024-12-26 14:15:19.151','2024-12-26 14:16:31.151',72,NULL,'用户-0',1),
	 ('测试数据-0','2024-12-31 14:15:19.151','2024-12-31 14:16:45.151',86,NULL,'用户-4',1),
	 ('测试数据-0','2024-12-09 14:15:19.151','2024-12-09 14:16:35.151',76,NULL,'用户-0',1),
	 ('测试数据-5','2024-12-18 14:15:19.151','2024-12-18 14:16:51.151',92,NULL,'用户-2',1),
	 ('测试数据-8','2024-11-17 14:15:19.151','2024-11-17 14:16:41.151',82,NULL,'用户-0',1),
	 ('测试数据-9','2024-10-24 14:15:19.151','2024-10-24 14:15:44.151',25,NULL,'用户-2',1),
	 ('测试数据-4','2024-11-08 14:15:19.151','2024-11-08 14:16:40.151',81,NULL,'用户-3',1);
	 ```