1. 低效SQL排查

在数据库中安装pg_stat_statements插件，该插件记录了所有SQL语句的执行统计信息：如每个SQL语句执行的总次数和总时间，以及一些其他的SQL语句执行的性能信息。

配置pg_stat_statements到postgresql.conf文件

shared_preload_libraries = 'pg_stat_statements'

重启数据库

在数据库中创建插件

psql
create extension pg_stat_statements;

查看性能视图

postgres=# create extension pg_stat_statements;
ERROR:  extension "pg_stat_statements" already exists
postgres=# \d pg_stat_statements
                    View "public.pg_stat_statements"
       Column        |       Type       | Collation | Nullable | Default 
---------------------+------------------+-----------+----------+---------
 userid              | oid              |           |          | 
 dbid                | oid              |           |          | 
 queryid             | bigint           |           |          | 
 query               | text             |           |          | 
 calls               | bigint           |           |          | 
 total_time          | double precision |           |          | 
 min_time            | double precision |           |          | 
 max_time            | double precision |           |          | 
 mean_time           | double precision |           |          | 
 stddev_time         | double precision |           |          | 
 rows                | bigint           |           |          | 
 shared_blks_hit     | bigint           |           |          | 
 shared_blks_read    | bigint           |           |          | 
 shared_blks_dirtied | bigint           |           |          | 
 shared_blks_written | bigint           |           |          | 
 local_blks_hit      | bigint           |           |          | 
 local_blks_read     | bigint           |           |          | 
 local_blks_dirtied  | bigint           |           |          | 
 local_blks_written  | bigint           |           |          | 
 temp_blks_read      | bigint           |           |          | 
 temp_blks_written   | bigint           |           |          | 
 blk_read_time       | double precision |           |          | 
 blk_write_time      | double precision |           |          |

自定义慢SQL查询

按照"max_time"排序找出执行时间最长的10条SQL语句：

select max_time, query from pg_stat_statements order by max_time desc limit 10;

按照"calls"排序找出执行最频繁的SQL语句：

select calls, query from pg_stat_statements order by calls desc limit 10;

找出某些查询的执行时间超过了所有查询总时间的一个百分比来确定消耗时间最多的SQL语句

-- 先查询所有查询消耗的总时间
SELECT sum(total_time) AS total_time,
		sum(blk_read_time + blk_write_time) AS io_time,
		sum(total_time - blk_read_time - blk_write_time) AS cpu_time,
		sum(calls) AS ncalls,
		sum(rows) AS total_rows
FROM pg_stat_statements
WHERE dbid IN (SELECT oid FROM pg_database WHERE datname=current_database())


-- 用上述SQL作为通用表达式，定义临时表
-- 查询消耗CPU时间超过所有SQL语句消耗的总CPU时间的5%的语句
WITH total AS (
	SELECT sum(total_time) AS total_time,
			   sum(blk_read_time + blk_write_time) AS io_time,
			   sum(total_time - blk_read_time - blk_write_time) AS cpu_time,
			   sum(calls) AS ncalls, sum(rows) AS total_rows
	FROM pg_stat_statements WHERE dbid IN (
			SELECT oid FROM pg_database WHERE datname=current_database())
	)
SELECT *,(pss.total_time-pss.blk_read_time-pss.blk_write_time)/total.cpu_time*100 cpu_pct
FROM pg_stat_statements pss, total
WHERE (pss.total_time-pss.blk_read_time-pss.blk_write_time)/total.cpu_time >= 0.05
ORDER BY pss.total_time-pss.blk_read_time-pss.blk_write_time DESC;


-- 查询消耗IO时间超过所有SQL语句消耗的总IO时间的5%的语句
WITH total AS (
	SELECT sum(total_time) AS total_time,
			 	 sum(blk_read_time + blk_write_time) AS io_time,
       	 sum(total_time - blk_read_time - blk_write_time) AS cpu_time,
       	 sum(calls) AS ncalls, sum(rows) AS total_rows
	FROM pg_stat_statements WHERE dbid IN (
			SELECT oid FROM pg_database WHERE datname=current_database())
  )
SELECT *,(pss.blk_read_time + pss.blk_write_time)/total.io_time*100 io_pct
FROM pg_stat_statements pss, total
WHERE (pss.blk_read_time + pss.blk_write_time)/total.io_time >= 0.05
AND total.io_time > 0
ORDER BY pss.blk_read_time + pss.blk_write_time DESC;

在日志中打印慢SQL

# 配置postgresql.conf
# 将超过10秒的SQL语句打印在日志中
log_min_duration_statement=10000

# 日志打印结果
2023-06-09 15:48:29.738 CST [20016] LOG: duration:
11009.196 ms statement: select pg_sleep(11);

查询走全表扫描次数超过10次且尺寸大于100kb的表，找出来后可以分析因为何原因导致全表扫描

-- relid是一个内部标识符（OID），标识数据库中的每个对象（表，索引，视图等），可以用唯一标识每个表。
-- pg_relation_size(relid)可以获取每个表的实际占用空间大小。
SELECT relname,
	pg_relation_size(relid) AS rel_size,
	seq_scan, idx_scan
FROM pg_stat_all_tables
WHERE pg_relation_size(relid) > 100000
AND seq_scan > 10
AND schemaname = 'public'
ORDER BY seq_scan DESC;

2. SQL优化

最好不要用select *返回表的所有列，应用具体的字段代替"*"，不要返回用不到的任何字段。

尽量避免全表扫描和排序操作，考虑在查询条件的列和order by涉及的列上建立索引。

如果某张表经常进行一些范围查询，可以考虑使用"CLUSTER table_name USING index_name"让表中行的物理存储顺序与索引的顺序一致，以提高查询效率。

"CLUSTER table_name USING index_name"用于对指定表进行聚集（Cluster）操作，目的是重新组织表的物理存储顺序，以便与特定索引的顺序相匹配，从而提高查询性能。
聚集操作的过程会根据指定的索引对表进行重新排序，使得相邻行的数据在物理存储上更加接近，从而减少磁盘I/O的开销，提高查询效率。值得注意的是，聚集操作会阻塞表上的其他操作，并且会消耗大量的系统资源，因此需要在适当的时间进行操作。

在建立了索引的情况下，应尽量避免在WHERE子句中对字段进行函数或表达式操作，因为这会导致走不到索引。

用"EXISTS"代替"IN"来进行指定范围查询，可以带来一些优势

性能优化：使用 EXISTS 可能比 IN 在某些情况下更高效。当子查询返回大量数据时，IN 子查询需要将整个结果集加载到内存中进行比较，而 EXISTS 只需要判断是否存在匹配的行即可，避免了加载和比较大量数据的开销。
提升查询效率：在一些情况下，使用 EXISTS 可以提供更好的查询执行计划。PostgreSQL 的查询优化器可能会对 EXISTS 进行优化，例如使用索引进行更高效的查询。
灵活性：使用 EXISTS 可以更灵活地进行条件判断。可以编写复杂的子查询，包含多个条件和关联关系，而 IN 子查询只能判断单个列的匹配。

只含数值信息的字段尽量不要设计为字符型，而应该设计成数值型，因为这会降低查询和连接的性能，并会增加存储开销。

存储效率：数值类型在存储上通常比字符类型更节省空间。数值类型在内部以二进制形式进行存储，而字符类型需要额外的字节用于存储字符编码信息和字符串长度等。因此，使用数值类型可以节省存储空间，并且在处理大量数据时可以减少磁盘和内存的使用。
查询性能：将数值字段设计为数值类型可以提高查询性能。数值类型的比较和计算操作通常比字符类型更快，因为它们直接在二进制表示上进行，而不需要进行字符解析和转换。这对于涉及数值计算、排序和索引的查询非常重要。

根据业务需求和数据特点有时可能仍然需要将数值信息设计成字符类型，例如存储货币金额时可能需要保留货币符号等。

数值类型的字段尽量设计为int或bigint类型而不应该设计成numeric型。只有int或bigint的范围不能表示时，才使用numeric类型。

numeric 用于存储可变精度的数值类型，可以存储非常大或非常小的数值，numeric的存储空间根据数值的实际需要进行动态分配，通常会占用更多的存储空间。它适合存储需要精确计算的数值，例如货币金额或科学计算。

如果明知两个结果集没有重复记录，则应该使用UNION ALL而不是UNION合并两个结果集。因为UNION会对合并的结果集进行去重操作，会有额外开销。

使用COPY导入数据，比一条条地INSERT要快得多，也比“INSERT t values(),(),(),....”这样的批量插入快。

批量插入：COPY 命令允许一次性插入大量数据，而不是逐条插入。这样可以减少网络通信和数据库引擎处理的开销，提高数据加载的效率。
事务控制：使用 COPY 命令加载数据时，默认情况下会自动启用事务，并在加载完成后一次性提交事务。这样可以减少事务的开启和提交次数，提高数据加载的速度。
内部优化：COPY 命令在内部进行了一些优化，例如使用二进制复制格式，减少了数据的转换和序列化开销。此外，COPY 命令可以利用数据库引擎的并行处理能力，提高数据加载的并发性能。
索引和约束：使用 COPY 命令加载数据时，可以选择是否在加载完成后重新创建索引和约束。在大规模数据加载场景下，可以先加载数据后再重新创建索引和约束，避免了每次插入都触发索引和约束的检查，提高了数据加载的速度。

建议使用timestamp with time zone类型，而不要用timestamp without time zone类型

timestamp without time zone 类型仅存储日期和时间信息，但不包含时区信息。这意味着它对时间的解释和处理是相对于数据库服务器的本地时区。当使用 timestamp without time zone 类型时，需要非常小心地处理时区转换和应用程序中的时区设置，以确保正确的时间计算和显示。
timestamp with time zone 类型存储了日期、时间和相关的时区信息。它使用 UTC（协调世界时）作为内部存储格式，并自动进行时区转换，可以为国际化业务提供前提。

PostgreSQL数据库对于INSERT、UPDATE和DELETE语句可以通过RETURING返回行的值，可以避免二次查询，从而提高性能。

CREATE TABLE test(
  id serial primary key, 
  t text, 
  tm timestamptz default now()
);
INSERT INTO test(t) VALUES('11111') RETURNING id, tm;

-- PostgreSQL的RETURNING语法可以返回任何列的值
UPDATE test set tm=now() WHERE id=1 RETURNING tm;
tm
------------------------------
2023-06-09 17:50:59.36902+08

随机抽取数据

有时需要从一张大表中随机抽取一些数据：

create table test01(id int, t text);
insert into test01 select seq, seq || 'osdba' from
generate_series(1, 100000) as seq;

limit方式抽取数据，随机性不好

select * from test01 limit 1000;

除余方式取数据，会全表扫描，效率低

select * from test01 where id%1000 = 1;

使用数据抽样语法，直接抽样大表数据

-- “TABLESAMPLE SYSTEM(0.1)”的含义是随机找一些数据块进行抽样，抽样比例是0.1%。
SELECT * FROM test01 TABLESAMPLE SYSTEM(0.1);

1. 低效SQL排查

配置pg_stat_statements到postgresql.conf文件

shared_preload_libraries = 'pg_stat_statements'

重启数据库

在数据库中创建插件

psql
create extension pg_stat_statements;

查看性能视图

postgres=# create extension pg_stat_statements;
ERROR:  extension "pg_stat_statements" already exists
postgres=# \d pg_stat_statements
                    View "public.pg_stat_statements"
       Column        |       Type       | Collation | Nullable | Default 
---------------------+------------------+-----------+----------+---------
 userid              | oid              |           |          | 
 dbid                | oid              |           |          | 
 queryid             | bigint           |           |          | 
 query               | text             |           |          | 
 calls               | bigint           |           |          | 
 total_time          | double precision |           |          | 
 min_time            | double precision |           |          | 
 max_time            | double precision |           |          | 
 mean_time           | double precision |           |          | 
 stddev_time         | double precision |           |          | 
 rows                | bigint           |           |          | 
 shared_blks_hit     | bigint           |           |          | 
 shared_blks_read    | bigint           |           |          | 
 shared_blks_dirtied | bigint           |           |          | 
 shared_blks_written | bigint           |           |          | 
 local_blks_hit      | bigint           |           |          | 
 local_blks_read     | bigint           |           |          | 
 local_blks_dirtied  | bigint           |           |          | 
 local_blks_written  | bigint           |           |          | 
 temp_blks_read      | bigint           |           |          | 
 temp_blks_written   | bigint           |           |          | 
 blk_read_time       | double precision |           |          | 
 blk_write_time      | double precision |           |          |

自定义慢SQL查询

按照"max_time"排序找出执行时间最长的10条SQL语句：

select max_time, query from pg_stat_statements order by max_time desc limit 10;

按照"calls"排序找出执行最频繁的SQL语句：

select calls, query from pg_stat_statements order by calls desc limit 10;

找出某些查询的执行时间超过了所有查询总时间的一个百分比来确定消耗时间最多的SQL语句

-- 先查询所有查询消耗的总时间
SELECT sum(total_time) AS total_time,
		sum(blk_read_time + blk_write_time) AS io_time,
		sum(total_time - blk_read_time - blk_write_time) AS cpu_time,
		sum(calls) AS ncalls,
		sum(rows) AS total_rows
FROM pg_stat_statements
WHERE dbid IN (SELECT oid FROM pg_database WHERE datname=current_database())


-- 用上述SQL作为通用表达式，定义临时表
-- 查询消耗CPU时间超过所有SQL语句消耗的总CPU时间的5%的语句
WITH total AS (
	SELECT sum(total_time) AS total_time,
			   sum(blk_read_time + blk_write_time) AS io_time,
			   sum(total_time - blk_read_time - blk_write_time) AS cpu_time,
			   sum(calls) AS ncalls, sum(rows) AS total_rows
	FROM pg_stat_statements WHERE dbid IN (
			SELECT oid FROM pg_database WHERE datname=current_database())
	)
SELECT *,(pss.total_time-pss.blk_read_time-pss.blk_write_time)/total.cpu_time*100 cpu_pct
FROM pg_stat_statements pss, total
WHERE (pss.total_time-pss.blk_read_time-pss.blk_write_time)/total.cpu_time >= 0.05
ORDER BY pss.total_time-pss.blk_read_time-pss.blk_write_time DESC;


-- 查询消耗IO时间超过所有SQL语句消耗的总IO时间的5%的语句
WITH total AS (
	SELECT sum(total_time) AS total_time,
			 	 sum(blk_read_time + blk_write_time) AS io_time,
       	 sum(total_time - blk_read_time - blk_write_time) AS cpu_time,
       	 sum(calls) AS ncalls, sum(rows) AS total_rows
	FROM pg_stat_statements WHERE dbid IN (
			SELECT oid FROM pg_database WHERE datname=current_database())
  )
SELECT *,(pss.blk_read_time + pss.blk_write_time)/total.io_time*100 io_pct
FROM pg_stat_statements pss, total
WHERE (pss.blk_read_time + pss.blk_write_time)/total.io_time >= 0.05
AND total.io_time > 0
ORDER BY pss.blk_read_time + pss.blk_write_time DESC;

在日志中打印慢SQL

# 配置postgresql.conf
# 将超过10秒的SQL语句打印在日志中
log_min_duration_statement=10000

# 日志打印结果
2023-06-09 15:48:29.738 CST [20016] LOG: duration:
11009.196 ms statement: select pg_sleep(11);

查询走全表扫描次数超过10次且尺寸大于100kb的表，找出来后可以分析因为何原因导致全表扫描

-- relid是一个内部标识符（OID），标识数据库中的每个对象（表，索引，视图等），可以用唯一标识每个表。
-- pg_relation_size(relid)可以获取每个表的实际占用空间大小。
SELECT relname,
	pg_relation_size(relid) AS rel_size,
	seq_scan, idx_scan
FROM pg_stat_all_tables
WHERE pg_relation_size(relid) > 100000
AND seq_scan > 10
AND schemaname = 'public'
ORDER BY seq_scan DESC;

2. SQL优化

最好不要用select *返回表的所有列，应用具体的字段代替"*"，不要返回用不到的任何字段。

尽量避免全表扫描和排序操作，考虑在查询条件的列和order by涉及的列上建立索引。

如果某张表经常进行一些范围查询，可以考虑使用"CLUSTER table_name USING index_name"让表中行的物理存储顺序与索引的顺序一致，以提高查询效率。

"CLUSTER table_name USING index_name"用于对指定表进行聚集（Cluster）操作，目的是重新组织表的物理存储顺序，以便与特定索引的顺序相匹配，从而提高查询性能。
聚集操作的过程会根据指定的索引对表进行重新排序，使得相邻行的数据在物理存储上更加接近，从而减少磁盘I/O的开销，提高查询效率。值得注意的是，聚集操作会阻塞表上的其他操作，并且会消耗大量的系统资源，因此需要在适当的时间进行操作。

在建立了索引的情况下，应尽量避免在WHERE子句中对字段进行函数或表达式操作，因为这会导致走不到索引。

用"EXISTS"代替"IN"来进行指定范围查询，可以带来一些优势

性能优化：使用 EXISTS 可能比 IN 在某些情况下更高效。当子查询返回大量数据时，IN 子查询需要将整个结果集加载到内存中进行比较，而 EXISTS 只需要判断是否存在匹配的行即可，避免了加载和比较大量数据的开销。
提升查询效率：在一些情况下，使用 EXISTS 可以提供更好的查询执行计划。PostgreSQL 的查询优化器可能会对 EXISTS 进行优化，例如使用索引进行更高效的查询。
灵活性：使用 EXISTS 可以更灵活地进行条件判断。可以编写复杂的子查询，包含多个条件和关联关系，而 IN 子查询只能判断单个列的匹配。

只含数值信息的字段尽量不要设计为字符型，而应该设计成数值型，因为这会降低查询和连接的性能，并会增加存储开销。

存储效率：数值类型在存储上通常比字符类型更节省空间。数值类型在内部以二进制形式进行存储，而字符类型需要额外的字节用于存储字符编码信息和字符串长度等。因此，使用数值类型可以节省存储空间，并且在处理大量数据时可以减少磁盘和内存的使用。
查询性能：将数值字段设计为数值类型可以提高查询性能。数值类型的比较和计算操作通常比字符类型更快，因为它们直接在二进制表示上进行，而不需要进行字符解析和转换。这对于涉及数值计算、排序和索引的查询非常重要。

根据业务需求和数据特点有时可能仍然需要将数值信息设计成字符类型，例如存储货币金额时可能需要保留货币符号等。

数值类型的字段尽量设计为int或bigint类型而不应该设计成numeric型。只有int或bigint的范围不能表示时，才使用numeric类型。

numeric 用于存储可变精度的数值类型，可以存储非常大或非常小的数值，numeric的存储空间根据数值的实际需要进行动态分配，通常会占用更多的存储空间。它适合存储需要精确计算的数值，例如货币金额或科学计算。

如果明知两个结果集没有重复记录，则应该使用UNION ALL而不是UNION合并两个结果集。因为UNION会对合并的结果集进行去重操作，会有额外开销。

使用COPY导入数据，比一条条地INSERT要快得多，也比“INSERT t values(),(),(),....”这样的批量插入快。

批量插入：COPY 命令允许一次性插入大量数据，而不是逐条插入。这样可以减少网络通信和数据库引擎处理的开销，提高数据加载的效率。
事务控制：使用 COPY 命令加载数据时，默认情况下会自动启用事务，并在加载完成后一次性提交事务。这样可以减少事务的开启和提交次数，提高数据加载的速度。
内部优化：COPY 命令在内部进行了一些优化，例如使用二进制复制格式，减少了数据的转换和序列化开销。此外，COPY 命令可以利用数据库引擎的并行处理能力，提高数据加载的并发性能。
索引和约束：使用 COPY 命令加载数据时，可以选择是否在加载完成后重新创建索引和约束。在大规模数据加载场景下，可以先加载数据后再重新创建索引和约束，避免了每次插入都触发索引和约束的检查，提高了数据加载的速度。

建议使用timestamp with time zone类型，而不要用timestamp without time zone类型

timestamp without time zone 类型仅存储日期和时间信息，但不包含时区信息。这意味着它对时间的解释和处理是相对于数据库服务器的本地时区。当使用 timestamp without time zone 类型时，需要非常小心地处理时区转换和应用程序中的时区设置，以确保正确的时间计算和显示。
timestamp with time zone 类型存储了日期、时间和相关的时区信息。它使用 UTC（协调世界时）作为内部存储格式，并自动进行时区转换，可以为国际化业务提供前提。

PostgreSQL数据库对于INSERT、UPDATE和DELETE语句可以通过RETURING返回行的值，可以避免二次查询，从而提高性能。

CREATE TABLE test(
  id serial primary key, 
  t text, 
  tm timestamptz default now()
);
INSERT INTO test(t) VALUES('11111') RETURNING id, tm;

-- PostgreSQL的RETURNING语法可以返回任何列的值
UPDATE test set tm=now() WHERE id=1 RETURNING tm;
tm
------------------------------
2023-06-09 17:50:59.36902+08

随机抽取数据

有时需要从一张大表中随机抽取一些数据：

create table test01(id int, t text);
insert into test01 select seq, seq || 'osdba' from
generate_series(1, 100000) as seq;

limit方式抽取数据，随机性不好

select * from test01 limit 1000;

除余方式取数据，会全表扫描，效率低

select * from test01 where id%1000 = 1;

使用数据抽样语法，直接抽样大表数据

-- “TABLESAMPLE SYSTEM(0.1)”的含义是随机找一些数据块进行抽样，抽样比例是0.1%。
SELECT * FROM test01 TABLESAMPLE SYSTEM(0.1);

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

PostgreSQL排查SQL效率和优化方法

1. 低效SQL排查

2. SQL优化

PostgreSQL排查SQL效率和优化方法

1. 低效SQL排查

2. SQL优化

活动

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

PostgreSQL排查SQL效率和优化方法

1. 低效SQL排查

2. SQL优化

PostgreSQL排查SQL效率和优化方法

1. 低效SQL排查

2. SQL优化