openGauss Performance Tuning
大约 147 分钟
openGauss Performance Tuning
总体调优思路
openGauss 的总体性能调优思路为性能瓶颈点分析、关键参数调整以及 SQL 调优。在调优过程中，通过系统资源、吞吐量、负载等因素来帮助定位和分析性能问题，使系统性能达到可接受的范围。
openGauss 性能调优过程需要综合考虑多方面因素，因此，调优人员应对系统软件架构、软硬件配置、数据库配置参数、并发控制、查询处理和数据库应用有广泛而深刻的理解。
性能调优流程如下图：
性能调优流程说明：
| 阶段 | 描述 |
|---|---|
| 确定性能调优范围 | 获取 openGauss 节点的 CPU、内存、I/O 和网络资源使用情况，确认这些资源是否已被充分利用，是否存在瓶颈点。 |
| 系统调优指南 | 进行操作系统级以及数据库系统级的调优，更充分地利用机器的 CPU、内存、I/O 和网络资源，避免资源冲突，提升整个系统查询的吞吐量。 |
| SQL 调优指南 | 审视业务所用 SQL 语句是否存在可优化空间，包括： 通过 ANALYZE 语句生成表统计信息：ANALYZE 语句可收集与数据库中表内容相关的统计信息，统计结果存储在系统表 PG_STATISTIC 中。执行计划生成器会使用这些统计数据，以确定最有效的执行计划。 分析执行计划：EXPLAIN 语句可显示 SQL 语句的执行计划，EXPLAIN PERFORMANCE 语句可显示 SQL 语句中各算子的执行时间。 查找问题根因并进行调优：通过分析执行计划，找到可能存在的原因，进行针对性的调优，通常为调整数据库级 SQL 调优参数。 编写更优的 SQL：介绍一些复杂查询中的中间临时数据缓存、结果集缓存、结果集合并等场景中的更优 SQL 语法。 |
确定性能调优范围
数据库性能调优通常发生在用户对业务的执行效率不满意，期望通过调优加快业务执行的情况下。数据库性能受影响因素多，因而性能调优是一项复杂的工程，有些时候无法系统性地说明和解释，而是依赖于 DBA 的经验判断。尽管如此，此处还是期望能尽量系统性的对性能调优方法加以说明，方便应用开发人员和刚接触 openGauss 的 DBA 参考。
多个性能因素会影响数据库性能，了解这些因素可以帮助定位和分析性能问题。
系统资源
数据库性能在很大程度上依赖于磁盘的 I/O 和内存使用情况。为了准确设置性能指标，用户需要了解 openGauss 部署硬件的基本性能。CPU、硬盘、磁盘控制器、内存和网络接口等这些硬件性能将显著影响数据库的运行速度。
负载
负载等于数据库系统的需求总量，它会随着时间变化。总体负载包含用户查询、应用程序、并行作业、事务以及数据库随时传递的系统命令。比如：多用户在执行多个查询时会提高负载。负载会显著地影响数据库的性能。了解工作负载高峰期可以帮助用户更合理地利用系统资源，更有效地完成系统任务。
吞吐量
使用系统的吞吐量来定义处理数据的整体能力。数据库的吞吐量以每秒的查询次数、每秒的处理事务数量或平均响应时间来测量。数据库的处理能力与底层系统（磁盘 I/O、CPU 速度、存储器带宽等）有密切的关系，所以当设置数据库吞吐量目标时，需要提前了解硬件的性能。
竞争
竞争是指两组或多组负载组件尝试使用冲突的方式使用系统的情况。比如，多条查询视图同一时间更新相同的数据，或者多个大量的负载争夺系统资源。随着竞争的增加，吞吐量下降。
优化
数据库优化可以影响到整个系统的性能。在执行 SQL 制定、数据库配置参数、表设计、数据分布等操作时，启用数据库查询优化器打造最有效的执行计划。
性能调优主要通过查看 openGauss 节点的 CPU、内存、I/O 和网络这些硬件资源的使用情况，确认这些资源是否已被充分利用，是否存在瓶颈点，然后针对性调优。
如果某个资源已达瓶颈，则：
- 检查关键的操作系统参数和数据库参数是否合理设置，进行系统调优指南。
- 通过查询最耗时的SQL语句、跑不出来的SQL语句，找出耗资源的 SQL，进行 SQL 调优指南。
如果所有资源均未达瓶颈，则表明性能仍有提升潜力。可以查询最耗时的 SQL 语句，或者跑不出来的 SQL 语句，进行针对性的 SQL 调优指南。
性能日志。
硬件瓶颈点分析，获取 openGauss 节点的 CPU、内存、I/O 和网络资源使用情况，确认这些资源是否已被充分利用，是否存在瓶颈点。
查询最耗性能的 SQL。
分析作业是否被阻塞。
性能日志
性能日志主要关注外部资源的访问性能问题。
性能日志指的是数据库系统在运行时检测物理资源的运行状态的日志，在对外部资源进行访问时的性能检测，包括磁盘、OBS 等外部资源的访问检测信息。openGauss 不支持 OBS、Hadoop，所以只有磁盘访问的监控信息。
磁盘监控的访问信息主要在磁盘文件 IO 读写的时候进行统计。例如，拷贝文件时的读文件 IO，正常 SQL 执行时访问 OS 表文件的 pread 系统调用。
在出现性能问题时，可以借助性能日志及时定位问题发生的原因，能极大地提升问题解决效率。
日志文件存储路径
性能日志目录在
$GAUSSLOG/gs_profile中各自对应的目录下。日志文件命名格式
性能日志的命名规则：
postgresql-创建时间.prf默认情况下，每日 0 点或者日志文件大于 20 MB 或者数据库实例（CN、DN）重新启动后，会生成新的日志文件。
日志内容说明
每一行日志内容的默认格式：
主机名称+日期+时间+实例名称+线程号+日志内容。
性能日志收集的配置参数：
logging_collector：该参数控制是否开启日志收集功能。默认值为on，表示开启日志收集功能。plog_merge_age：控制性能日志数据输出的周期，即多久进行一次性能日志汇聚，单位为毫秒。当logging_collector参数为on，plog_merge_age大于0，且主机正常运行时，恢复过程不进行性能收集。
硬件瓶颈点分析
获取 openGauss 节点的 CPU、内存、I/O 和网络资源使用情况，确认这些资源是否已被充分利用，是否存在瓶颈点。
CPU
通过 top 命令查看 openGauss 内节点 CPU 使用情况，分析是否存在由于 CPU 负载过高导致的性能瓶颈。top 命令经常用来监控 Linux 的系统状况，是常用的性能分析工具，能够实时显示系统中各个进程的资源占用情况。
查询服务器 CPU 的使用情况主要通过以下方式：
在所有存储节点，逐一执行 top 命令，查看 CPU 占用情况。执行该命令后，按 1 键，可查看每个 CPU 核的使用率。
[root@opengauss ~]# top
top - 10:09:17 up 57 min, 2 users, load average: 0.12, 0.20, 0.15
Tasks: 168 total, 1 running, 167 sleeping, 0 stopped, 0 zombie
%Cpu0 : 1.4 us, 0.7 sy, 0.0 ni, 98.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu1 : 11.1 us, 1.4 sy, 0.0 ni, 87.5 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 3861292 total, 1872936 free, 772156 used, 1216200 buff/cache
KiB Swap: 2097148 total, 2097148 free, 0 used. 1730636 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3675 omm 20 0 6831752 1.3g 899972 S 17.9 35.9 2:30.39 gaussdb
11 root rt 0 0 0 0 S 0.3 0.0 0:00.04 watchdog/0
13 root rt 0 0 0 0 S 0.3 0.0 0:00.09 migration/1
497 root 20 0 0 0 0 S 0.3 0.0 0:03.94 xfsaild/dm-0
分析时，请主要关注进程占用的 CPU 利用率。
其中，统计信息中 us 表示用户空间占用 CPU 百分比，sy 表示内核空间占用 CPU 百分比，id 表示空闲 CPU 百分比。如果 id 低于 10%，即表明 CPU 负载较高，可尝试通过降低本节点任务量等手段降低 CPU 负载。
使用 top -H 命令查看 gaussdb 各个线程运行情况，显示内容如下所示：
[root@opengauss ~]# top -p 3675 -H
top - 10:14:05 up 1:02, 2 users, load average: 0.30, 0.27, 0.20
Threads: 38 total, 0 running, 38 sleeping, 0 stopped, 0 zombie
%Cpu(s): 6.0 us, 0.7 sy, 0.0 ni, 93.1 id, 0.2 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 3861292 total, 1871916 free, 772204 used, 1217172 buff/cache
KiB Swap: 2097148 total, 2097148 free, 0 used. 1730096 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3739 omm 20 0 6831752 1.3g 899988 S 8.6 35.9 1:09.46 ashworker
3724 omm 20 0 6831752 1.3g 899988 S 3.0 35.9 0:28.95 pagewriter
3738 omm 20 0 6831752 1.3g 899988 S 1.3 35.9 0:06.62 percentworker
3740 omm 20 0 6831752 1.3g 899988 S 1.0 35.9 0:15.22 TrackStmtWorker
3675 omm 20 0 6831752 1.3g 899988 S 0.3 35.9 0:19.55 gaussdb
3727 omm 20 0 6831752 1.3g 899988 S 0.3 35.9 0:01.71 WALwriter
3744 omm 20 0 6831752 1.3g 899988 S 0.3 35.9 0:12.71 undorecycler
3676 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.02 jemalloc_bg_thd
3685 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.00 gaussdb
3686 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.12 syslogger
3687 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:01.80 auditor
3688 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.00 jemalloc_bg_thd
3689 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.61 alarm
3690 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.00 jemalloc_bg_thd
3691 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.03 reaper
3692 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.00 jemalloc_bg_thd
3716 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.00 gaussdb
3717 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.04 gaussdb
3718 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.13 gaussdb
3720 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.56 checkpointer
3721 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.01 Spbgwriter
3722 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.94 pagewriter
3723 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.44 pagewriter
3725 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.43 pagewriter
3726 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.24 pagewriter
3728 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.03 WALwriteraux
3729 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.26 AVClauncher
3730 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.56 Jobscheduler
3731 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.23 asyncundolaunch
3732 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.19 globalstats
3733 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.32 applylauncher
3734 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.40 statscollector
3735 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.55 txnsnapcapturer
3736 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.30 CfsShrinker
3737 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.35 rbcleaner
3741 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.31 2pccleaner
3742 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.06 faultmonitor
4204 omm 20 0 6831752 1.3g 899988 S 0.0 35.9 0:00.03 worker
内存
通过 top 命令查看 openGauss 节点内存使用情况，分析是否存在由于内存占用率过高导致的性能瓶颈。
查询服务器内存的使用情况主要通过以下方式：
执行 top 命令，查看内存占用情况。执行该命令后，按 Shift+M 键，可按照内存大小排序。
[root@opengauss ~]# top
top - 10:57:58 up 1:45, 3 users, load average: 0.03, 0.09, 0.13
Tasks: 169 total, 1 running, 168 sleeping, 0 stopped, 0 zombie
%Cpu(s): 2.9 us, 0.5 sy, 0.0 ni, 96.4 id, 0.2 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 3861292 total, 1849964 free, 784364 used, 1226964 buff/cache
KiB Swap: 2097148 total, 2097148 free, 0 used. 1712968 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3675 omm 20 0 6862472 1.3g 900248 S 10.0 36.0 8:49.56 gaussdb
10240 root 20 0 0 0 0 S 0.3 0.0 0:00.13 kworker/0:2
分析时，请主要关注 gaussdb 进程占用的内存百分比（%MEM）、整系统的剩余内存。
显示信息中的主要属性解释如下：
total：物理内存总量。used：已使用的物理内存总量。free：空闲内存总量。buffers：进程使用的虚拟内存总量。%MEM：进程占用的内存百分比。VIRT：进程使用的虚拟内存总量，VIRT=SWAP+RES。SWAP：进程使用的虚拟内存中已被换出到交换分区的量。RES：进程使用的虚拟内存中未被换出的量。SHR：共享内存大小。
还可以执行 free 命令查看 Cache 的占用情况：
[root@opengauss ~]# free
total used free shared buff/cache available
Mem: 3861292 784064 1849792 884880 1227436 1713016
Swap: 2097148 0 2097148
若用户内存占用过高，需查看执行计划，是否有不合理的连接顺序。例如，多表关联时，执行计划中优先关联的两表的中间结果集比较大，导致最终执行代价比较大。
IO
通过 iostat、pidstat 命令或 openGauss 健康检查工具查看 openGauss 内节点 I/O 繁忙度和吞吐量，分析是否存在由于 I/O 导致的性能瓶颈。
查询服务器 I/O 的方法主要有以下三种方式：
- 使用
iostat命令查看 I/O 情况。此命令主要关注单个硬盘的 I/O 使用率和每秒读取、写入的数量。
[root@opengauss ~]# iostat -xm 1
Linux 3.10.0-1160.el7.x86_64 (opengauss) 04/11/2025 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
3.10 0.00 1.00 0.16 0.00 95.73
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 0.20 1.00 2.84 0.04 0.06 54.54 0.01 3.50 2.23 3.95 1.69 0.65
scd0 0.00 0.00 0.00 0.00 0.00 0.00 114.22 0.00 0.94 0.94 0.00 0.78 0.00
dm-0 0.00 0.00 0.73 3.04 0.04 0.06 53.33 0.02 4.30 2.96 4.62 1.71 0.64
dm-1 0.00 0.00 0.01 0.00 0.00 0.00 50.09 0.00 0.38 0.38 0.00 0.27 0.00
其中 rMB/s 为每秒读取的 MB 数，wMB/s 为每秒写入的 MB 数，%util 为硬盘使用率。
- 使用
pidstat命令查看 I/O 情况。此命令主要关注单个进程每秒读取、写入的数量。
[root@opengauss ~]# pidstat -d 1 5
Linux 3.10.0-1160.el7.x86_64 (opengauss) 04/11/2025 _x86_64_ (2 CPU)
11:24:50 AM UID PID kB_rd/s kB_wr/s kB_ccwr/s Command
11:24:51 AM 1000 3675 0.00 293.07 0.00 gaussdb
11:24:51 AM UID PID kB_rd/s kB_wr/s kB_ccwr/s Command
11:24:52 AM UID PID kB_rd/s kB_wr/s kB_ccwr/s Command
11:24:53 AM 1000 3675 0.00 4.00 0.00 gaussdb
11:24:53 AM UID PID kB_rd/s kB_wr/s kB_ccwr/s Command
11:24:54 AM 1000 3675 0.00 264.00 0.00 gaussdb
11:24:54 AM UID PID kB_rd/s kB_wr/s kB_ccwr/s Command
Average: UID PID kB_rd/s kB_wr/s kB_ccwr/s Command
Average: 1000 3675 0.00 112.35 0.00 gaussdb
其中 kB_rd/s 为每秒读取的 KB 数，kB_wr/s 为每秒写入的 KB 数。
- 使用
gs_checkperf工具对 openGauss 进行性能检查，需要以omm用户登录。
[omm@opengauss ~]$ gs_checkperf
Cluster statistics information:
Host CPU busy time ratio : 4.12 %
MPPDB CPU time % in busy time : 100.00 %
Shared Buffer Hit ratio : 99.47 %
In-memory sort ratio : 0
Physical Reads : 1524
Physical Writes : 19062
DB size : 73 MB
Total Physical writes : 19062
Active SQL count : 4
Session count : 10
显示结果包括每个节点的 I/O 使用情况，物理读写次数。也可以使用 gs_checkperf --detail 命令查询每个节点的详细性能信息。
网络
通过 sar 或 ifconfig 命令查看 openGauss 内节点网络使用情况，分析是否存在由于网络导致的性能瓶颈。
查询服务器网络状况的方法主要有以下两种方式：
- 使用
root用户身份登录服务器，执行如下命令查看服务器网络连接。
[root@opengauss ~]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.92.129 netmask 255.255.255.0 broadcast 192.168.92.255
ether 00:0c:29:0a:67:f4 txqueuelen 1000 (Ethernet)
RX packets 2299 bytes 187074 (182.6 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 3626 bytes 4821004 (4.5 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
loop txqueuelen 1000 (Local Loopback)
RX packets 54340 bytes 18318429 (17.4 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 54340 bytes 18318429 (17.4 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
其中：
errors表示收包错误的总数量。dropped表示数据包已经进入了 Ring Buffer，但是由于内存不够等系统原因，导致在拷贝到内存的过程中被丢弃的总数量。overruns表示 Ring Buffer 队列中被丢弃的报文数目，由于 Ring Buffer(aka Driver Queue) 传输的 IO 大于 openGauss 能够处理的 IO 导致。
分析时，如果发现上述三个值持续增长，则表示网络负载过大或者存在网卡、内存等硬件故障。
- 使用
sar命令查看服务器网络连接。
[root@opengauss ~]# sar -n DEV 1
Linux 3.10.0-1160.el7.x86_64 (opengauss) 04/11/2025 _x86_64_ (2 CPU)
11:40:12 AM IFACE rxpck/s txpck/s rxkB/s txkB/s rxcmp/s txcmp/s rxmcst/s
11:40:13 AM lo 12.00 12.00 1.41 1.41 0.00 0.00 0.00
11:40:13 AM ens33 1.00 1.00 0.06 0.21 0.00 0.00 0.00
其中 rxkB/s 为每秒接收的 KB 数，txkB/s 为每秒发送的 KB 数。分析时主要关注每个网卡的传输量和是否达到传输上限。
查询最耗性能的 SQL
系统中有些 SQL 语句运行了很长时间还没有结束，这些语句会消耗很多的系统性能。
操作步骤：
以操作系统用户
omm登录数据库节点。使用
gsql连接数据库
[omm@opengauss ~]$ gsql
gsql ((openGauss 6.0.1 build 84c20a90) compiled at 2025-01-17 17:29:32 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm@postgres=#
- 执行以下语句查询长时间运行的 SQL：
SELECT current_timestamp - query_start AS runtime, datname, usename, query FROM pg_stat_activity where state != 'idle' ORDER BY 1 desc;
查询后会按执行时间从长到短顺序返回查询语句列表，第一条结果就是当前系统中执行时间最长的查询语句。返回结果中包含了系统调用的 SQL 语句和用户执行 SQL 语句，请根据实际找到用户执行时间长的语句。
若当前系统较为繁忙，可以通过限制 current_timestamp - query_start 大于某一阈值来查看执行时间超过此阈值的查询语句。
SELECT query FROM pg_stat_activity WHERE current_timestamp - query_start > interval '1 days';
- 需要先设置参数
track_activities为on，数据库系统才会收集当前活动查询的运行信息：
omm@postgres=# set track_activities=on;
SET
- 查看正在运行的查询语句，以查看视图
pg_stat_activity为例：
omm@postgres=# SELECT datname, usename, state FROM pg_stat_activity;
datname | usename | state
----------+---------+--------
postgres | omm | idle
postgres | omm | active
postgres | omm | active
postgres | omm | active
postgres | omm | idle
postgres | omm | active
postgres | omm | idle
postgres | omm | idle
postgres | omm | idle
(9 rows)
如果 state 字段显示为 idle，则表明此连接处于空闲，等待用户输入命令。
如果仅需要查看非空闲的查询语句，则使用如下 SQL 查看：
SELECT datname, usename, state FROM pg_stat_activity WHERE state != 'idle';
- 分析长时间运行的查询语句状态。
- 若查询语句处于正常状态，则等待其执行完毕。
- 若查询语句阻塞，则通过如下命令查看当前处于阻塞状态的查询语句：
SELECT datname, usename, state, query FROM pg_stat_activity WHERE waiting = true;
查询结果中包含了当前被阻塞的查询语句，该查询语句所请求的锁资源可能被其他会话持有，正在等待持有会话释放锁资源。
说明：
只有当查询阻塞在系统内部锁资源时，
waiting字段才显示为true。尽管等待锁资源是数据库系统最常见的阻塞行为，但是在某些场景下查询也会阻塞在等待其他系统资源上，例如写文件、定时器等。但是这种情况的查询阻塞，不会在视图pg_stat_activity中体现。
分析作业是否被阻塞
数据库系统运行时，在某些业务场景下查询语句会被阻塞，导致语句运行时间过长，可以强制结束有问题的会话。
模拟阻塞的会话：
会话 1 执行更新：
stone@postgres=> begin;
BEGIN
stone@postgres=> update emp set age=30 where id=1;
UPDATE 1
会话 2 更新同一条记录：
stone@postgres=> begin;
BEGIN
stone@postgres=> update emp set name='tom' where id=1;
此时会话 2 被会话 1 阻塞。
分析阻塞的操作步骤：
以操作系统用户
omm登录数据库节点。使用
gsql连接数据库
[omm@opengauss ~]$ gsql
gsql ((openGauss 6.0.1 build 84c20a90) compiled at 2025-01-17 17:29:32 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm@postgres=#
- 执行以下语句查询阻塞者和被阻塞者：
omm@postgres=# with lock as (
select
usename,granted,locktag,query_start,query,l.pid
from
pg_locks l,
pg_stat_activity a
where
l.pid = a.pid
and locktag in (select locktag from pg_locks where granted = 'f'))
select
locker.pid locker_pid,
locker.query locker_query,
locker.usename locker_user,
locker.query_start locker_query_start,
locker.granted locker_granted,
locked.pid locked_pid,
locked.query locked_query,
locked.usename locked_user,
locked.query_start locked_query_start,
locked.granted locked_granted,
extract(epoch from now() - locked.query_start) as locked_times
from
(select * from lock where granted = 't') locker,
(select * from lock where granted = 'f') locked
where
locker.locktag = locked.locktag
order by 1;
-[ RECORD 1 ]------+--------------------------------------
locker_pid | 140637277845248
locker_query | update emp set age=30 where id=1;
locker_user | stone
locker_query_start | 2025-04-11 13:32:37.94069+08
locker_granted | t
locked_pid | 140636922181376
locked_query | update emp set name='tom' where id=1;
locked_user | stone
locked_query_start | 2025-04-11 14:48:49.942115+08
locked_granted | f
locked_times | 72.556151
- 使用如下命令结束相应的会话：
omm@postgres=# SELECT PG_TERMINATE_BACKEND(140637277845248);
pg_terminate_backend
----------------------
t
(1 row)
系统调优指南
系统调优是指进行操作系统级以及数据库系统级的调优，更充分地利用机器的 CPU、内存、I/O 和网络资源，避免资源冲突，提升整个系统查询的吞吐量。
操作系统参数调优
在性能调优过程中，可以根据实际业务情况修改关键操作系统（OS）配置参数，以提升 openGauss 数据库的性能。
在调整前，建议用户使用 gs_check 检查操作系统参数结果是否和建议值保持一致，如果不一致，用户可根据实际业务情况去手动修改。
内存相关参数设置
配置 /etc/sysctl.conf 文件，修改内存相关参数 vm.extfrag_threshold 为 1000（参考值），如果文件中没有内存相关参数，可以手动添加。
vim /etc/sysctl.conf
修改完成后，请执行如下命令，使参数生效。
sysctl -p
网络相关参数设置
- 配置
/etc/sysctl.conf文件，修改网络相关参数，如果文件中没有网络相关参数，可以手动添加。
vim /etc/sysctl.conf
修改完成后，请执行如下命令，使参数生效。
sysctl -p
网络相关参数如下表：
| 参数名 | 参考值 | 说明 |
|---|---|---|
| net.ipv4.tcp_timestamps | 1 | 表示开启 TCP 连接中 TIME-WAIT Sockets 的快速回收，默认为 0，表示关闭，1 表示打开。 |
| net.ipv4.tcp_mem | 94500000 915000000 927000000 | 第一个数字表示，当 TCP 使用的 Page 少于 94500000 时，Kernel不对其进行任何的干预。 第二个数字表示，当 TCP 使用的 Page 超过 915000000 时，Kernel会进入 “Memory Pressure” 压力模式。 第三个数字表示，当 TCP 使用的 Pages 超过 927000000 时，就会报： Out of socket memory。 |
| net.ipv4.tcp_max_orphans | 3276800 | 最大孤儿套接字（Orphan Sockets）数。 |
| net.ipv4.tcp_fin_timeout | 60 | 表示系統默认的 TIMEOUT 时间。 |
| net.ipv4.ip_local_port_range | 26000 65535 | TCP 和 UDP 能够使用的 Port 段。 |
- 设置 10 GE 网卡最大传输单元（MTU），使用
ifconfig命令设置。10 GE 网卡推荐设置为 8192，可提升网络带宽利用率。
[root@opengauss ~]# ifconfig ens33
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.92.129 netmask 255.255.255.0 broadcast 192.168.92.255
ether 00:0c:29:0a:67:f4 txqueuelen 1000 (Ethernet)
RX packets 7355 bytes 684756 (668.7 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 7666 bytes 5354475 (5.1 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
[root@opengauss ~]# ifconfig ens33 mtu 8192
[root@opengauss ~]# ifconfig ens33
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 8192
inet 192.168.92.129 netmask 255.255.255.0 broadcast 192.168.92.255
ether 00:0c:29:0a:67:f4 txqueuelen 1000 (Ethernet)
RX packets 7392 bytes 688422 (672.2 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 7690 bytes 5358089 (5.1 MiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
说明：
- 需使用
root用户设置。ens33为 10 GE 数据库内部使用的业务网卡。
- 设置 10 GE 网卡接收（RX）、发送队列（TX）长度，使用
ethtool工具设置。10 GE 网卡推荐设置为 4096，可提升网络带宽利用率。
[root@opengauss ~]# ethtool -g ens33
Ring parameters for ens33:
Pre-set maximums:
RX: 4096
RX Mini: 0
RX Jumbo: 0
TX: 4096
Current hardware settings:
RX: 256
RX Mini: 0
RX Jumbo: 0
TX: 256
[root@opengauss ~]# ethtool -G ens33 rx 4096 tx 4096
[root@opengauss ~]# ethtool -g ens33
Ring parameters for ens33:
Pre-set maximums:
RX: 4096
RX Mini: 0
RX Jumbo: 0
TX: 4096
Current hardware settings:
RX: 4096
RX Mini: 0
RX Jumbo: 0
TX: 4096
说明：
- 需使用
root用户设置。ens33为 10 GE 数据库内部使用的业务网卡。
IO 相关参数设置
通过如下命令，临时关闭透明大页：
[root@opengauss ~]# echo never > /sys/kernel/mm/transparent_hugepage/enabled
[root@opengauss ~]# echo never > /sys/kernel/mm/transparent_hugepage/defrag
永久关闭透明大页，需要将以上命令写入到 /etc/rc.d/rc.local 文件中：
[root@opengauss ~]# cat >> /etc/rc.d/rc.local << EOF
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
EOF
为 /etc/rc.d/rc.local 文件增加可执行权限并重启：
[root@opengauss ~]# chmod a+x /etc/rc.d/rc.local
[root@opengauss ~]# init 6
数据库系统参数调优
数据库内存参数调优
数据库复杂查询语句的性能非常依赖于数据库内存参数配置。数据库系统内存参数主要包括逻辑内存管理的控制参数和执行算子是否下盘的参数。
逻辑内存管理参数
使用参数 max_process_memory 控制数据库节点上可用内存的最大峰值：
omm@postgres=# SELECT * FROM pg_settings WHERE NAME='max_process_memory';
-[ RECORD 1 ]------------------------------------------------------
name | max_process_memory
setting | 2097152
unit | kB
category | Resource Usage / Memory
short_desc | Sets the maximum number of memory used by the process.
extra_desc |
context | postmaster
vartype | integer
source | configuration file
min_val | 2097152
max_val | 2147483647
enumvals |
boot_val | 12582912
reset_val | 2097152
sourcefile | /opt/huawei/install/data/dn/postgresql.conf
sourceline | 139
设置建议：
- 数据库节点上该数值需要根据系统物理内存决定。建议计算公式如下：
(物理内存大小 - vm.min_free_kbytes) * 0.7。该系数的目的是尽可能保证系统的可靠性，不会因数据库内存膨胀导致节点 OOM。这个公式中提到vm.min_free_kbytes，其含义是预留操作系统内存供内核使用，通常用作操作系统内核中通信收发内存分配，至少为 5% 内存。即，max_process_memory = 物理内存 * 0.665。- 当该值设置不合理，即大于服务器物理内存，可能导致操作系统OOM问题。 若一台机器上部署多个数据库实例时，需要缩小相关配置。
使用参数 enable_memory_limit 启用逻辑内存管理模块：
omm@postgres=# SELECT * FROM pg_settings WHERE NAME='enable_memory_limit';
-[ RECORD 1 ]-----------------------------
name | enable_memory_limit
setting | off
unit |
category | Resource Usage / Memory
short_desc | Using memory protect feature.
extra_desc |
context | postmaster
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | on
reset_val | off
sourcefile |
sourceline |
注意：
- 为了使
enable_memory_limit参数生效，max_process_memory的值需要设置足够大。若max_process_memory - shared memory - cstore_buffers - 元数据少于 2 GB，openGauss 会强制把enable_memory_limit设置为off/false。其中元数据是 openGauss 内部使用的内存，和部分并发参数相关，如max_connections、thread_pool_attr、max_prepared_transactions等。- 当该值为
off/false时，不对数据库使用的内存做限制，在大并发或者复杂查询时，使用内存过多，可能导致操作系统 OOM 问题。
使用参数 shared_buffers 设置共享内存大小：
omm@postgres=# SELECT * FROM pg_settings WHERE NAME='shared_buffers';
-[ RECORD 1 ]------------------------------------------------------------
name | shared_buffers
setting | 32768
unit | 8kB
category | Resource Usage / Memory
short_desc | Sets the number of shared memory buffers used by the server.
extra_desc |
context | postmaster
vartype | integer
source | configuration file
min_val | 16
max_val | 1073741823
enumvals |
boot_val | 1024
reset_val | 32768
sourcefile | /opt/huawei/install/data/dn/postgresql.conf
sourceline | 145
设置建议：
- 建议设置
shared_buffers值为内存的 40% 以内，一般为内存的 25%。- 如果设置较大的
shared_buffers需要同时增加checkpoint_segments的值，因为写入大量新增、修改数据需要消耗更多的时间周期。- 如果调整
shared_buffers参数之后，导致进程重启失败，请参考启动失败的报错信息，采用以下解决方案之一：
- 对应调整操作系统
kernel.shmall、kernel.shmmax、kernel.shmmin参数。- 执行
free -g观察操作系统可用内存和 Swap 空间是否足够，如果内存明显不足，请手动停止其他比较占用内存的用户程序。- 避免设置明显不合理（过大或过小）的
shared_buffers值。- 在资源池化模式下，如果主备机同时运行业务，应适当增大本参数，避免该参数设置过小导致主备机页面频繁交互，增大网络负载，严重情况下，可能出现数据库响应较慢甚至卡住的情况。
使用参数 cstore_buffers 设置列存所使用的共享缓冲区的大小：
omm@postgres=# SELECT * FROM pg_settings WHERE NAME='cstore_buffers';
-[ RECORD 1 ]-----------------------------------------------------
name | cstore_buffers
setting | 131072
unit | kB
category | Resource Usage / Memory
short_desc | Sets the number of CStore buffers used by the server.
extra_desc |
context | postmaster
vartype | integer
source | configuration file
min_val | 16384
max_val | 1073741823
enumvals |
boot_val | 131072
reset_val | 131072
sourcefile | /opt/huawei/install/data/dn/postgresql.conf
sourceline | 160
设置建议：
列存表使用
cstore_buffers设置的共享缓冲区，几乎不用shared_buffers。因此在列存表为主的场景中，应减少shared_buffers，增加cstore_buffers。
在启动 openGauss 时如果出现如下警告：
WARNING: Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (128 Mbytes) or shared memory (2281 Mbytes) is larger.
表示由于 cstore_buffers 或者共享内存（Shared Memory，包括 shared_buffers）过大，导致 max_process_memory - shared memory - cstore_buffers - 元数据 少于 2 GB，故无法设置内存保护。这里需要注意共享内存（Shared Memory）的计算方法：
- 当
shared_buffers为 KB 时，共享内存为 1835 MB。 - 当
shared_buffers为 MB 时，共享内存为 2 GB +shared_buffers。 - 当
shared_buffers为 GB 时，共享内存为 4 GB +shared_buffers。
故如果 shared_buffers 设置为 1 GB，则 max_process_memory 至少为 8 GB 才能够启用内存保护，同时物理内存则至少为 12 GB。
执行作业最终可用的内存为：max_process_memory – shared memory ( 包括shared_buffers ) – cstore_buffers。
逻辑内存管理有专门的视图查询数据库节点中各大块内存区域已使用内存及峰值信息。可连接到单个数据库节点，通过 pg_total_memory_detail 查询该节点上内存区域信息；或者连接到数据库主节点，通过 pg_total_memory_detail 查询节点上内存区域信息。如果参数 enable_memory_limit 为 off，则无法查询视图 pg_total_memory_detail ，并报错 ERROR: unsupported view for memory protection feature is disabled.
omm@postgres=# select * from pg_total_memory_detail;
nodename | memorytype | memorymbytes
----------+-------------------------+--------------
dn_6001 | max_process_memory | 6144
dn_6001 | process_used_memory | 1310
dn_6001 | max_dynamic_memory | 2293
dn_6001 | dynamic_used_memory | 616
dn_6001 | dynamic_peak_memory | 626
dn_6001 | dynamic_used_shrctx | 192
dn_6001 | dynamic_peak_shrctx | 192
dn_6001 | max_backend_memory | 660
dn_6001 | backend_used_memory | 2
dn_6001 | max_shared_memory | 3062
dn_6001 | shared_used_memory | 862
dn_6001 | max_cstore_memory | 128
dn_6001 | cstore_used_memory | 0
dn_6001 | max_sctpcomm_memory | 0
dn_6001 | sctpcomm_used_memory | 0
dn_6001 | sctpcomm_peak_memory | 0
dn_6001 | other_used_memory | 0
dn_6001 | gpu_max_dynamic_memory | 0
dn_6001 | gpu_dynamic_used_memory | 0
dn_6001 | gpu_dynamic_peak_memory | 0
dn_6001 | pooler_conn_memory | 0
dn_6001 | pooler_freeconn_memory | 0
dn_6001 | storage_compress_memory | 0
dn_6001 | udf_reserved_memory | 0
(24 rows)
以上结果来自于如下参数设置：
max_process_memory = 6GBshared_buffers = 512MBcstore_buffers = 128MB
使用参数 work_mem 设置内部排序操作和 Hash 表在开始写入临时磁盘文件之前使用的内存大小：
omm@postgres=# SELECT * FROM pg_settings WHERE NAME='work_mem';
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------------------------
name | work_mem
setting | 65536
unit | kB
category | Resource Usage / Memory
short_desc | Sets the maximum memory to be used for query workspaces.
extra_desc | This much memory can be used by each internal sort operation and hash table before switching to temporary disk files.
context | user
vartype | integer
source | configuration file
min_val | 64
max_val | 2147483647
enumvals |
boot_val | 65536
reset_val | 65536
sourcefile | /opt/huawei/install/data/dn/postgresql.conf
sourceline | 156
设置建议：
- 对于复杂的查询，可能会同时并发运行好几个排序或者散列操作，每个都可以使用此参数所声明的内存量，不足时会使用临时文件。同样，好几个正在运行的会话可能会同时进行排序操作。因此使用的总内存可能是
work_mem的好几倍。- 依据查询特点和并发来确定，一旦
work_mem限定的物理内存不够，算子运算数据将写入临时表空间，带来 5-10 倍的性能下降，查询响应时间从秒级下降到分钟级。
- 对于串行无并发的复杂查询场景，平均每个查询有 5-10 关联操作，建议
work_mem=50%内存/10。- 对于串行无并发的简单查询场景，平均每个查询有 2-5 个关联操作，建议
work_mem=50%内存/5。- 对于并发场景，建议
work_mem=串行下的work_mem/物理并发数。- 对于 BitmapScan 的哈希表也会受到
work_mem的限制，但不会被严格管控下盘。完全 Lossify 的情况下，哈希表每占用 1 MB 的内存，对应一次 BitmapHeapScan 的 16 GB 的页面（Ustore 为 32 GB），达到work_mem上限后，会按此比例随数据访问量线性增长。
使用参数 maintenance_work_mem 设置在维护性操作（比如 VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY 等）中可使用的最大的内存：
omm@postgres=# SELECT * FROM pg_settings WHERE NAME='maintenance_work_mem';
-[ RECORD 1 ]--------------------------------------------------------------
name | maintenance_work_mem
setting | 131072
unit | kB
category | Resource Usage / Memory
short_desc | Sets the maximum memory to be used for maintenance operations.
extra_desc | This includes operations such as VACUUM and CREATE INDEX.
context | user
vartype | integer
source | configuration file
min_val | 1024
max_val | 2147483647
enumvals |
boot_val | 16384
reset_val | 131072
sourcefile | /opt/huawei/install/data/dn/postgresql.conf
sourceline | 157
设置建议：
建议设置此参数的值大于
work_mem可以改进清理和恢复数据库转储的速度。因为在一个数据库会话里，任意时刻只有一个维护性操作可以执行，并且在执行维护性操作时不会有太多的会话。当自动清理线程运行时，
autovacuum_max_workers倍数的内存将会被分配，所以此时设置maintenance_work_mem的值应该不小于work_mem。
当物理内存分别为 8 GB，16 GB，32 GB，64 GB，128 GB 时的内存配置：
- 物理内存 8 GB
max_process_memory = 6GB # 8×0.75
shared_buffers = 512MB # 小于1GB，以启用内存保护
cstore_buffers = 128MB # 列存较少时
work_mem = 64MB # 并发约10时
maintenance_work_mem = 128MB # 避免挤压业务内存
- 物理内存 16GB
max_process_memory = 12GB # 16×0.75
shared_buffers = 4GB # 25%（混合负载）
cstore_buffers = 1GB # 列存中等
work_mem = 128MB # 并发约15时
maintenance_work_mem = 1GB # 常规维护
- 物理内存 32GB
max_process_memory = 25GB # 32×0.78
shared_buffers = 8GB # 25%（OLTP主导）
cstore_buffers = 3GB # 列存占比30%
work_mem = 256MB # 并发约20时
maintenance_work_mem = 1.5GB # 支持更大索引创建
- 物理内存 64GB
max_process_memory = 50GB # 64×0.78
shared_buffers = 16GB # 25%（OLTP）
cstore_buffers = 6GB # 列存占比40%
work_mem = 512MB # 低并发复杂查询
maintenance_work_mem = 2GB # 支持TB级表VACUUM
- 物理内存 128GB
max_process_memory = 100GB # 128×0.78
shared_buffers = 32GB # 25%（OLTP）
cstore_buffers = 20GB # 列存主导场景
work_mem = 1GB # 低并发+分析型负载
maintenance_work_mem = 4GB # 超大规模数据维护
执行算子是否下盘的参数
参数 work_mem 可以判断执行作业可下盘算子是否已使用内存量触发下盘点。
当前可下盘算子有六类（向量化及非向量化共 10 种）：
- Hash(VecHashJoin)
- Agg(VecAgg)
- Sort(VecSort)
- Material(VecMaterial)
- SetOp(VecSetOp)
- WindowAgg(VecWindowAgg)
该参数设置通常是一个权衡，即要保证并发的吞吐量，又要保证单查询作业的性能，故需要根据实际执行情况（结合 Explain Performance 输出）进行调优。
配置 Ustore
Ustore 存储引擎，又名 In-place Update 存储引擎（原地更新），是 openGauss 内核新增的一种存储模式。此前的版本使用的行存储引擎是 Append Update（追加更新）模式。追加更新对于业务中的增、删以及 HOT（HeapOnly Tuple）Update（即同一页面内更新）有很好的表现，但对于跨数据页面的非 HOT UPDATE 场景，垃圾回收不够高效。因此，Ustore 存储引擎应运而生。
Ustore 存储引擎将最新版本的 “有效数据” 和历史版本的 “垃圾数据” 分离存储。将最新版本的 “有效数据” 存储在数据页面上，并单独开辟一段 UNDO 空间，用于统一管理历史版本的 “垃圾数据”，因此数据空间不会由于频繁更新而膨胀，“垃圾数据” 集中回收效率更高。
Ustore 存储引擎采用 NUMA-Aware 的 UNDO 子系统设计，使得 UNDO 子系统可以在多核平台上有效扩展；同时采用多版本索引技术，解决索引清理问题，有效提升了存储空间的回收复用效率。
Ustore 存储引擎结合 UNDO 空间，可以实现更高效、更全面的闪回查询和回收站机制，能快速回退人为 “误操作”，为 openGauss 提供了更丰富的企业级功能。
核心优势：
- 高性能： 对插入、更新、删除等不同负载的业务，性能以及资源使用表现相对均衡。更新操作采用原地更新模式在频繁更新类的业务场景下可拥有更高、更平稳的性能表现。适应 “短”（事务短）、“频”（更新操作频繁）、“快”（性能要求高）的典型 OLTP 类业务场景。
- 高效存储： 支持最大限度的原位更新，极大节约了空间；将回滚段、数据页面分离存储，具备更高效、平稳的 IO 使用能力，UNDO 子系统采用 NUMA-Aware 设计，具有更好的多核扩展性，UNDO 空间统一分配，集中回收，复用效率更高，存储空间使用更加高效、平稳。
- 细粒度资源控制： Ustore 引擎提供多维度的事务 “监管” 方式，可基于事务运行时长、单事务使用 UNDO 空间大小、以及整体 UNDO 空间限制等方式对事务运行进行 “监管”，防止异常、非预期内的行为出现，方便数据库管理员对数据库系统资源使用进行规范和约束。
Ustore 存储引擎可以在数据频繁更新场景下性能依旧稳如泰山，使业务系统运行更加平稳，适应更多业务场景和工作负载，特别是对性能和稳定性有更高要求的金融核心业务场景。
创建存储引擎为 Ustore 的表有以下两种方式：
- 在创建表的语句后面加上
storage_type=ustore
stone@postgres=> create table test(id int, age int, name varchar(10)) with (storage_type=ustore);
CREATE TABLE
stone@postgres=> \dt test
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+-------+------------------------------------------------------
stone | test | table | stone | {orientation=row,storage_type=ustore,compression=no}
(1 row)
stone@postgres=> \d+ test
Table "stone.test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
age | integer | | plain | |
name | character varying(10) | | extended | |
Has OIDs: no
Options: orientation=row, storage_type=ustore, compression=no
- 配置参数
enable_default_ustore_table为on，默认指定用户创建表时使用 Ustore 存储引擎
[omm@opengauss ~]$ gs_guc reload -c "enable_default_ustore_table = on"
The gs_guc run with the following arguments: [gs_guc -c enable_default_ustore_table = on reload ].
NOTICE: Enable to create ustore table by default
expected instance path: [/opt/huawei/install/data/dn/postgresql.conf]
gs_guc reload: enable_default_ustore_table=on: [/opt/huawei/install/data/dn/postgresql.conf]
server signaled
Total instances: 1. Failed instances: 0.
Success to perform gs_guc!
stone@postgres=> create table test(id int, age int, name varchar(10));
CREATE TABLE
stone@postgres=> \dt test
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+-------+------------------------------------------------------
stone | test | table | stone | {orientation=row,compression=no,storage_type=ustore}
(1 row)
stone@postgres=> \d+ test
Table "stone.test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
age | integer | | plain | |
name | character varying(10) | | extended | |
Has OIDs: no
Options: orientation=row, compression=no, storage_type=ustore
SQL调优指南
SQL 调优的唯一目的是 “资源利用最大化”，即 CPU、内存、磁盘 IO 三种资源利用最大化。所有调优手段都是围绕资源使用开展的。
所谓资源利用最大化是指 SQL 语句尽量高效，节省资源开销，以最小的代价实现最大的效益。比如做典型点查询的时候，可以用 Seqscan + Filter（即读取每一条元组和点查询条件进行匹配）实现，也可以通过 Indexscan 实现，显然 Indexscan 可以以更小的代价实现相同的效果。
根据硬件资源和客户的业务特征确定合理的 openGauss 部署方案和表定义是数据库在多数情况下满足性能要求的基础。下文的调优说明假设您已根据 “软件安装” 指引在安装过程中按照合理的 openGauss 方案完成了安装，且已经根据 “开发设计建议” 的指引进行了数据库设计。
查询执行流程
SQL 引擎从接受 SQL 语句到执行 SQL 语句需要经历的步骤如下，其中红色字体部分为 DBA 可以介入实施调优的环节。
SQL 引擎执行查询类 SQL 语句的流程：
SQL 引擎执行查询类 SQL 语句的步骤：
| 步骤 | 说明 |
|---|---|
| 1、语法&词法解析 | 按照约定的 SQL 语句规则，把输入的 SQL 语句从字符串转化为格式化结构（Stmt）。 |
| 2、语义解析 | 将 “语法&词法解析” 输出的格式化结构转化为数据库可以识别的对象。 |
| 3、查询重写 | 根据规则把 “语义解析” 的输出等价转化为执行上更为优化的结构。 |
| 4、查询优化 | 根据 “查询重写” 的输出和数据库内部的统计信息规划 SQL 语句具体的执行方式，也就是执行计划。 |
| 5、查询执行 | 根据 “查询优化” 规划的执行路径执行 SQL 查询语句。底层存储方式的选择合理性，将影响查询执行效率。 |
调优手段之统计信息
openGauss 优化器是典型的基于代价的优化（Cost-Based Optimization，简称 CBO）。在这种优化器模型下，数据库根据表的元组数、字段宽度、NULL 记录比率、DISTINCT 值、MCV 值、HB 值等表的特征值，以及一定的代价计算模型，计算出每一个执行步骤的不同执行方式的输出元组数和执行代价（Cost），进而选出整体执行代价最小 / 首元组返回代价最小的执行方式进行执行。这些特征值就是统计信息。
从上面的描述可以看出统计信息是查询优化的核心输入，准确的统计信息将帮助优化器选择最合适的执行计划。
一般来说我们会通过 ANALYZE 语法收集整个表或者表的若干个字段的统计信息，周期性地运行 ANALYZE，或者在对表的大部分内容做了更改之后马上运行 ANALYZE。
调优手段之 GUC 参数
查询优化的主要目的是为查询语句选择高效的执行方式。
如下 SQL 语句：
select count(1)
from customer inner join store_sales on (ss_customer_sk = c_customer_sk);
在执行 customer inner join store_sales 的时候，openGauss 支持 Nested Loop、Merge Join 和 Hash Join 三种不同的 Join 方式。优化器会根据表 customer 和表 store_sales 的统计信息估算结果集的大小以及每种 Join 方式的执行代价，然后对比选出执行代价最小的执行计划。
正如前面所说，执行代价计算都是基于一定的模型和统计信息进行估算，当因为某些原因代价估算不能反映真实的 Cost 的时候，我们就需要通过 GUC 参数设置的方式让执行计划倾向更优规划。
调优手段之底层存储
openGauss 的表支持行存表、列存表，底层存储方式的选择严格依赖于客户的具体业务场景。
一般来说，计算型业务查询场景（以关联、聚合操作为主）建议使用列存表；点查询、大批量 UPDATE/DELETE 业务场景适合行存表。
对于每种存储方式还有对应的存储层优化手段，这部分会在后续的调优章节深入介绍。
调优手段之 SQL 重写
除了上述干预 SQL 引擎所生成执行计划的执行性能外，根据数据库的 SQL 执行机制以及大量的实践发现，在某些场景下，在保证客户业务 SQL 逻辑的前提下，由 DBA 依据一定规则重写 SQL 语句，能够大幅提升 SQL 语句的性能。
这种调优方式对 DBA 的要求较高，不仅需要 DBA 充分了解客户业务，还要求其具备扎实的 SQL 功底。后续将介绍几个常见的 SQL 改写场景。
SQL 执行计划
概述
什么是执行计划：
- openGauss 的执行计划是指数据库系统在执行 SQL 语句时，根据查询条件和表结构等信息，生成的一种执行计划，用于指导数据库系统如何执行查询操作。
- 执行计划包括了查询语句的各个操作步骤、操作顺序、使用的索引、表的访问方式等信息，是优化查询性能的关键。通过分析执行计划，可以了解查询语句的执行情况，找到查询性能瓶颈，进而优化查询性能。
- 执行计划在结构上也是一棵节点树，每个节点代表执行过程中使用的数据库基础运算符。在默认的输出方式（Normal）中，通过缩进的方式来区别处于节点树不同层的节点，即先从顶层节点输出，对顶层节点的左右子树节点进行缩进后再输出。
- 执行计划将显示 SQL 语句所引用的表会采用什么样的扫描方式，如：简单的顺序扫描、索引扫描等。如果引用了多个表，执行计划还会显示用到的 JOIN 算法。
- 执行计划的最关键的部分是语句的预计执行开销，这是计划生成器估算执行该语句将花费多长的时间。
- 若指定了 ANALYZE 选项，则该语句会被执行，然后根据实际的运行结果显示统计数据，包括每个计划节点内时间总开销（毫秒为单位）和实际返回的总行数。这对于判断计划生成器的估计是否接近现实非常有用。
获取执行计划的语法：
EXPLAIN [ ( option [, ...] ) ] statement;
EXPLAIN { [ { ANALYZE | ANALYSE } ] [ VERBOSE ] | PERFORMANCE } statement;
其中 option 子句的语法：
ANALYZE [ boolean ] |
ANALYSE [ boolean ] |
VERBOSE [ boolean ] |
COSTS [ boolean ] |
CPU [ boolean ] |
DETAIL [ boolean ] |(不可用)
NODES [ boolean ] |(不可用)
NUM_NODES [ boolean ] |(不可用)
BUFFERS [ boolean ] |
TIMING [ boolean ] |
PLAN [ boolean ] |
FORMAT { TEXT | XML | JSON | YAML }
参数说明：
statement：指定要分析的 SQL 语句。ANALYZE boolean | ANALYSE boolean：显示实际运行时间和其他统计数据。取值范围：TRUE（缺省值）：显示实际运行时间和其他统计数据。
FALSE：不显示。
VERBOSE boolean：显示有关计划的额外信息。取值范围：TRUE（缺省值）：显示额外信息。
FALSE：不显示。
COSTS boolean：包括每个规划节点的估计总成本，以及估计的行数和每行的宽度。取值范围：TRUE（缺省值）：显示估计总成本和宽度。
FALSE：不显示。
CPU boolean：打印 CPU 的使用情况的信息。取值范围：TRUE（缺省值）：显示 CPU 的使用情况。
FALSE：不显示。
DETAIL boolean（不可用）：打印数据库节点上的信息。取值范围：TRUE（缺省值）：打印数据库节点的信息。
FALSE：不打印。
NODES boolean（不可用）：打印 query 执行的节点信息。取值范围：TRUE（缺省值）：打印执行的节点的信息。
FALSE：不打印。
NUM_NODES boolean（不可用）：打印执行中的节点的个数信息。取值范围：TRUE（缺省值）：打印数据库节点个数的信息。
FALSE：不打印。
BUFFERS boolean：包括缓冲区的使用情况的信息。取值范围：TRUE：显示缓冲区的使用情况。
FALSE（缺省值）：不显示。
TIMING boolean：包括实际的启动时间和花费在输出节点上的时间信息。取值范围：TRUE（缺省值）：显示启动时间和花费在输出节点上的时间信息。
FALSE：不显示。
PLAN：是否将执行计划存储在 PLAN_TABLE 中。当该选项开启时，会将执行计划存储在 PLAN_TABLE 中，不打印到当前屏幕，因此该选项为on时，不能与其他选项同时使用。取值范围：ON（缺省值）：将执行计划存储在 PLAN_TABLE 中，不打印到当前屏幕。执行成功返回 EXPLAIN SUCCESS。
OFF：不存储执行计划，将执行计划打印到当前屏幕。
FORMAT：指定输出格式。取值范围：TEXT、XML、JSON 和 YAML。默认值：TEXT。PERFORMANCE：使用此选项时，即打印执行中的所有相关信息。
注意：
- 在指定 ANALYZE 选项时，语句会被执行。
- 如果用户想使用 EXPLAIN 分析 INSERT、UPDATE、DELETE、CREATE TABLE AS 或 EXECUTE 语句，而不想改动数据（执行这些语句会影响数据），可以使用事务回滚方式：
START TRANSACTION; EXPLAIN ANALYZE ...; ROLLBACK;
使用 EXPLAIN 命令能够查看优化器为每个查询生成的具体执行计划。EXPLAIN 会为每个执行节点输出一行内容，显示基本的节点类型以及优化器为执行该节点所预计的开销值。
openGauss=# explain select * from t1, t2 where t1.c1=t2.c2;
QUERY PLAN
--------------------------------------------------------------
Hash Join (cost=58.35..355.67 rows=23091 width=16)
-> Seq Scan on t1 (cost=0.00..31.49 rows=2149 width=8)
-> Hash (cost=31.49..31.49 rows=2149 width=8)
-> Seq Scan on t2 (cost=0.00..31.49 rows=2149 width=8)
(5 rows)
- 最底层节点是表扫描节点，它负责扫描表并返回原始数据行。不同的表访问模式有不同的扫描节点类型，例如顺序扫描、索引扫描等。此外，最底层节点的扫描对象也可能是非表行数据（即不是直接从表中读取的数据），如
VALUES子句和返回行集的函数，它们都有各自的扫描节点类型。 - 如果查询需要进行连接、聚集、排序或者对原始行执行其他操作，那么就会在扫描节点之上添加其他节点。而且这些操作通常存在多种实现方法，所以在这些位置也可能出现不同的执行节点类型。
- 第一行（也就是最上层节点）是执行计划总执行开销的预计值。这个数值正是优化器试图最小化的数值。
除了设置不同的执行计划显示格式外，还可以通过不同的 EXPLAIN 用法，显示不同详细程度的执行计划信息。常见有如下几种：
EXPLAIN statement：只生成执行计划，不实际执行。其中statement代表 SQL 语句。EXPLAIN ANALYZE statement：生成执行计划，进行执行，并显示执行的概要信息。显示中加入了实际的运行时间统计，包括在每个规划节点内部花掉的总时间（以毫秒计）和它实际返回的行数。EXPLAIN PERFORMANCE statement：生成执行计划，进行执行，并显示执行期间的全部信息。
为了测量运行时在执行计划中每个节点的开销，EXPLAIN ANALYZE 或 EXPLAIN PERFORMANCE 会在当前查询执行上增加性能分析的开销。在一个查询上运行 EXPLAIN ANALYZE 或 EXPLAIN PERFORMANCE 有时会比普通查询明显的花费更多的时间。超支的数量依赖于查询的本质和使用的平台。
因此，当定位 SQL 运行慢问题时，如果 SQL 长时间运行未结束，建议通过 EXPLAIN 命令查看执行计划，进行初步定位。如果 SQL 可以运行出来，则推荐使用 EXPLAIN ANALYZE 或 EXPLAIN PERFORMANCE 查看执行计划及其实际的运行信息，以便更精准地定位问题原因。
详解
在这一节将详细解释执行计划及执行信息。
以如下 SQL 语句为例：
SELECT * FROM t1, t2 WHERE t1.c1 = t2.c2;
执行 EXPLAIN 的输出为：
openGauss=# explain select * from t1, t2 where t1.c1=t2.c2;
QUERY PLAN
--------------------------------------------------------------
Hash Join (cost=58.35..355.67 rows=23091 width=16)
-> Seq Scan on t1 (cost=0.00..31.49 rows=2149 width=8)
-> Hash (cost=31.49..31.49 rows=2149 width=8)
-> Seq Scan on t2 (cost=0.00..31.49 rows=2149 width=8)
(5 rows)
执行计划层级解读（纵向）：
- 第一层
Seq Scan on t2：表扫描算子，用 Seq Scan 的方式扫描表 T2。这一层的作用是把表 T2 的数据从 Buffer 或者磁盘上读上来输送给上层节点参与计算。 - 第二层
Hash：Hash 算子，作用是把下层计算输送上来的算子计算 Hash 值，为后续 Hash Join 操作做数据准备。 - 第三层
Seq Scan on t1：表扫描算子，用 Seq Scan 的方式扫描表 T1。这一层的作用是把表 T1 的数据从 Buffer 或者磁盘上读上来输送给上层节点参与 Hash Join计算。 - 第四层
Hash Join：Join 算子，主要作用是将 T1 表和 T2 表的数据通过 Hash Join 的方式连接，并输出结果数据。
执行计划中的关键字说明：
- 表访问方式
Seq Scan：全表顺序扫描。Index Scan：优化器采用两步计划：子计划节点访问索引，以查找与索引条件匹配的行的位置，然后上层计划节点从表中实际获取这些行。单独获取行的成本比顺序读取要高得多，但由于无需访问表的所有页面，因此这仍比顺序扫描成本低。上层计划节点在读取索引标识的行之前，会根据它们的物理位置对其进行预排序。这将独立获取的开销降至最低。如果在 WHERE 里面使用的好几个字段上都有索引，那么优化器可能会使用索引的 AND 或 OR 的组合。但是这么做要求访问两个索引，因此与只使用一个索引，而把另外一个条件只当作过滤器相比，这个方法未必是更优。索引扫描可以分为以下几类，他们之间的差异在于索引的排序机制。Bitmap Index Scan：使用位图索引抓取数据页。Index Scan using index_name：以索引顺序获取表行数据，这会导致读取操作的成本更高。然而，由于需要读取的行数极少，对行位置进行排序的额外开销就显得没有必要。这种执行计划类型主要应用于以下两种场景：一是仅需要获取单行数据的查询，二是查询中包含与索引顺序相匹配的 ORDER BY 条件的情况，因为在这种情况下无需额外的排序步骤即可满足 ORDER BY 要求。
- 表连接方式
Nested Loop：嵌套循环，适用于被连接的数据子集较小的查询。在嵌套循环中，外表驱动内表，外表返回的每一行都要在内表中检索找到它匹配的行，因此整个查询返回的结果集不能太大（不能大于10000），要把返回子集较小的表作为外表，而且在内表的连接字段上建议要有索引。- (Sonic)
Hash Join：哈希连接，适用于数据量大的表的连接方式。优化器使用两个表中较小的表，利用连接键在内存中建立 Hash 表，然后扫描较大的表并探测散列，找到与散列匹配的行。Sonic 和非 Sonic 的 Hash Join 的区别在于所使用 Hash 表结构不同，不影响执行的结果集。 Merge Join：合并连接，通常情况下执行性能差于哈希连接。如果源数据已经被排序过，在执行连接时，并不需要再排序，此时合并连接的性能优于哈希连接。
- 运算符
sort：对结果集进行排序。filter：EXPLAIN的输出结果显示，WHERE子句作为附加在顺序扫描（Seq Scan）计划节点上的过滤（Filter）条件来应用。这意味着该计划节点会对其扫描的每一行检查该条件，并且只返回符合条件的行。由于WHERE子句的存在，估计的输出行数已经减少了。然而，扫描操作仍然必须访问全部 10000 行数据，因此，成本并未降低，反而略有增加（增加了 10000 乘以cpu_operator_cost），以反映在检查WHERE条件上所花费的额外 CPU 时间。LIMIT：限定执行结果的输出记录数。如果增加了LIMIT，那么不是所有的行都会被检索到。
执行 EXPLAIN ANALYZE 的输出为：
stone@postgres=> CREATE TABLE t1(a INT, b INT);
CREATE TABLE
stone@postgres=> INSERT INTO t1 SELECT x,x FROM generate_series(1,10) x;
INSERT 0 10
stone@postgres=> ANALYZE t1;
ANALYZE
stone@postgres=> EXPLAIN ANALYZE SELECT * FROM t1 WHERE a=1;
QUERY PLAN
--------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..1.12 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=1)
Filter: (a = 1)
Rows Removed by Filter: 9
Total runtime: 0.052 ms
(4 rows)
Seq Scan对应的是全表扫描算子，cost代表算子的代价，其中0.00为启动代价（返回第一条数据的代价），1.12为算子全部代价，rows则是算子估算的结果集大小，width为每行记录的平均宽度。actual time是算子的实际执行时间，0.013是返回第一条记录的时间，0.015是整个算子的执行时间，rows是实际行数，loops则是重复了多少次。Rows Removed by Filter则说明了过滤条件筛选掉的行数，Total runtime这个字段是整个执行器实际的执行时间。
使用 EXPLAIN PERFORMANCE 的输出为：
stone@postgres=> EXPLAIN PERFORMANCE SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department = d.department_name;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=21.14..73.31 rows=2626 distinct=[200, 200] width=111) (actual time=0.005..0.005 rows=0 loops=1)
Output: e.name, d.department_name
Hash Cond: ((d.department_name)::text = (e.department)::text)
(CPU: ex c/r=0, ex row=0, ex cyc=9910, inc cyc=81710033143518)
-> Seq Scan on stone.departments d (cost=0.00..20.61 rows=1061 width=43) (actual time=0.001..0.001 rows=0 loops=1)
Output: d.department_id, d.department_name
(CPU: ex c/r=0, ex row=0, ex cyc=81710033133608, inc cyc=81710033133608)
-> Hash (cost=14.95..14.95 rows=495 width=111) (Actual time: never executed)
Output: e.name, e.department
Buckets: 0 Batches: 0 Memory Usage: 0kB
(CPU: ex c/r=0, ex row=0, ex cyc=0, inc cyc=0)
-> Seq Scan on stone.employees e (cost=0.00..14.95 rows=495 width=111) (Actual time: never executed)
Output: e.name, e.department
(CPU: ex c/r=0, ex row=0, ex cyc=0, inc cyc=0)
Total runtime: 0.104 ms
(15 rows)
Buckets：代表在 Hash Join 计算中实际使用到的桶的个数。Batches：代表在 Hash Join 计算中实际分块的数量。- 若
Batches=1，说明 Hash 的计算全都在内存中，没有下盘操作。 - 若
Batches>1，说明数据下盘，Batches的个数就是实际使用的临时文件的个数。
- 若
Memory Usage：即 Hash Join 中内存的使用情况。
核心算子原理
openGauss 执行计划中每个节点的算子按照操作方式的不同，可以分为：扫描类，控制类，物化类，连接类， Stream类。
扫描类算子
扫描类算子负责从底层数据来源抽取数据，数据来源可能是来自文件系统，也可能来自网络（分布式查询）。一般而言扫描类算子都位于执行树的叶子节点，作为执行树 PlanTree 的数据输入来源。
扫描类算子有：
| 算子 | 说明 | 场景 |
|---|---|---|
| SeqScan | 行存储引擎顺序扫描 | 基本的扫描算子，用于按扫描方向顺序扫描普通物理行存表。 |
| CstoreScan | 扫描列存储引擎 | 基本的列引擎扫描算子，用于扫描普通列存表。 |
| SubQueryScan | 子查询扫描 | 以另一个子查询的结果集作为当前查询的输入。 |
| BitmapHeapScan | 利用 Bitmap 获取元组 | Bitmap 利用属性上的索引进行扫描，返回结果为一个位图（标记了满足条件的元组在页面中的偏移量 ）。 |
| BitmapIndexScan | 利用 Bitmap 获取元组 | Bitmap 利用属性上的索引进行扫描，返回结果为一个位图（标记了满足条件的元组在页面中的偏移量 ）。 |
| TidScan | 通过 Tid 获取元组 | 通过对表 table.ctid 字段进行过滤和查找，例如 where table.ctid = tid OR table.ctid IN (tid1, tid2, tid3, ...) |
| IndexScan | 索引扫描 | 选择条件涉及的属性上建立了索引，并且用索引进行查询的快速定位。 |
| IndexOnlyScan | 直接从索引返回元组 | 索引列完全覆盖查询的 Targetlist，在该场景下可以直接返回 Index 结果，而无需再次访问基表。 |
| CteScan | 扫描 Common Table Expression | 将 CTE 的输出看成是一个集合，进行后续的关系运算。 |
| FunctionScan | 函数扫描 | 将函数的输出看成是一个集合，进行后续的关系运算。 |
| ForgeinScan | 外部表扫描 | 查询基于外部数据源的外部表（FDW），外部数据源可以是 HDFS，GDS，OBS 等。 |
| WorkTableScan | 扫描中间结果集 | 扫描查询过程中 Splitout 的中间结果集，例如 RecursiveUnion 算子中每次迭代产生的中间结果集。 |
| ValueScan | 扫描 Value 列表 | 对 values() 子句中给出的元组集合进行扫描。 |
Seq Scan 算子
顺序扫描是最基本的扫描类型之一。在执行顺序扫描时，数据库引擎会逐行读取整个表，检查每一行以确定是否满足查询的条件。顺序扫描不依赖于索引，因此当表较小或查询需要大量表中数据时，顺序扫描可能是最有效的选择。
典型场景：
- 表无索引，需要对表进行扫描操作。
- 表有索引，但需要对表大部分数据进行扫描操作。
当表的数据量较大时，顺序扫描会从前往后（从后往前）进行扫描，如果缓存较小会造成大量的磁盘 IO 操作；同时即使缓存可以放下全部表的数据，但其时间复杂度为 O (n)，当 n 非常大的时候，效率依然较低。此时可以：
- 分配较大的
shared_buffers空间，提高缓存命中率。 - 当从表中获取的数据量较小的时候，建议使用时间复杂度 O(log(n)) 的 Index Scan，即为 WHERE 条件的字段添加索引。
- 当从表中获取的数据量较大的时候，建议使用时间复杂度为 O(n/dop) 的并行顺序扫描，dop 为并行度。
例子：创建表，插入数据并获取查询的执行计划
stone@postgres=> create table test(id int,name text,age int);
CREATE TABLE
stone@postgres=> insert into test select generate_series(1,1000),'test'::text,generate_series(1,1000);
INSERT 0 1000
stone@postgres=> select * from test limit 5;
id | name | age
----+------+-----
1 | test | 1
2 | test | 2
3 | test | 3
4 | test | 4
5 | test | 5
(5 rows)
stone@postgres=> explain select id from test where id > 50;
QUERY PLAN
-------------------------------------------------------
Seq Scan on test (cost=0.00..24.59 rows=389 width=4)
Filter: (id > 50)
(2 rows)
其中：
Seq Scan（顺序扫描）表示 openGauss 执行了一个全表扫描，test是被扫描的表的名称。cost是一个表示执行这个操作的预估代价的度量，通常包括两个数字：起始成本和总成本。0.00是开始返回数据之前的预计成本，这里是0，意味着从开始扫描就可能开始有数据输出。24.59是完成整个扫描的预计总成本。（需要注意的是，出现在explain中的部分数据为预估结果，其可能与实际结果不相同，结果也可能受多方面因素影响而与本手册给出的结果不同，为正常现象）
rows表示预估的满足条件的行数，这里是389行。这是优化器根据统计信息估算的，表示预计会有389行数据的id字段大于50。width表示每行数据的平均字节数，这里是4字节。这个数字有助于数据库估算查询的内存消耗。- 过滤条件
Filter: (id> 50)说明顺序扫描在检查每一行数据时应用了这个条件来决定是否包括该行在结果中。
Index Scan 算子
利用索引来快速找到满足特定条件的表行。在执行索引扫描时，数据库首先遍历索引以找到匹配查询条件的索引条目，然后根据索引条目中的指针访问表中相应的行。
- 在索引扫描中，数据库使用语句指定的索引列，通过遍历索引树来检索行。
- 数据库为一个值扫描索引时，发生 n 次 I/O 就能找到其要查找的值，其中 n 即 B - tree 索引的高度。
- 通常为检索表数据，数据库以轮流方式先读取索引块，找到对应的索引键值，然后通过索引键对应的 tid 去读取相应的表元组。
- 在数据量较大的场景下，Index Scan 的效率往往高于 Seq Scan。
使用场景：
| 场景 | 说明 |
|---|---|
| 查询特定行 | 当查询语句中包含 WHERE 子句时，如果 WHERE 子句中的条件可以通过索引进行匹配，那么 openGauss 就会使用 Index Scan 来查找符合条件的行。 |
| 排序 | 当查询语句中包含 ORDER BY 子句时，如果 ORDER BY 子句中的列可以通过索引进行排序，那么 openGauss 就会使用 Index Scan 来进行排序操作。 |
| 聚合 | 当查询语句中包含 GROUP BY 子句时，如果 GROUP BY 子句中的列可以通过索引进行分组，那么 openGauss 就会使用 Index Scan 来进行聚合操作。 |
| 连接 | 当查询语句中包含 JOIN 操作时，如果 JOIN 操作中的列可以通过索引进行匹配，那么 openGauss 就会使用 Index Scan 来进行连接操作。 |
性能分析：
| 出现场景 | 导致性能下降的原因 | 解决方案 |
|---|---|---|
| 数据分布不均匀 | 如果表中的数据分布不均匀，那么 Index Scan 可能会导致大量的随机 I/O 操作，从而降低查询效率。 | 暂未有较好的解决方案，建议规避该场景。 |
| 索引列数据类型不合适 | 如果索引列的数据类型不合适，比如使用了较长的字符串类型，那么 Index Scan 可能会导致大量的 I/O 操作。 | 使用合适的类型替换。 |
| 索引列数据重复度高 | 如果索引列的数据重复度很高，那么 Index Scan 可能会导致大量的 I/O 操作。 | 选择筛选度高的列作为索引。 |
| 查询条件不合适 | 如果查询条件不合适，比如使用了 LIKE 操作符或者使用了非前缀匹配的查询条件，那么 Index Scan 可能会导致大量的 I/O 操作。 | 修改查询条件。 |
| 索引列数据量过大 | 如果索引列的数据量过大，那么 Index Scan 可能会导致大量的 I/O 操作。 | 对于多列索引，建议删掉对索引无提升的列。 |
例子：创建索引并查看执行计划
stone@postgres=> create index test_id on test(id);
CREATE INDEX
stone@postgres=> explain select id from test where id > 50;
QUERY PLAN
-------------------------------------------------------
Seq Scan on test (cost=0.00..16.50 rows=950 width=4)
Filter: (id > 50)
(2 rows)
stone@postgres=> explain select id from test where id < 20;
QUERY PLAN
--------------------------------------------------------------------------
[Bypass]
Index Only Scan using test_id on test (cost=0.00..4.60 rows=20 width=4)
Index Cond: (id < 20)
(3 rows)
可以发现，我们在条件查询 id < 20 时，Seq Scan 变为了 Index Only Scan，using test_id on test 说明使用了我们刚刚创建的索引 test_id，相应的，Filter 也变为了 Index。
其中，[Bypass] 标记指的是，查询执行时绕过了一些常规的处理步骤，直接使用了更优的方法或技术，以提高查询效率。通常发生在：
- 索引完全覆盖查询需求：查询所需的所有数据都可以直接从索引中获取，无需访问表的数据页。
- 索引非常高效：对于非常小的结果集，访问索引比进行任何类型的表扫描都要高效。
对于第二点，我们可以看到，条件查询 id > 50 时，仍使用了顺序扫描，这是因为此查询涵盖表中 95% 的数据，在这种情况下，如果使用索引，优化器预计将需要检索大部分数据页，可能还要涉及大量的随机 I/O 操作。因此，全表扫描（尽管听起来效率低下）可能是更有效的方式。而 id < 20 只涵盖约 2% 的数据。在这种情况下，使用索引可以快速定位到这些较少的行，大幅减少必须读取的数据量。
添加 and 查询条件：
stone@postgres=> explain select id from test where id < 20 and age > 0;
QUERY PLAN
---------------------------------------------------------------------
Index Scan using test_id on test (cost=0.00..8.65 rows=20 width=4)
Index Cond: (id < 20)
Filter: (age > 0)
(3 rows)
添加了 and 查询条件后，where id < 20 and age > 0，与单独查询 where id < 20 相比，其没有[Bypass] 标记，且 Index Only Scan 变为 Index Scan，这正是上面提到的 “索引完全覆盖查询需求” 导致的。我们只创建了 id 上的索引 test_id，而条件用到了 id 列与 age 列，索引未覆盖这两列。同时，存在索引的列使用的是 Index Cond: (id < 20)，而没有索引的列为 Filter: (age > 0)。
删除单列索引，创建复合索引：
stone@postgres=> drop index test_id;
DROP INDEX
stone@postgres=> create index test_id_age on test(id, age);
CREATE INDEX
stone@postgres=> explain select id from test where id < 20 and age > 0;
QUERY PLAN
------------------------------------------------------------------------------
[Bypass]
Index Only Scan using test_id_age on test (cost=0.00..4.65 rows=20 width=4)
Index Cond: ((id < 20) AND (age > 0))
(3 rows)
数据库现在只需要使用索引 test_id_age 来查找满足条件 id < 20 和 age > 0 的行。成本从 0.00 开始，结束于 4.65，预计找到 20 行，小于上方第一次执行 where id < 20 and age > 0 查询时所估算的 8.65。
Subquery Scan 算子
Subquery Scan 通常出现在涉及子查询的查询中。子查询可以是 SELECT 语句中的一部分，通常被用作过滤条件、数据源或在 SELECT 子句中计算字段值。
例子：创建表，插入数据并获取查询的执行计划
stone@postgres=> CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INTEGER,
salary DECIMAL(10, 2),
department VARCHAR(50));
NOTICE: CREATE TABLE will create implicit sequence "employees_id_seq" for serial column "employees.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "employees_pkey" for table "employees"
CREATE TABLE
stone@postgres=> INSERT INTO employees (name, age, salary, department) VALUES
('Alice', 30, 50000.00, 'HR'),
('Bob', 22, 40000.00, 'Marketing'),
('Charlie', 28, 45000.00, 'IT'),
('David', 35, 55000.00, 'Finance'),
('Eve', 45, 62000.00, 'IT'),
('Frank', 32, 48000.00, 'Marketing');
INSERT 0 6
stone@postgres=> EXPLAIN SELECT *
FROM (SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC LIMIT 10) AS top_earners
WHERE salary > (SELECT AVG(salary) FROM employees);
QUERY PLAN
---------------------------------------------------------------------------------
Subquery Scan on top_earners (cost=52.15..52.30 rows=3 width=84)
Filter: (top_earners.salary > $0)
InitPlan 1 (returns $0)
-> Aggregate (cost=16.19..16.20 rows=1 width=48)
-> Seq Scan on employees (cost=0.00..14.95 rows=495 width=16)
-> Limit (cost=35.95..35.98 rows=10 width=84)
InitPlan 2 (returns $1)
-> Aggregate (cost=16.19..16.20 rows=1 width=48)
-> Seq Scan on employees (cost=0.00..14.95 rows=495 width=16)
-> Sort (cost=19.75..20.17 rows=165 width=84)
Sort Key: stone.employees.salary DESC
-> Seq Scan on employees (cost=0.00..16.19 rows=165 width=84)
Filter: (salary > $1)
(13 rows)
以上执行了一个包含子查询的查询语句，即在 select * from 中嵌套了 select。这个查询相对复杂，explain 打印内容也较复杂，出现了较多还没有介绍到的算子，这是因为子查询通常被优化，尤其是在可以用更简单的执行计划来解决查询的场合。要确保 Subquery Scan 出现在查询计划中，需要创建一个场景，其中子查询的结果被直接用作另一查询的数据源。因此构造了这样一个复杂 SQL，才使子查询未被优化，这其实也体现了查询优化的强大。在这里只关心第一行出现的 Subquery Scan，其他算子将在以后介绍。
Subquery Scan on top_earners 表示 openGauss 对内部查询 top_earners 的结果进行了一次扫描。这个内部查询通过一个更复杂的操作生成了一个临时结果集，该结果集包含了薪资高于公司平均薪资的前 10 名员工。
成本从 52.15 到 52.30，预计找到 3 行。这个成本估计包括了内部查询的成本以及将这些结果过滤到只包括薪资高于公司平均水平的那些记录的成本。
Filter： 这一层的过滤条件是 top_earners.salary > $0，这里的 $0 代表一个初始化计划 （InitPlan 1 (returns $0)） 生成的值，即公司的平均薪资。
Tid Scan 算子
Tid Scan（Tuple Identifier Scan）是一种特殊的扫描方式，它直接使用元组标识符（tuple identifiers，或称为 TID）来访问表中的行。TID 是行在其物理页中的位置的内部标识符，通常用于低层次的操作，如行级锁定或特定的系统管理功能。
例子：使用 ctid 查询的执行计划
stone@postgres=> EXPLAIN SELECT * FROM employees WHERE ctid = '(0,1)';
QUERY PLAN
-----------------------------------------------------------
Tid Scan on employees (cost=0.00..4.01 rows=1 width=135)
TID Cond: (ctid = '(0,1)'::tid)
(2 rows)
要使查询计划中出现 TID Scan，通常需要手动指定 TID：在查询中直接使用 TID 来选择行。这可以通过使用 ctid 字段来实现，ctid 是系统列，存储了行的物理位置。
在实际应用中，使用 TID 来直接访问行非常少见，因为它依赖于行的物理位置，这在数据库中通常是不透明且易变的。
Tid Scan 表示查询计划直接通过行的物理位置（即 Tuple Identifier 或 TID）来访问表中的数据。这是一种效率很高的数据访问方式，因为它避免了常规的索引扫描或顺序扫描，直接跳转到数据行所在的物理位置。
rows=1指出预计找到 1 行，这是因为查询通过ctid指定了一个精确的行位置。- TID Cond 部分详细说明了 TID 条件，即查询使用的
ctid值。这里，(ctid = '(0,1)'::tid)表明查询正在寻找ctid值恰好为(0,1)的行。::tid是 openGauss 的类型转换，表示将字符串转换为 TID 类型。
Function Scan 算子
Function Scan 出现在执行计划中，表示数据库正在执行一个或多个函数调用，并且这些函数的返回值被当做表中的行来处理。要构造一个 EXPLAIN 语句，其中会显示 Function Scan，可以使用一个返回表集（set-returning function）的内置函数。
例子：使用 generate_series() 函数的执行计划
stone@postgres=> EXPLAIN SELECT * FROM generate_series(1, 10);
QUERY PLAN
------------------------------------------------------------------------
Function Scan on generate_series (cost=0.00..10.00 rows=1000 width=4)
(1 row)
generate_series() 函数就是一个返回表集的内置函数，返回了 1 - 10 的序列表集作为结果。执行这个 EXPLAIN 命令会显示一个 Function Scan，因为查询计划需要扫描由 generate_series() 函数生成的序列行。
rows 值表示查询优化器预计这个函数调用将生成大约 1000 行数据。这个数字是一个估计值，通常基于函数的历史执行统计信息或者函数的定义特性。实际上，generate_series(1, 10) 明显只会生成 10 行数据，因此这里的 1000 行是默认的估计值或者优化器对该函数的一种保守预设。
连接类算子
连接类算子是为了应对数据库中最常见的关联操作。
按实现方式主要有三种：
| 算子类型 | 算子说明 | 出现场景 |
|---|---|---|
| Nested Loop Join | 对下层两股数据流实现循环嵌套连接操作。 | Inner, Left-Outer, Semi Join, Anti Join |
| Merge Join | 对下层两股排序数据流实现归并连接操作 | Inner, Left-Outer-Join, Right-Outer-Join, Full-Outer-Join, Semi Join, Anti Join |
| Hash Join | 对下层两股数据流实现哈希连接操作 | Inner, Left-Outer-Join, Right-Outer-Join, Full-Outer-Join, Semi Join, Anti Join |
Hash Join 算子
算法说明：
- 哈希连接（Hash Join）是一种高效的连接方法，依赖于哈希技术。在进行哈希连接时，openGauss 会先选取两个表中的一个（通常是小表），然后根据连接条件，建立一个哈希表。
- 哈希表的键是小表的连接字段，值是小表的其他字段。然后，对于大表中的每一行，计算连接字段的哈希值，然后在哈希表中查找是否有匹配的行。
- 这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。
- Hash Join 的时间复杂度为 O(n+m)，其中 n 和 m 分别代表两个表的行数。然而如果内部表过大，以至于哈希表无法完全放入内存，那么这可能需要额外的磁盘 I/O 操作，导致性能降低。
典型场景：
- 连接条件基于等值：Hash Join 需要连接条件是基于等值的（例如 A = B），这允许数据库为其中一个表（或两者）构建哈希表。
- 较大的数据集：适用于处理大批量数据，尤其是当两个表的大小差异较大时。
- 无有效索引：当参与连接的列上没有有效的索引，或索引的使用不会带来性能提升时，Hash Join 往往更有效。
- 成本效益：相比其他连接方法（如 Nested Loop 或 Merge Join），Hash Join 在处理大量数据时通常更加高效，因为它通过创建哈希表减少了数据查找的时间复杂度。
性能分析：
- 哈希连接的一个主要性能问题是，如果哈希表太大以至于无法完全放入内存，那么 openGauss 可能需要将部分哈希表写入磁盘，这会大大降低查询性能。
- 哈希连接需要在构建完哈希表之后才能返回结果行，不像 Nested Loop Join，执行后可以立即返回结果行。
例子：查看 Hash Join 的执行计划
stone@postgres=> CREATE TABLE departments (
department_id SERIAL PRIMARY KEY,
department_name VARCHAR(50)
);
NOTICE: CREATE TABLE will create implicit sequence "departments_department_id_seq" for serial column "departments.department_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "departments_pkey" for table "departments"
CREATE TABLE
stone@postgres=> EXPLAIN SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department = d.department_name;
QUERY PLAN
----------------------------------------------------------------------------
Hash Join (cost=21.14..73.31 rows=2626 width=111)
Hash Cond: ((d.department_name)::text = (e.department)::text)
-> Seq Scan on departments d (cost=0.00..20.61 rows=1061 width=43)
-> Hash (cost=14.95..14.95 rows=495 width=111)
-> Seq Scan on employees e (cost=0.00..14.95 rows=495 width=111)
(5 rows)
Hash Join 预估结果集有 2626 行，结果集中每行数据的平均字节宽度为 111。
Hash Cond 指出 Hash Join 使用的连接条件，这里是部门名称，部门名称通过文本形式进行比较。
Seq Scan on departments d 对 departments 表进行顺序扫描。
Hash (cost=14.95..14.95 rows=495 width=111)，用于构建哈希表的数据，来自 employees 表。
Seq Scan on employees e 生成哈希表之前，对 employees 表的顺序扫描，反映了从这个表中读取数据的成本和行数。
这里预估的行数有较大差距，实际两张表中目前都只有个位行数的数据，真实查询结果也只有 6 行。可以看出这确实只是一个预估结果，特别是在实际数据很少时，可能会产生较大偏差。
在实际生产环境中，有较多数据时，该估计值的误差一般在可接受范围内，作为执行计划选择的参考仍是有意义的。我们可以通过添加 ANALYZE 关键字，获取查询语句执行的实际代价、行数等信息。
stone@postgres=> EXPLAIN ANALYZE SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department = d.department_name;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Hash Join (cost=21.14..73.31 rows=2626 width=111) (actual time=0.005..0.005 rows=0 loops=1)
Hash Cond: ((d.department_name)::text = (e.department)::text)
-> Seq Scan on departments d (cost=0.00..20.61 rows=1061 width=43) (actual time=0.001..0.001 rows=0 loops=1)
-> Hash (cost=14.95..14.95 rows=495 width=111) (Actual time: never executed)
Buckets: 0 Batches: 0 Memory Usage: 0kB
-> Seq Scan on employees e (cost=0.00..14.95 rows=495 width=111) (Actual time: never executed)
Total runtime: 0.069 ms
(7 rows)
使用 EXPLAIN ANALYZE 后，显示了查询语句执行的实际代价、行数等信息。
Merge Join 算子
算法说明:
- 合并连接（Merge Join）是一种高效的连接方法，依赖于排序操作。在进行合并连接时，openGaus 会对两个表的连接字段进行排序，然后同步扫描两个表，寻找匹配的行。
- 通常用于处理已排序的数据集或可以通过排序来优化连接的场合。如果这些列没有预先排序，数据库会自动对它们进行排序，然后再执行连接。
- Merge join 的时间复杂度为 O(n+m)，其中 n 和 m 分别代表两个表的行数。然而，如果需要排序操作，这个排序操作的时间复杂度可能会达到 max(O(logn), O(logm))，这通常比直接的 Merge join 操作更加耗时。
- 在 openGauss 中，Merge join 比较少见，即使需要连接的两张表已经经过排序，优化器在大部分情况下依旧会更倾向去选择 Hash Join。
- 通常情况下 Hash 连接的效果都比排序合并连接要好，但如果元组已经被排序，在执行排序合并连接时不需要再排序，这时排序合并连接的性能会优于 Hash 连接。
典型场景：
- 当两个表的大小接近时。
- 当两个表的连接字段已经被排序或者已经有序时。
- 如果连接的列上有有效的索引，尤其是在索引已经按连接键排序的情况下，优化器可能倾向于使用 Merge Join。
- 当连接条件除了等值连接（比如使用
=运算符）之外，还包括范围查询（比如使用<、<=、>、>=运算符）。
性能分析：
- 合并连接的主要性能问题是：如果两个表的连接字段没有排序，openGauss 就需要先对这两个表的连接字段进行排序，这可能需要大量的 CPU 时间和磁盘 I/O。
例子：启用 Merge Join 并查看执行计划
stone@postgres=> SET enable_hashjoin = off;
SET
stone@postgres=> set enable_mergejoin = on;
SET
stone@postgres=> EXPLAIN SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department = d.department_name;
QUERY PLAN
------------------------------------------------------------------------------
Merge Join (cost=111.04..152.90 rows=2626 width=111)
Merge Cond: ((e.department)::text = (d.department_name)::text)
-> Sort (cost=37.10..38.34 rows=495 width=111)
Sort Key: e.department
-> Seq Scan on employees e (cost=0.00..14.95 rows=495 width=111)
-> Sort (cost=73.93..76.58 rows=1061 width=43)
Sort Key: d.department_name
-> Seq Scan on departments d (cost=0.00..20.61 rows=1061 width=43)
(8 rows)
禁用 Hash Join，启动 Merge Join 后，执行计划选择了 Merge Join，可以看到预估同样不太准确，原因与 Hash Join 相同。Merge Join 的预估代价是 152.90，高于 Hash Join 的 73.31，因此，在未禁用 Hash Join 时，数据库选择了 Hash Join。
同时可以看到与上述 Merge Join 的介绍相对应，Merge Join 需要两个表中参与连接的列事先处于排序状态，执行计划中确实对两个表都进行了排序（Sort）操作。
使用 EXPLAIN ANALYZE 获取查询语句执行的实际代价、行数等信息：
stone@postgres=> EXPLAIN ANALYZE SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department = d.department_name;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=111.04..152.90 rows=2626 width=111) (actual time=0.265..0.265 rows=0 loops=1)
Merge Cond: ((e.department)::text = (d.department_name)::text)
-> Sort (cost=37.10..38.34 rows=495 width=111) (actual time=0.164..0.164 rows=1 loops=1)
Sort Key: e.department
Sort Method: quicksort Memory: 25kB
-> Seq Scan on employees e (cost=0.00..14.95 rows=495 width=111) (actual time=0.016..0.018 rows=6 loops=1)
-> Sort (cost=73.93..76.58 rows=1061 width=43) (actual time=0.027..0.027 rows=0 loops=1)
Sort Key: d.department_name
Sort Method: quicksort Memory: 25kB
-> Seq Scan on departments d (cost=0.00..20.61 rows=1061 width=43) (actual time=0.001..0.001 rows=0 loops=1)
Total runtime: 0.364 ms
(11 rows)
可以看到实际的执行代价和实际执行时间，Merge Join 是快于 Hash Join 的，因为前面介绍到，Hash Join 其实在大量数据时效果更好，这里的数据量很少，而预估行数误差较大，导致优化器选择了 Hash Join。也可以看出，优化器并不总能选择最优执行计划，会受到数据特征的影响。
Nested Loop Join 算子
算法说明：
- 嵌套循环连接（Nested Loop Join）是最简单的连接方法，也是所有关系数据库系统中都会实现的连接操作。这种方法的基本思想是 “把两个表中的数据两两比较，看是否满足连接条件”。
- 在 openGauss 中，Nested Loop Join 的工作原理是，对于外部表（Outer Table）中的每一行，扫描内部表（Inner Table），查找符合连接条件的行。这就像两个嵌套的循环，外部循环遍历外部表，内部循环遍历内部表，这就是其名字的由来。
- Nested loop join 的时间复杂度是 O(n*m)，其中 n 和 m 分别代表两个表的行数，如果内部表可以用索引来扫描，那么时间复杂度可以降低到 O(nlogm)。
典型场景：
- 当至少一个参与连接的表相对较小时，Nested Loop Join 可以非常高效，尤其是当小表可以完全放入内存时。
- 当内部表能够根据连接条件快速定位到满足条件的行时，例如内部表的连接字段已经建了索引。
- Nested Loop join 对连接的条件没有限制，任何连接条件都可以执行。
性能分析：
- Nested Loop Join 在处理大表时效率较低，特别是在两个大表之间没有索引的情况下，可能需要大量的磁盘 I/O 和 CPU 时间。
例子：查看 Nested Loop Join 的执行计划
stone@postgres=> SET enable_hashjoin = off;
SET
stone@postgres=> SET enable_mergejoin = off;
SET
stone@postgres=> EXPLAIN SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department = d.department_name;
QUERY PLAN
----------------------------------------------------------------------------
Nested Loop (cost=0.00..7914.72 rows=2626 width=111)
Join Filter: ((e.department)::text = (d.department_name)::text)
-> Seq Scan on departments d (cost=0.00..20.61 rows=1061 width=43)
-> Materialize (cost=0.00..17.43 rows=495 width=111)
-> Seq Scan on employees e (cost=0.00..14.95 rows=495 width=111)
(5 rows)
禁用 Hash Join、Merge Join 后，数据库选择 Nested Loop Join。可以看到预估同样不太准确，原因同上。Nested Loop 的预估代价是 7914.72，远高于 Hash Join 和 Merge Join，因此，在未禁用时，数据库的选择顺序是 Hash Join、Merge Join、Nested Loop Join。
其他内容基本相同，这里介绍一下 explain 中出现的 Materialize，它在内存中缓存 employees 表的数据，这有助于减少多次访问同一数据的成本。
使用 EXPLAIN ANALYZE 获取查询语句执行的实际代价、行数等信息：
stone@postgres=> EXPLAIN ANALYZE SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department = d.department_name;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..7914.72 rows=2626 width=111) (actual time=0.002..0.002 rows=0 loops=1)
Join Filter: ((e.department)::text = (d.department_name)::text)
-> Seq Scan on departments d (cost=0.00..20.61 rows=1061 width=43) (actual time=0.001..0.001 rows=0 loops=1)
-> Materialize (cost=0.00..17.43 rows=495 width=111) (Actual time: never executed)
-> Seq Scan on employees e (cost=0.00..14.95 rows=495 width=111) (Actual time: never executed)
Total runtime: 0.103 ms
(6 rows)
可以看到，实际的执行代价和实际执行时间，Nested Loop Join 其实是最快的，前面介绍到，Nested Loop Join 适合小数据量，与实际场景相符，而预估行数误差较大，导致优化器选择了 Hash Join、次选 Merge Join。也可以看出，优化器并不总能选择最优执行计划，会受到数据特征的影响。在本用例下，甚至实际优先级结果刚好相反。体现了预估算法的局限性，然而，在未实际执行前，要求预估算法与实际结果完全相符，也是不实际的。
本用例仅是一种极端情况，在用例数据量很少的情况下，三种算子的绝对用时差距也没有太大。当然，优化预估算法，在更多场景中贴合实际情况，也是 openGauss 努力的方向。我们插入大量数据后再次尝试。
插入 1000000 条数据：
stone@postgres=> DO $$
DECLARE
iter INTEGER;
BEGIN
FOR iter IN 1..1000000 LOOP
INSERT INTO employees (name, age, salary, department)
VALUES ('Alice', 30, (RANDOM() * (100000 - 10000) + 10000)::INT, 'HR');
END LOOP;
END $$;
ANONYMOUS BLOCK EXECUTE
Hash Join 连接测试：
stone@postgres=> SET enable_hashjoin = on;
SET
stone@postgres=> SET enable_mergejoin = on;
SET
stone@postgres=> EXPLAIN ANALYZE SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department = d.department_name;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=33.87..84419.52 rows=5830524 width=49) (actual time=0.187..0.187 rows=0 loops=1)
Hash Cond: ((e.department)::text = (d.department_name)::text)
-> Seq Scan on employees e (cost=0.00..16463.62 rows=1099062 width=9) (actual time=0.047..0.047 rows=1 loops=1)
-> Hash (cost=20.61..20.61 rows=1061 width=43) (actual time=0.002..0.002 rows=0 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 256kB
-> Seq Scan on departments d (cost=0.00..20.61 rows=1061 width=43) (actual time=0.001..0.001 rows=0 loops=1)
Total runtime: 0.443 ms
(7 rows)
Merge Join 连接测试：
stone@postgres=> SET enable_hashjoin = off;
SET
stone@postgres=> EXPLAIN ANALYZE SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department = d.department_name;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=183166.56..273377.38 rows=5830524 width=49) (actual time=0.020..0.020 rows=0 loops=1)
Merge Cond: ((d.department_name)::text = (e.department)::text)
-> Sort (cost=73.93..76.58 rows=1061 width=43) (actual time=0.011..0.011 rows=0 loops=1)
Sort Key: d.department_name
Sort Method: quicksort Memory: 25kB
-> Seq Scan on departments d (cost=0.00..20.61 rows=1061 width=43) (actual time=0.001..0.001 rows=0 loops=1)
-> Materialize (cost=183092.63..188587.94 rows=1099062 width=9) (Actual time: never executed)
-> Sort (cost=183092.63..185840.28 rows=1099062 width=9) (Actual time: never executed)
Sort Key: e.department
-> Seq Scan on employees e (cost=0.00..16463.62 rows=1099062 width=9) (Actual time: never executed)
Total runtime: 0.199 ms
(11 rows)
Nested Loop Join 连接测试：
stone@postgres=> SET enable_hashjoin = off;
SET
stone@postgres=> SET enable_mergejoin = off;
SET
stone@postgres=> EXPLAIN ANALYZE SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department = d.department_name;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..17508058.61 rows=5830524 width=49) (actual time=418.597..418.597 rows=0 loops=1)
Join Filter: ((e.department)::text = (d.department_name)::text)
-> Seq Scan on employees e (cost=0.00..16463.62 rows=1099062 width=9) (actual time=0.043..190.554 rows=1100006 loops=1)
-> Materialize (cost=0.00..25.91 rows=1061 width=43) (actual time=56.985..56.985 rows=0 loops=1100006)
-> Seq Scan on departments d (cost=0.00..20.61 rows=1061 width=43) (actual time=0.001..0.001 rows=0 loops=1)
Total runtime: 418.662 ms
(6 rows)
可以看出，增加数据量后，实际执行时间排序与预估排序、选择的优先级是相同的，Hash Join 快于 Merge Join 快于 Nested Loop Join。
重建数据并恢复 Hash Join：
stone@postgres=> SET enable_hashjoin = on;
SET
stone@postgres=> drop table employees;
DROP TABLE
stone@postgres=> CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INTEGER,
salary DECIMAL(10, 2),
department VARCHAR(50));
NOTICE: CREATE TABLE will create implicit sequence "employees_id_seq" for serial column "employees.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "employees_pkey" for table "employees"
CREATE TABLE
stone@postgres=> INSERT INTO employees (name, age, salary, department) VALUES
('Alice', 30, 50000.00, 'HR'),
('Bob', 22, 40000.00, 'Marketing'),
('Charlie', 28, 45000.00, 'IT'),
('David', 35, 55000.00, 'Finance'),
('Eve', 45, 62000.00, 'IT'),
('Frank', 32, 48000.00, 'Marketing');
INSERT 0 6
控制类算子
控制算子一般不映射代数运算符，通常是为了执行器完成一些特殊的流程引入的算子。
| 算子类型 | 算子说明 | 出现场景 |
|---|---|---|
| Result | 处理只有一个结果或过滤条件是常量的流程 | 处理仅需要一次计算的条件表达式或 Insert 中仅有一个 Values 子句的场景。 |
| ModifyTable | INSERT/UPDATE/DELETE 操作的算子 | 查询进行插入、更新、删除。 |
| Append | 多个关系集合的追加操作 | UNION、UNION-ALL。 |
| MergeAppend | 多个有序关系集合的追加操作 | UNION、继承表。 |
| RecursiveUnion | 执行 Recursive Subquery | 用于处理 With Recursive 递归查询。 |
| Limit | 对输出的结果集进行限制 | 带 Limit 的 SQL 语句。 |
| BitmapAnd | 对位图做与运算 | 用于对多个属性约束都有索引，且属性约束是 And 运算。 |
| BitmapOr | 对位图做或运算 | 用于对多个属性约束都有索引，且属性约束是 Or 运算。 |
| Rownum | 行号算子 | 带 Rownum 的 SQL 语句。 |
Result 算子
在 SQL 查询的 EXPLAIN 输出中，Result 操作通常表示一个计算或条件过滤发生在没有实际从物理表读取数据的情况下。这通常出现在以下几种情况：
- 查询涉及常量或表达式计算：当查询只涉及返回一组计算值或常量，而不是从表中检索数据时，可能会出现 Result 节点。
- 查询使用函数返回结果：当查询依赖于返回动态计算结果的函数时（例如，生成数据的函数或条件选择函数）。
- WHERE 条件过滤所有行：当 WHERE 子句中的条件导致没有任何行满足条件，而查询需要返回一个空的结果集或一个计算值时。
例子：查询不涉及任何表，只是返回一个常量字符串 'Hello World'
stone@postgres=> EXPLAIN SELECT 'Hello World' AS greeting;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
(1 row)
例子：使用 random() 函数生成一个 0 到 1 之间的随机浮点数，然后使用 CASE 语句根据这个随机数的值来返回不同的字符串。这种类型的查询完全基于函数的返回结果，不依赖任何外部表数据。
stone@postgres=> EXPLAIN SELECT CASE WHEN random() > 0.5 THEN 'Greater than 0.5' ELSE 'Less than or equal to 0.5' END AS random_result;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
(1 row)
Limit 算子
在 SQL 查询的 EXPLAIN 输出中，Limit 操作通常表示查询计划包含了限制返回行数的操作。这通常出现在以下情况：
- LIMIT 子句使用：当查询包含 LIMIT 子句时，用于限定查询返回的最大行数。
- OFFSET 子句使用：经常与 LIMIT 一起使用，OFFSET 指定了在开始返回行之前要跳过的行数。
- 查询优化：某些情况下，即使在查询中没有显式使用 LIMIT，优化器也可能因为某些内部优化策略（如在估计不需要更多行来满足查询要求时）而隐式使用 LIMIT。
例子：使用 Limit 的执行计划
stone@postgres=> EXPLAIN SELECT * FROM employees LIMIT 10;
QUERY PLAN
--------------------------------------------------------------------
Limit (cost=0.00..0.30 rows=10 width=135)
-> Seq Scan on employees (cost=0.00..14.95 rows=495 width=135)
(2 rows)
执行计划中包括一个 Limit 步骤，表明数据库执行了限制操作以确保结果中只包含指定数量的行。这个操作对于控制大数据集查询的结果非常有用，可以显著提高性能，尤其是当只需要部分数据时。
Append 算子
在 SQL 查询的 EXPLAIN 输出中，Append 操作通常出现在以下几种情况：
- 查询涉及并集操作：当查询需要合并来自多个表或子查询的结果集时，如使用
UNION ALL操作。 - 查询分区表：当查询涉及分区表，且需要从多个分区中检索数据时，Append 操作用于合并来自各个分区的结果。
- 使用继承表结构：当表使用了表继承特性，查询父表时，Append 用于合并来自所有子表的结果。
例子：使用 Append 的执行计划
stone@postgres=> EXPLAIN SELECT name FROM employees
UNION ALL
SELECT name FROM employees WHERE department = 'HR';
QUERY PLAN
-------------------------------------------------------------------------
Result (cost=0.00..31.16 rows=497 width=68)
-> Append (cost=0.00..31.16 rows=497 width=68)
-> Seq Scan on employees (cost=0.00..14.95 rows=495 width=68)
-> Seq Scan on employees (cost=0.00..16.19 rows=2 width=68)
Filter: ((department)::text = 'HR'::text)
(5 rows)
这个查询从 employees 表中选择所有员工的名字，然后通过 UNION ALL 合并了同一表中部门为 'HR' 的员工名字的结果。查询计划中包括一个 Append 步骤，表明数据库执行了合并操作，将两个独立的查询结果集直接拼接起来。
物化类算子
物化算子一般指算法要求，在做算子逻辑处理的时候，要求把下层的数据进行缓存处理，因为对于下层算子返回的数据量不可提前预知，因此需要在算法上考虑数据无法全部放置到内存的情况。
| 算子类型 | 算子说明 | 出现场景 |
|---|---|---|
| Materialize | 物化算子 | 缓存节点结果集以方便后续能够重新扫描。 |
| Sort | 对下层数据进行排序 | ORDER BY 子句，Merge Join 连接操作，SortAgg 分组操作，MergeAppend 集合操作，配合 Unique 去重等。 |
| Group | 对下层已经排序的数据进行分组 | 处理 GROUP BY 分组操作。 |
| Aggregate | 对下层数据进行分组（无序） | COUNT/SUM/AVG/MAX/MIN 等聚合函数，DISTINCT 子句，UNION 去重，GROUP BY 子句。 |
| Unique | 对下层数据进行去重操作 | DISTINCT 子句，UNION 去重。 |
| Hash | 对下层数据进行缓存，存储到一个 Hash 表中 | 构造 HashTable，配合 Hash Join 算子。 |
| SetOp | 对下层数据进行缓存，用于处理 Intersect 等集合操作 | INTERSECT/INTERSECT ALL，EXCEPT/EXCEPT ALL 操作。 |
| WindowAgg | 窗口函数 | 包含窗口函数的语句，如：row_number () OVER (PARTITION BY xxx)、avg (xxx) OVER (PARTITION BY xxx) 等。 |
| LockRows | 处理行级锁 | SELECT ... FOR SHARE/UPDATE。 |
Sort 算子
在 SQL 查询的 EXPLAIN 输出中，Sort 操作出现通常是因为以下原因：
- 排序操作：当查询包含
ORDER BY子句时，需要对结果集进行排序，以满足指定的排序顺序。 - 聚合操作：一些聚合函数（如
GROUP BY）可能需要先对数据进行排序，以便更高效地计算聚合。 - 窗口函数：使用窗口函数时，可能需要对参与窗口计算的数据进行排序。
- 优化连接：在执行某些类型的连接（如 Merge Join）时，如果参与连接的表的相关列没有预先排序，那么在连接之前需要进行排序。这点在前面 Merge Join 一节中有体现。
例子：使用 Sort 的执行计划
stone@postgres=> EXPLAIN SELECT name, department, salary FROM employees ORDER BY salary DESC;
QUERY PLAN
--------------------------------------------------------------------
Sort (cost=37.10..38.34 rows=495 width=127)
Sort Key: salary DESC
-> Seq Scan on employees (cost=0.00..14.95 rows=495 width=127)
(3 rows)
查询计划中包含一个 Sort 步骤，反映了必须对数据进行排序以满足查询的 ORDER BY 子句。这种排序通常涉及将数据加载到内存中，并使用适当的排序算法（如快速排序或归并排序）来调整行的顺序。如果数据量较大，排序可能涉及使用磁盘空间来存储临时数据。
这里的预估也有较大偏差，可以使用 ANALYZE 查看实际情况。
stone@postgres=> EXPLAIN ANALYZE SELECT name, department, salary FROM employees ORDER BY salary DESC;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Sort (cost=37.10..38.34 rows=495 width=127) (actual time=0.039..0.040 rows=6 loops=1)
Sort Key: salary DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on employees (cost=0.00..14.95 rows=495 width=127) (actual time=0.012..0.015 rows=6 loops=1)
Total runtime: 0.098 ms
(5 rows)
Hash 算子
在 SQL 查询的 EXPLAIN 输出中，Hash 操作通常出现在涉及 Hash Join 或用于快速查找和比较的哈希表构建过程中。具体情况包括：
- Hash Join：当执行 Hash Join 操作时，系统会为一个或两个参与连接的表构建哈希表。这种类型的连接适用于等值连接，尤其是在没有有效索引可用或表的大小不均时。
- 聚合操作：执行分组聚合（如使用
GROUP BY）时，如果使用哈希方法进行分组，也会出现 Hash 操作。这种情况下，哈希表用于快速分组键值的聚合计算。
例子：使用 Hash 的执行计划
stone@postgres=> EXPLAIN SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department = d.department_name;
QUERY PLAN
----------------------------------------------------------------------------
Hash Join (cost=21.14..73.31 rows=2626 width=111)
Hash Cond: ((d.department_name)::text = (e.department)::text)
-> Seq Scan on departments d (cost=0.00..20.61 rows=1061 width=43)
-> Hash (cost=14.95..14.95 rows=495 width=111)
-> Seq Scan on employees e (cost=0.00..14.95 rows=495 width=111)
(5 rows)
Hash (cost=14.95..14.95 rows=495 width=111)，用于构建哈希表的数据，来自 employees 表。
哈希表的构建是为了支持哈希连接。在这个过程中，数据库扫描 employees 表，对每一行中的 department 字段应用哈希函数，并将结果存储在内存中的哈希表中。这样，当 departments 表被扫描时，每个部门名称可以快速地与哈希表中的条目进行匹配，从而确定哪些 employees 行与 departments 行相连接。
Unique 算子
在 SQL 查询的 EXPLAIN 输出中，Unique 操作通常表示数据库系统正在执行去重操作。这通常出现在以下情况：
- SELECT DISTINCT：当查询中包含
DISTINCT关键字时，用于确保返回结果中不包含重复的行。 - 聚合查询中：某些情况下，即使没有显式使用
DISTINCT，为了保证聚合函数（如COUNT(DISTINCT column)）的正确执行，也可能隐式进行去重操作。 - 窗口函数：在计算某些窗口函数时，去重可能是必要步骤，尤其是当窗口函数依赖于唯一值时。
例子：使用 Unique 的执行计划
stone@postgres=> EXPLAIN SELECT DISTINCT department FROM employees;
QUERY PLAN
-------------------------------------------------------------------
HashAggregate (cost=16.19..18.19 rows=200 width=43)
Group By Key: department
-> Seq Scan on employees (cost=0.00..14.95 rows=495 width=43)
(3 rows)
在现代数据库系统中，尤其是在处理大数据集时，HashAggregate 被认为是去重的更有效方式。相比传统的 Unique 过滤，HashAggregate 通过构建哈希表来分组数据，这通常比先排序再去重更快。
stone@postgres=> EXPLAIN SELECT DISTINCT department FROM (SELECT department FROM employees ORDER BY department) sub;
QUERY PLAN
-------------------------------------------------------------------------
Unique (cost=37.10..44.53 rows=200 width=43)
-> Sort (cost=37.10..38.34 rows=495 width=43)
Sort Key: employees.department
-> Seq Scan on employees (cost=0.00..14.95 rows=495 width=43)
(4 rows)
在这个查询中，子查询首先对 department 进行排序，外部查询再对排序后的结果应用 DISTINCT。这种结构有时可以促使优化器采用 Unique 步骤，特别是在子查询的结果被外部查询明确要求去重时。
Aggregate 算子
在 SQL 查询的 EXPLAIN 输出中，Aggregate 操作通常表示查询中包含了聚合函数的使用。聚合函数包括但不限于 SUM()、AVG()、COUNT()、MAX()、MIN() 等，用于执行统计计算。这些操作通常出现在以下情况：
- 聚合查询：包含聚合函数，需要对数据进行统计计算。
- GROUP BY 子句：与
GROUP BY子句结合使用时，用于对指定的列或表达式进行分组并计算每组的聚合值。
例子：使用 Aggregate 的执行计划
stone@postgres=> EXPLAIN SELECT AVG(salary) AS average_salary FROM employees;
QUERY PLAN
-------------------------------------------------------------------
Aggregate (cost=16.19..16.20 rows=1 width=48)
-> Seq Scan on employees (cost=0.00..14.95 rows=495 width=16)
(2 rows)
查询计划中包括一个 Aggregate 步骤，表明数据库执行了聚合操作以计算平均薪资。这种操作是在数据处理的最后阶段完成的，通常涉及扫描整个表或多行的数据集合来计算结果。
Group 算子
在 SQL 查询的 EXPLAIN 输出中，Group 或 GroupAggregate 操作通常表示查询计划涉及了分组操作，这是为了处理 GROUP BY 子句中指定的列或表达式。
例子：使用 Group 的执行计划
stone@postgres=> EXPLAIN SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department;
QUERY PLAN
-------------------------------------------------------------------
HashAggregate (cost=17.43..19.93 rows=200 width=91)
Group By Key: department
-> Seq Scan on employees (cost=0.00..14.95 rows=495 width=59)
(3 rows)
优化器通常优先选择使用 HashAggregate 策略进行分组聚合操作，因为这些方法通常比传统的 Group 操作（基于排序的分组）更高效。尤其是在没有明显指示要求强制使用排序分组的情况下，优化器倾向于选择哈希聚合，因为它通常更快、更节省资源。
stone@postgres=> SET enable_hashagg = off;
SET
stone@postgres=> EXPLAIN SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department;
QUERY PLAN
-------------------------------------------------------------------------
GroupAggregate (cost=37.10..43.32 rows=200 width=91)
Group By Key: department
-> Sort (cost=37.10..38.34 rows=495 width=59)
Sort Key: department
-> Seq Scan on employees (cost=0.00..14.95 rows=495 width=59)
(5 rows)
stone@postgres=> RESET enable_hashagg;
RESET
在进行 GroupAggregate 之前，必须先对数据进行排序，这是因为 GroupAggregate 需要按照 department 字段的顺序处理数据以有效地进行分组。
SetOp 算子
在 SQL 查询的 EXPLAIN 输出中，SetOp、HashSetOp 表示集合操作，这通常涉及对数据集进行一些集合理论的操作，如去重（DISTINCT）、集合并集（UNION）、交集（INTERSECT）、差集（EXCEPT）等。这些操作常见于需要处理多个数据集并对其进行组合或去除重复数据的情况。通常出现在以下几种情况：
- DISTINCT 操作：当查询包含
SELECT DISTINCT时，可能会使用SetOp来去除重复的行。 - 集合并集、交集和差集：当查询使用了
UNION、INTERSECT、EXCEPT等 SQL 关键字时，SetOp可能被用来执行这些集合操作。
例子：使用 SetOp 的执行计划
stone@postgres=> EXPLAIN SELECT name FROM employees WHERE department = 'Sales'
EXCEPT
SELECT name FROM employees WHERE department = 'HR';
QUERY PLAN
-------------------------------------------------------------------------------
HashSetOp Except (cost=0.00..32.42 rows=1 width=68)
-> Append (cost=0.00..32.41 rows=4 width=68)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..16.21 rows=2 width=68)
-> Seq Scan on employees (cost=0.00..16.19 rows=2 width=68)
Filter: ((department)::text = 'Sales'::text)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..16.21 rows=2 width=68)
-> Seq Scan on employees (cost=0.00..16.19 rows=2 width=68)
Filter: ((department)::text = 'HR'::text)
(8 rows)
该查询语句结果为空集，仅作 SetOp 演示作用。对两个子查询进行了求差集操作，分别进行了两个 Subquery Scan 操作，Append 后由 HashSetOp Except 处理。
调优流程
对慢 SQL 语句进行分析，通常包括以下步骤：
- 收集 SQL 中涉及到的所有表的统计信息。在数据库中，统计信息是优化器生成计划的源数据。没有收集统计信息或者统计信息陈旧往往会造成执行计划严重劣化，从而导致性能问题。从经验数据来看，10% 左右性能问题是因为没有收集统计信息。
- 通过查看执行计划来查找原因。如果 SQL 长时间运行未结束，通过
EXPLAIN命令查看执行计划，进行初步定位。如果 SQL 可以运行出来，则推荐使用EXPLAIN ANALYZE或EXPLAIN PERFORMANCE查看执行计划及实际运行情况，以便更精准地定位问题原因。 - 审视和修改表定义
- 针对
EXPLAIN或EXPLAIN PERFORMANCE信息，定位 SQL 慢的具体原因以及改进措施。 - 通常情况下，有些 SQL 语句可以通过查询重写转换成等价的，或特定场景下等价的语句。重写后的语句比原语句更简单，且可以简化某些执行步骤达到提升性能的目的。查询重写方法在各个数据库中基本是通用的。
更新统计信息
在数据库中，统计信息是优化器生成计划的源数据。没有收集统计信息或者统计信息陈旧往往会造成执行计划严重劣化，从而导致性能问题。
ANALYZE语句可收集与数据库中表内容相关的统计信息，统计结果存储在系统表PG_STATISTIC中。查询优化器会使用这些统计数据，以生成最高效的执行计划。- 建议在执行了大批量插入/删除操作后，例行对表或全库执行
ANALYZE语句更新统计信息。目前默认收集统计信息的采样比例是 30000 行（即：GUC 参数default_statistics_target默认设置为 100），如果表的总行数超过一定行数（大于 1600000），建议设置 GUC 参数default_statistics_target为-2，即按 2% 收集样本估算统计信息。 - 对于在批处理脚本或者存储过程中生成的中间表，也需要在完成数据生成之后显式的调用
ANALYZE。 - 对于表中多个列有相关性且查询中有同时基于这些列的条件或分组操作的情况，可尝试收集多列统计信息，以便查询优化器可以更准确地估算行数，并生成更有效的执行计划。
语法：
--更新单个表的统计信息。
ANALYZE tablename;
--更新全库的统计信息。
ANALYZE;
使用以下命令进行多列统计信息相关操作：
--收集tablename表的column_1、column_2列的多列统计信息。
ANALYZE tablename ((column_1, column_2));
--添加tablename表的column_1、column_2列的多列统计信息声明。
ALTER TABLE tablename ADD STATISTICS ((column_1, column_2));
--收集单列统计信息，并收集已声明的多列统计信息。
ANALYZE tablename;
--删除tablename表的column_1、column_2列的多列统计信息或其声明。
ALTER TABLE tablename DELETE STATISTICS ((column_1, column_2));
注意：
- 在使用
ALTER TABLE tablename ADD STATISTICS语句添加了多列统计信息声明后，系统并不会立刻收集多列统计信息，而是在下次对该表或全库进行ANALYZE时，进行多列统计信息的收集。 - 如果想直接收集多列统计信息，请使用
ANALYZE命令进行收集。 - 使用
EXPLAIN查看各 SQL 的执行计划时，如果发现某个表SEQ SCAN的输出中rows=10，rows=10是系统给的默认值，有可能该表没有进行ANALYZE，需要对该表执行ANALYZE。
审视和修改表定义
概述
好的表定义至少需要达到以下几个目标：
- 减少扫描数据量。通过分区的剪枝机制可以实现。
- 尽量减少随机 IO。通过聚簇/局部聚簇可以实现。
表定义在数据库设计阶段创建，在 SQL 调优过程中进行审视和修改。
选择存储模型
进行数据库设计时，表设计上的一些关键项将严重影响后续整库的查询性能。表设计对数据存储也有影响：好的表设计能够减少 IO 操作及最小化内存使用，进而提升查询性能。
表的存储模型选择是表定义的第一步。客户业务属性是表的存储模型的决定性因素，依据下面表格选择适合当前业务的存储模型。
| 存储模型 | 适用场景 |
|---|---|
| 行存 | 点查询（返回记录少，基于索引的简单查询）。增删改比较多的场景。 |
| 列存 | 统计分析类查询（GROUP 、JOIN 多的场景）。 |
使用局部聚簇
局部聚簇（Partial Cluster Key）是列存下的一种技术。这种技术可以通过最小化或者最大化稀疏索引以便快速过滤基表。Partial Cluster Key 可以指定多列，但是一般不建议超过 2 列。Partial Cluster Key 的选取原则：
- 受基表中的简单表达式约束。此类约束通常以
col op const的形式表示，其中col表示列名，op表示运算符（包括=、>、>=、<=和<），而const表示常量。 - 尽量采用选择度比较高（过滤掉更多数据）的简单表达式中的列。
- 尽量把选择度比较低的约束
col放在 Partial Cluster Key 中的前面。 - 尽量把枚举类型的列放在 Partial Cluster Key 中的前面。
使用分区表
分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储。这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。分区表和普通表相比具有以下优点：
- 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
- 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
- 方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。
openGauss 数据库支持的分区表为一级分区表和二级分区表，其中一级分区表包括范围分区表、间隔分区表、列表分区表、哈希分区表四种，二级分区表包括范围分区、列表分区、哈希分区两两组合的九种。
- 范围分区表：将数据基于范围映射到每一个分区，这个范围是由创建分区表时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期，例如将销售数据按照月份进行分区。
- 间隔分区表：是一种特殊的范围分区表，相比范围分区表，新增间隔值定义，当插入记录找不到匹配的分区时，可以根据间隔值自动创建分区。
- 列表分区表：将数据中包含的键值分别存储再不同的分区中，依次将数据映射到每一个分区，分区中包含的键值由创建分区表时指定。
- 哈希分区表：将数据根据内部哈希算法依次映射到每一个分区中，包含的分区个数由创建分区表时指定。
- 二级分区表：由范围分区、列表分区、哈希分区任意组合得到的分区表，其一级分区和二级分区均可以使用前面三种定义方式。
选择数据类型
高效数据类型，主要包括以下三方面：
- 尽量使用执行效率比较高的数据类型：一般来说整型数据运算（包括
=、＞、＜、>=、<=、<>等常规的比较运算，以及GROUP BY）的效率比字符串、浮点数要高。比如某客户场景中对列存表进行点查询，过滤条件在一个类型为NUMERIC列上，执行时间超过 10 秒；修改NUMERIC为INT类型之后，执行时间缩短为 1.8 秒左右。 - 尽量使用短字段的数据类型：长度较短的数据类型不仅可以减小数据文件的大小，提升 IO 性能；同时也可以减小相关计算时的内存消耗，提升计算性能。比如对于整型数据，如果可以用
SMALLINT就尽量不用INT，如果可以用INT就尽量不用BIGINT。 - 使用一致的数据类型：表关联列尽量使用相同的数据类型。如果表关联列数据类型不同，数据库必须动态地转化为相同的数据类型进行比较，这种转换会带来一定的性能开销。
典型的 SQL 优化方法
SQL 优化需要持续不断地进行分析和尝试。在将查询用于服务之前，先运行这些查询，以确定其性能是否满足要求。如果不满足，就通过检查执行计划并找出原因来对查询进行优化。然后，再次运行并优化这些查询，直至其满足要求为止。
SQL 自我诊断
在查询数据或运行 INSERT、DELETE、UPDATE 或 CREATE TABLE AS 语句时，可能会出现性能问题。在这种情况下，可以查询 GS_WLM_SESSION_STATISTICS 和 GS_WLM_SESSION_HISTORY 视图中的告警列，以获取性能优化的参考信息。
能够触发 SQL 自我诊断的告警取决于参数 resource_track_level 的设置。如果 resource_track_level 设置为 query，那么关于收集列统计信息失败以及 SQL 语句下推失败的告警将触发诊断。如果 resource_track_level 设置为 operator，则所有告警都将触发诊断。
一个 SQL 执行计划是否会被诊断取决于 resource_track_cost 的设置（默认值 100000）。只有当一个 SQL 执行计划的执行代价大于 resource_track_cost 时，它才会被诊断。可以使用 EXPLAIN 关键字来查看执行计划的执行代价。
目前支持对多列/单列统计信息未收集导致性能问题的场景上报告警。
如果存在单列或者多列统计信息未收集，则上报相关告警。
注意：
告警字符串长度上限为 2048。如果告警信息超过这个长度（例如存在大量未收集统计信息的超长表名、列名等信息）则不告警，只上报 WARNING：
WARNING, "Planner issue report is truncated, the rest of planner issues will be skipped"如果查询语句包含 LIMIT 运算符，执行计划中层级低于 LIMIT 的运算符将不会触发告警。这是由于 LIMIT 作为查询的最后阶段操作，会主动终止执行流。当达到指定行数限制后，执行引擎会立即终止下层运算符的执行。例如
SELECT * FROM logs LIMIT 100，当读取到第 100 行时，即使底层全表扫描运算符（Seq Scan）尚未完成，也会强制终止扫描过程。
子查询调优
应用程序通过 SQL 语句来操作数据库时会使用大量的子查询，这种写法比直接对两个表做连接操作在结构上和思路上更清晰，尤其是在一些比较复杂的查询语句中，子查询有更完整、更独立的语义，会使 SQL 对业务逻辑的表达更清晰更容易理解，因此得到了广泛的应用。
openGauss 根据子查询在 SQL 语句中的位置把子查询分成了子查询、子链接两种形式。
- 子查询 SubQuery：对应于查询解析树中的范围表 RangeTblEntry，更通俗一些指的是出现在 FROM 语句后面的独立的 SELECT 语句。
- 子链接 SubLink：对应于查询解析树中的表达式，更通俗一些指的是出现在 WHERE/ON 子句、Targetlist 里面的语句。
综上，对于查询解析树而言，SubQuery 的本质是范围表，而 SubLink 的本质是表达式。针对 SubLink 场景而言，由于 SubLink 可以出现在约束条件、表达式中，按照 openGauss 对 SubLink 的实现，SubLink 可以分为以下几类：
| 类型 | 语句 |
|---|---|
EXIST_SUBLINK | EXIST/NOT EXIST |
ANY_SUBLINK | OP ANY (SELECT...)（OP 为 IN、<、>、=） |
ALL_SUBLINK | OP ALL (SELECT...)（OP 为 IN、<、>、=） |
ROWCOMPARE_SUBLINK | RECORD OP (SELECT...) |
EXPR_SUBLINK | (SELECT with single targetlist item...) |
ARRAY_SUBLINK | ARRAY(SELECT...) |
CTE_SUBLINK | WITH QUERY(...) |
其中 OLAP、HTAP 场景中常用的 SubLink 为 EXIST_SUBLINK、ANY_SUBLINK，在 openGauss 的优化引擎中对其应用场景做了优化（子链接提升），由于 SQL 语句中子查询的灵活使用，过于复杂的 SQL 子查询可能会造成性能问题。
子查询从大类上来看，分为非相关子查询和相关子查询：
- 非相关子查询 None-Correlated SubQuery：子查询的执行不依赖于外层父查询的任何属性值。这样子查询具有独立性，可独自求解，形成一个子查询计划先于外层的查询求解。例如：
select t1.c1,t1.c2
from t1
where t1.c1 in (
select c2
from t2
where t2.c2 IN (2,3,4)
);
QUERY PLAN
----------------------------------------------------------------
Hash Join
Hash Cond: (t1.c1 = t2.c2)
-> Seq Scan on t1
Filter: (c1 = ANY ('{2,3,4}'::integer[]))
-> Hash
-> HashAggregate
Group By Key: t2.c2
-> Seq Scan on t2
Filter: (c2 = ANY ('{2,3,4}'::integer[]))
(9 rows)
- 相关子查询 Correlated-SubQuery：子查询的执行依赖于外层父查询的一些属性值（如下列示例
t2.c1 = t1.c1条件中的t1.c1）作为内层查询的一个条件。这样的子查询不具备独立性，需要和外层查询按分组进行求解。例如：
select t1.c1,t1.c2
from t1
where t1.c1 in (
select c2
from t2
where t2.c1 = t1.c1 AND t2.c2 in (2,3,4)
);
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on t1
Filter: (SubPlan 1)
SubPlan 1
-> Seq Scan on t2
Filter: ((c1 = t1.c1) AND (c2 = ANY ('{2,3,4}'::integer[])))
(5 rows)
针对 SubLink 的优化策略主要通过**子链接上拉（Sublink Pull-up）**技术，将子查询转换为高效的 JOIN 操作，避免生成 SubPlan + Broadcast 的低效执行计划。判断子查询是否存在性能风险，可以通过 EXPLAIN 查询语句查看 Sublink 的部分是否被转换成这种执行计划。
支持优化的子链接类型及场景
- IN 子链接上拉
- 条件：
- 子查询不包含外层查询的列。
- 无易变函数（如
random()）。
-- 原查询
SELECT t1.c1 FROM t1 WHERE t1.c1 IN (SELECT c2 FROM t2 WHERE t2.c2 IN (2,3,4));
-- 优化后计划
Hash Join
Hash Cond: (t1.c1 = t2.c2)
-> Seq Scan on t1
-> Hash
-> HashAggregate (Group By: t2.c2)
-> Seq Scan on t2 (Filter: c2 IN (2,3,4))
子查询转换为 Hash Join，避免逐行比较。
- EXISTS 子链接上拉
- 条件：
- 子查询 WHERE 子句必须包含外层查询的列。
- 子查询不含
GROUP BY、LIMIT、HAVING等复杂操作。
-- 原查询
SELECT t1.c1 FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.c1 = t1.c1);
-- 优化后计划
Hash Join
Hash Cond: (t1.c1 = t2.c1)
-> Seq Scan on t1
-> Hash
-> HashAggregate (Group By: t2.c1)
-> Seq Scan on t2
EXISTS 转换为 LEFT JOIN，通过检查 JOIN 结果是否存在过滤数据。
- 含聚合函数的等价相关子查询
- 条件：
- 子查询 WHERE 子句包含外层列与子查询列的等价比较（如
t1.c1 = t2.c1）。 - 子查询 SELECT 列表仅含聚合函数（如
MAX、MIN），且参数不来自外层表。
- 子查询 WHERE 子句包含外层列与子查询列的等价比较（如
-- 原查询
SELECT * FROM t1 WHERE c1 > (SELECT MAX(t2.c1) FROM t2 WHERE t2.c1 = t1.c1);
-- 优化后
SELECT t1.* FROM t1
LEFT JOIN (SELECT MAX(c1) max_c1, c1 FROM t2 GROUP BY c1) t2
ON t1.c1 = t2.c1
WHERE t1.c1 > t2.max_c1;
聚合结果通过 GROUP BY 预计算，避免重复执行子查询。
- OR 子句中的子链接上拉
- 条件：
- 当 WHERE 包含
OR连接的多个子链接时，逐个提取并优化。
- 当 WHERE 包含
-- 原查询
SELECT a FROM t1
WHERE t1.a = (SELECT AVG(a) FROM t3 WHERE t1.b = t3.b)
OR EXISTS (SELECT 1 FROM t4 WHERE t1.c = t4.c);
-- 优化后
SELECT a FROM t1
LEFT JOIN (SELECT AVG(a) avg_a, b FROM t3 GROUP BY b) t3 ON t1.a = avg_a AND t1.b = t3.b
LEFT JOIN (SELECT c FROM t4 GROUP BY c) t4 ON t1.c = t4.c
WHERE t3.b IS NOT NULL OR t4.c IS NOT NULL;
| 场景 | 未优化计划 | 优化后计划 | 性能提升 |
|---|---|---|---|
| IN 子查询 | 嵌套循环（SubPlan） | Hash Join | 减少迭代，批量处理 |
| EXISTS 子查询 | 逐行执行子查询 | 预计算 + 左连接 | 避免重复扫描子表 |
| 含聚合的相关子查询 | 多次执行聚合 | 预聚合 + JOIN | 聚合计算一次完成 |
不支持优化的场景及应对方案
目标列中的子查询（标量子查询）
- 示例：
SELECT (SELECT c2 FROM t2 WHERE t1.c1 = t2.c1) AS ssq FROM t1;- 问题：需处理外层 JOIN 未匹配时的 NULL 值。
- 手动优化：
WITH ssq AS (SELECT c2, c1 FROM t2) SELECT ssq.c2, t1.* FROM t1 LEFT JOIN ssq ON t1.c1 = ssq.c1;非等价相关子查询
- 示例：
SELECT t1.c1 FROM t1 WHERE c1 > (SELECT AVG(c2) FROM t2 WHERE t2.c2 > t1.c2);- 优化限制：无法直接转换为 JOIN。
- 替代方案：使用 CTE 预计算或自连接。
多表关联的复杂子查询
- 示例：
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2, t3 WHERE t1.a = t2.a AND t2.b = t3.b);- 优化方案：手动重写为显式 JOIN 或使用 CTE 分步处理。
统计信息调优
openGauss 是基于代价估算生成的最优执行计划。优化器需要根据 ANALYZE 收集的统计信息进行行数估算和代价估算，因此统计信息对优化器行数估算和代价估算起着至关重要的作用。通过 ANALYZE 收集全局统计信息，主要位于：pg_class 表中的 relpages 和 reltuples；pg_statistic 表中的 stadistinct、stanullfrac、stanumbersN、stavaluesN、histogram_bounds 等。
在很多场景下，由于查询中涉及到的表或列没有收集统计信息，会对查询性能有很大的影响。
例如以下表和查询：
CREATE TABLE LINEITEM
(
L_ORDERKEY BIGINT NOT NULL
, L_PARTKEY BIGINT NOT NULL
, L_SUPPKEY BIGINT NOT NULL
, L_LINENUMBER BIGINT NOT NULL
, L_QUANTITY DECIMAL(15,2) NOT NULL
, L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL
, L_DISCOUNT DECIMAL(15,2) NOT NULL
, L_TAX DECIMAL(15,2) NOT NULL
, L_RETURNFLAG CHAR(1) NOT NULL
, L_LINESTATUS CHAR(1) NOT NULL
, L_SHIPDATE DATE NOT NULL
, L_COMMITDATE DATE NOT NULL
, L_RECEIPTDATE DATE NOT NULL
, L_SHIPINSTRUCT CHAR(25) NOT NULL
, L_SHIPMODE CHAR(10) NOT NULL
, L_COMMENT VARCHAR(44) NOT NULL
) with (orientation = column, COMPRESSION = MIDDLE);
CREATE TABLE ORDERS
(
O_ORDERKEY BIGINT NOT NULL
, O_CUSTKEY BIGINT NOT NULL
, O_ORDERSTATUS CHAR(1) NOT NULL
, O_TOTALPRICE DECIMAL(15,2) NOT NULL
, O_ORDERDATE DATE NOT NULL
, O_ORDERPRIORITY CHAR(15) NOT NULL
, O_CLERK CHAR(15) NOT NULL
, O_SHIPPRIORITY BIGINT NOT NULL
, O_COMMENT VARCHAR(79) NOT NULL
)with (orientation = column, COMPRESSION = MIDDLE);
explain verbose
select count(*) as numwait
from lineitem l1, orders
where o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and not exists (select *
from lineitem l3
where l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate)
order by numwait desc;
当出现性能问题时，可以通过如下方法确认查询中涉及到的表或列有没有做过 ANALYZE 收集统计信息。
- 通过
explain verbose执行query分析执行计划时会提示WARNING信息，如下所示：
WARNING:Statistics in some tables or columns(public.lineitem.l_receiptdate, public.lineitem.l_commitdate, public.lineitem.l_orderkey, public.lineitem.l_suppkey, public.orders.o_orderstatus, public.orders.o_orderkey) are not collected.
HINT:Do analyze for them in order to generate optimized plan.
- 可以通过在
pg_log目录下的日志文件中查找以下信息来确认是当前执行的查询是否由于没有收集统计信息导致查询性能变差。
2017-06-14 17:28:30.336 CST 140644024579856 20971684 [BACKEND] LOG:Statistics in some tables or columns(public.lineitem.l_receiptdate, public.lineitem.l_commitdate, public.lineitem.l_orderkey, public.linei
tem.l_suppkey, public.orders.o_orderstatus, public.orders.o_orderkey) are not collected.
2017-06-14 17:28:30.336 CST 140644024579856 20971684 [BACKEND] HINT:Do analyze for them in order to generate optimized plan.
当通过以上方法查看到哪些表或列没有做 ANALYZE，可以通过对 WARNING 或日志中上报的表或列做 ANALYZE 可以解决由于未收集统计信息导致查询变慢的问题。
算子级调优
一个查询语句要经过多个算子步骤才会输出最终的结果。由于个别算子耗时过长导致整体查询性能下降的情况比较常见。这些算子是整个查询的瓶颈算子。通用的优化手段是 EXPLAIN ANALYZE/PERFORMANCE 命令查看执行过程的瓶颈算子，然后进行针对性优化。
如下面的执行过程信息中，Hashagg 算子的执行时间占总时间的：(51016-13535)/56476 ≈ 66%，此处 Hashagg 算子就是这个查询的瓶颈算子，在进行性能优化时应当优先考虑此算子的优化。
| id | operation | A-time | A-rows | E-rows | Peak Memory | E-memory | A-width | E-width | E-costs
|----|--------------------------------------------------------------------|-----------------------|-----------|----------|---------------------|----------|---------|---------|-----------
| 1 | -> Row Adapter | 56476.397 | 10000000 | 237060 | 19KB | | | 20 | 20933222.75
| 2 | -> Vector Streaming (type: GATHER) | 55614.28 | 10000000 | 237060 | 243 | | | 20 | 20933222.75
| 3 | -> Vector Hash Aggregate | [52624.685,55132.180] | 10000000 | 237060 | [29349KB,29441KB] | 16MB | [20.20] | 20 | 20918406.50
| 4 | -> Vector Streaming (type: REDISTRIBUTE) | [52519.781,53209.779] | 339364604 | 4856184 | [1219KB,1219KB] | 1MB | | 20 | 10461210.65
| 5 | -> Vector Hash Aggregate | [35675.636,51016.424] | 339364604 | 4856184 | [732850KB,746894KB] | 16MB | [20.20] | 20 | 10457195.65
| 6 | -> Vector Partition Iterator | [9035.202,13656.884] | 970000000 | 93583097 | 99KB | 1MB | | 20 | 10195891.68
| 7 | -> Partition CStore Scan on xujie_ep_day_energy_csv_1 | [9015.645,13535.346] | 970000000 | 93583097 | [845KB,845KB] | 1MB | | 20 | 10195891.68
(7 rows)
例子：基表扫描时，对于点查或者范围扫描等过滤大量数据的查询，如果使用 SeqScan 全表扫描会比较耗时，可以在条件列上建立索引选择IndexScan进行索引扫描提升扫描效率。
openGauss=# explain (analyze on, costs off) select * from store_sales where ss_sold_date_sk = 2450944;
id | operation | A-time | A-rows | Peak Memory | A-width
----+--------------------------------+---------------------+--------+--------------+---------
1 | -> Streaming (type: GATHER) | 3666.020 | 3360 | 195KB |
2 | -> Seq Scan on store_sales | [3594.611,3594.611] | 3360 | [34KB, 34KB] |
(2 rows)
Predicate Information (identified by plan id)
-----------------------------------------------
2 --Seq Scan on store_sales
Filter: (ss_sold_date_sk = 2450944)
Rows Removed by Filter: 4968936
openGauss=# create index idx on store_sales_row(ss_sold_date_sk);
CREATE INDEX
openGauss=# explain (analyze on, costs off) select * from store_sales_row where ss_sold_date_sk = 2450944;
id | operation | A-time | A-rows | Peak Memory | A-width
----+------------------------------------------------+-----------------+--------+--------------+----------
1 | -> Streaming (type: GATHER) | 81.524 | 3360 | 195KB |
2 | -> Index Scan using idx on store_sales_row | [13.352,13.352] | 3360 | [34KB, 34KB] |
(2 rows)
上述例子中，全表扫描返回 3360 条数据，过滤掉大量数据，在 ss_sold_date_sk 列上建立索引后，使用 IndexScan 扫描效率显著提高，从 3.6 秒提升到 13 毫秒。
例子：如果从执行计划中看，两表 Join 选择了 NestLoop，而实际行数比较大时，NestLoop Join 可能执行比较慢。如下的例子中 NestLoop 耗时 181 秒，如果设置参数 enable_mergejoin=off 关掉 Merge Join，同时设置参数 enable_nestloop=off 关掉 NestLoop，让优化器选择 HashJoin，则 Join 耗时提升至 200 多毫秒。
openGauss=# explain analyze select count(*) from store_sales ss, item i where ss.ss_item_sk = i.i_item_sk;
| id | operation | A-time | A-rows | E-rows | Peak Memory | E-memory | A-width | E-width | E-costs
|----|-----------------------------------------------|-------------------------|-------------|----------|---------------|----------|---------|---------|----------
| 1 | -> Row Adapter | 184300.301 | 1 | 1 | 110KB | | | 0 | 48629179.77
| 2 | -> Vector Aggregate | 184300.280 | 1 | 1 | 181KB | | | 0 | 48629179.77
| 3 | -> Vector Streaming (type: GATHER) | 184300.186 | 1 | 1 | 186KB | | | 0 | 48629179.77
| 4 | -> Vector Aggregate | [184255.394,184252.368] | 4 | 4 | [140KB,140KB] | 1MB | | 0 | 48629179.61
| 5 | -> Vector Nest Join (6,7) | [162910.848,181438.162] | 28804004 | 28804004 | [74KB,74KB] | 1MB | | 0 | 48627839.35
| 6 | -> CStore Scan on store_sales ss | [115360.16,18229] | 28804004 | 28804004 | [490KB,490KB] | 1MB | | 4 | 16683.10
| 7 | -> Vector Materialize | [113814.521,132478.454] | 12968211302 | 18000 | [869KB,900KB] | 16MB | [8,8] | 4 | 3890.00
| 8 | -> CStore Scan on item i | [0.234,0.243] | 18000 | 18000 | [476KB,476KB] | 1MB | | 4 | 3867.50
(8 rows)
openGauss=# set enable_nestloop=off;
SET
openGauss=# set enable_mergejoin=off;
SET
openGauss=# explain analyze select count(*) from store_sales ss, item i where ss.ss_item_sk = i.i_item_sk;
| id | operation | A-time | A-rows | E-rows | Peak Memory | E-memory | A-width | E-width | E-costs
|----|-----------------------------------------------|---------------------|---------|---------|---------------|----------|---------|---------|----------
| 1 | -> Row Adapter | 291.066 | 1 | 1 | 11KB | | 0 | 0 | 32308.66
| 2 | -> Vector Aggregate | 291.052 | 1 | 1 | 181KB | | 0 | 0 | 32308.66
| 3 | -> Vector Streaming (type: GATHER) | 290.973 | 4 | 4 | 186KB | | 0 | 0 | 32308.66
| 4 | -> Vector Aggregate | [220.792,234.532] | 4 | 4 | [140KB,140KB] | 1MB | 0 | 0 | 32308.50
| 5 | -> Vector Hash Join (6,7) | [209.987,223.345] | 2880404 | 2880404 | [236KB,241KB] | 16MB | [8,8] | 4 | 32508.24
| 6 | -> CStore Scan on store_sales ss | [13.312,13.718] | 2880404 | 2880404 | [490KB,490KB] | 1MB | 0 | 0 | 16683.10
| 7 | -> CStore Scan on item i | [0.214,0.246] | 16000 | 16000 | [477KB,477KB] | 1MB | 0 | 0 | 3067.50
(7 rows)
例子：通常情况下 Agg 选择 HashAgg 性能较好，如果大结果集选择了 Sort+GroupAgg，则需要设置 enable_sort=off，HashAgg 耗时明显优于 Sort+GroupAgg。
openGauss=# explain analyze select count(*) from store_sales group by ss_item_sk;
| id | operation | A-time | A-rows | E-rows | Peak Memory | E-memory | A-width | E-width | E-costs
|----|----------------------------------------|---------------------|---------|---------|---------------------|----------|---------|---------|----------
| 1 | -> Row Adapter | 1977.385 | 18000 | 17644 | 20KB | | | 4 | 92875.24
| 2 | -> Vector Streaming (type: GATHER) | 1973.617 | 18000 | 17644 | 1946KB | | | 4 | 92875.24
| 3 | -> Vector Sort Aggregate | [1784.800,1883.243] | 18000 | 17644 | [273KB,273KB] | 1MB | | 4 | 92186.02
| 4 | -> Vector Sort | [1752.270,1848.357] | 2880404 | 2880404 | [124866KB,135135KB] | 16MB | [8,8] | 4 | 88564.14
| 5 | -> CStore Scan on store_sales | [12.463,13.548] | 2880404 | 2880404 | [490KB,490KB] | 1MB | | 4 | 16683.10
(5 rows)
openGauss=# set enable_sort=off;
SET
openGauss=# explain analyze select count(*) from store_sales group by ss_item_sk;
| id | operation | A-time | A-rows | E-rows | Peak Memory | E-memory | A-width | E-width | E-costs |
|----|---------------------------------------|---------------------|---------|---------|---------------------|----------|---------|---------|----------|
| 1 | -> Row Adapter | 838.218 | 18000 | 17644 | 20KB | | | 4 | 21016.93 |
| 2 | -> Vector Streaming (type: GATHER) | 834.264 | 18000 | 17644 | 228KB | | | 4 | 21016.93 |
| 3 | -> Vector Hash Aggregate | [585.017,758.204] | 18000 | 17644 | [262552KB,262564KB] | 16MB | [8,8] | 4 | 20327.72 |
| 4 | -> CStore Scan on store_sales | [12.540,13.941] | 2880404 | 2880404 | [490KB,490KB] | 1MB | | 4 | 16683.10 |
(4 rows)
SQL 语句改写规则
根据数据库的 SQL 执行机制以及大量的实践，总结发现：通过一定的规则调整 SQL 语句，在保证结果正确的基础上，能够提高 SQL 执行效率。如果遵守这些规则，常常能够大幅度提升业务查询效率。
使用
union all代替union：union在合并两个集合时会执行去重操作，而union all则直接将两个结果集合并、不执行去重。执行去重会消耗大量的时间，因此，在一些实际应用场景中，如果通过业务逻辑已确认两个集合不存在重叠，可用union all替代union以便提升性能。join列增加非空过滤条件：若join列上的 NULL 值较多，则可以加上is not null过滤条件，以实现数据的提前过滤，提高join效率。not in转not exists：not in语句需要使用nestloop anti join来实现，而not exists则可以通过hash anti join来实现。在join列不存在 NULL 值的情况下，not exists和not in等价。因此在确保没有 NULL 值时，可以通过将not in转换为not exists，通过生成hash join来提升查询效率。选择
hashagg：查询中GROUP BY语句如果生成了groupagg + sort的执行计划性能会比较差，可以通过加大work_mem的方法生成hashagg的执行计划，因为不用排序而提高性能。尝试将函数替换为
case语句：openGauss 函数调用性能较低，如果出现过多的函数调用导致性能下降很多，可以根据情况把下推函数改成CASE表达式。避免对索引使用函数或表达式运算：对索引使用函数或表达式运算会停止使用索引转而执行全表扫描。
尽量避免在
where子句中使用!=或<>操作符、NULL 值判断、OR 连接、参数隐式转换对复杂 SQL 语句进行拆分：对于过于复杂并且不易通过以上方法调整性能的 SQL 可以考虑拆分的方法，把 SQL 中某一部分拆分成独立的 SQL 并把执行结果存入临时表，拆分常见的场景包括但不限于：
作业中多个 SQL 有同样的子查询，并且子查询数据量较大。
执行计划成本计算不准，导致子查询 Hash Bucket 太小，比如实际数据 1000 万行，Hash Bucket 只有 1000。
函数（如
substr、to_number）导致大数据量子查询选择度计算不准。
SQL 调优关键参数调整
影响openGauss SQL调优性能的关键数据库主节点配置参数有：
| 参数/参考值 | 描述 |
|---|---|
| enable_nestloop=on | 控制查询优化器对嵌套循环连接（Nest Loop Join）类型的使用。 当设置为 on 后，优化器优先使用 Nest Loop Join；当设置为 off 后，优化器在存在其他方法时将优先选择其他方法。说明：如果只需要在当前数据库连接（即当前 Session）中临时更改该参数值，则只需要在 SQL 语句中执行如下命令： SET enable_nestloop to off;此参数默认设置为 on，但实际调优中应根据情况选择是否关闭。一般情况下，在三种 Join 方式（Nested Loop、Merge Join 和 Hash Join）里，Nested Loop 性能较差，实际调优中可以选择关闭。 |
| enable_bitmapscan=on | 控制查询优化器对位图扫描规划类型的使用。 设置为 on，表示使用；设置为 off，表示不使用。说明：如果只需要在当前数据库连接（即当前 Session）中临时更改该参数值，则只需要在SQL语句中执行命令如下命令： SET enable_bitmapscan to off;Bitmapscan 扫描方式适用于 where a > 1 and b > 1且 a 列和 b 列都有索引这种查询条件，但有时其性能不如 Indexscan。因此，现场调优如发现查询性能较差且计划中有 Bitmapscan 算子，可以关闭 Bitmapscan，看性能是否有提升。 |
| enable_hashagg=on | 控制优化器对 Hash 聚集的使用。 |
| enable_hashjoin=on | 控制优化器对 Hash 连接的使用。 |
| enable_mergejoin=on | 控制优化器对合并连接的使用。 |
| enable_indexscan=on | 控制优化器对索引扫描的使用。 |
| enable_indexonlyscan=on | 控制优化器对仅索引扫描的使用。 |
| enable_seqscan=on | 控制优化器对顺序扫描的使用。 完全消除顺序扫描是不可能的，但是关闭这个变量会让优化器在存在其他方法的时候优先选择其他方法。 |
| enable_sort=on | 控制优化器使用的排序步骤。 该设置不可能完全消除明确的排序，但是关闭这个变量可以让优化器在存在其他方法的时候优先选择其他方法。 |
| rewrite_rule | 控制优化器是否启用 LAZY_AGG 和 MAGIC_SET 重写规则。 |
| sql_beta_feature | 控制优化器是否启用 SEL_SEMI_POISSON, SEL_EXPR_INSTR, PARAM_PATH_GEN, RAND_COST_OPT, PARAM_PATH_OPT, PAGE_EST_OPT, CANONICAL_PATHKEY, PARTITION_OPFUSION, PREDPUSH_SAME_LEVEL, PARTITION_FDW_ON, DISABLE_BITMAP_COST_WITH_LOSSY_PAGES |
| var_eq_const_selectivity | 控制优化器是否使用直方图计算整型常量的选择率。 |
| partition_page_estimation | 控制分区表页面是否通过剪枝结果进行页面估算优化，只包括分区表和 LOCAL 索引页面，不包括全局索引页面。估算公式为： 估算后页面 = 分区表总页面 * （剪枝后分区数 / 总分区数）。 |
| partition_iterator_elimination | 控制分区表在分区剪枝结果为一个分区时，是否通过消除分区迭代算子来提升执行效率。 |
| enable_functional_dependency | 控制函数依赖统计信息的使用。 设置为 on：1. 执行 ANALYZE 生成的多列统计信息包含函数依赖统计信息。 2. 计算选择率会使用函数依赖统计信息。 设置为 off：1. 执行 ANALYZE 生成的多列统计信息不包含函数依赖统计信息。 2. 计算选择率不会使用函数依赖统计信息。 说明：函数依赖（Functional Dependency）的概念来自于关系数据库范式（Normal Form），表示属性间的函数关系。函数依赖统计信息，对此概念进行了扩展，表示满足函数关系的数据量占总数据量的比例。函数依赖统计信息是多列统计信息的一种，可以用于提升选择率估算的准确率。 函数依赖统计信息适用于形如 where a = 1 and b = 1 的格式，要求a和b均是同一个表的属性，约束条件为等式约束，约束条件用 AND 连接，约束条件至少为两个。 |
| enable_seqscan_fusion | 控制 Seqscan 底噪消除是否打开。 |
使用 Plan Hint 进行调优
Plan Hint 调优概述
Plan Hint 为用户提供了直接影响执行计划生成的手段，用户可以通过指定 Join 顺序、Join 方式、Scan 方法、指定结果行数等多个手段来进行执行计划的调优，以提升查询的性能。
语法：
Plan Hint 支持在 SELECT 关键字后通过如下形式指定：
/*+ <plan hint> */
可以同时指定多个 Hint，之间使用空格分隔。Hint 只能影响当前层的计划，对于子查询，需要在子查询的 SELECT 关键字后指定 Hint。
例如：
select /*+ <plan_hint1> <plan_hint2> */ * from t1, (select /*+ <plan_hint3> */ from t2) where 1=1;
其中 <plan_hint1>，<plan_hint2> 为外层查询的 Hint，<plan_hint3> 为内层子查询的 Hint。
注意：
- 如果在视图定义（CREATE VIEW）时指定 Hint，则在该视图每次被应用时会使用该 Hint。
- 当使用 Random Plan 功能（参数
plan_mode_seed不为0）时，查询指定的 Plan Hint 不会被使用。
当前版本 Plan Hint 支持的范围如下：
- 指定 Join 顺序的 Hint
- 指定 Join 方式的 Hint
- 指定结果集行数的 Hint
- 指定 Scan 方式的 Hint
- 指定子链接块名的 Hint
当前版本 Plan Hint 不支持 Agg、Sort、Setop 和 Subplan。
本章节使用的表如下：
create table store
(
s_store_sk integer not null,
s_store_id char(16) not null,
s_rec_start_date date ,
s_rec_end_date date ,
s_closed_date_sk integer ,
s_store_name varchar(50) ,
s_number_employees integer ,
s_floor_space integer ,
s_hours char(20) ,
s_manager varchar(40) ,
s_market_id integer ,
s_geography_class varchar(100) ,
s_market_desc varchar(100) ,
s_market_manager varchar(40) ,
s_division_id integer ,
s_division_name varchar(50) ,
s_company_id integer ,
s_company_name varchar(50) ,
s_street_number varchar(10) ,
s_street_name varchar(60) ,
s_street_type char(15) ,
s_suite_number char(10) ,
s_city varchar(60) ,
s_county varchar(30) ,
s_state char(2) ,
s_zip char(10) ,
s_country varchar(20) ,
s_gmt_offset decimal(5,2) ,
s_tax_precentage decimal(5,2) ,
primary key (s_store_sk)
);
create table store_sales
(
ss_sold_date_sk integer ,
ss_sold_time_sk integer ,
ss_item_sk integer not null,
ss_customer_sk integer ,
ss_cdemo_sk integer ,
ss_hdemo_sk integer ,
ss_addr_sk integer ,
ss_store_sk integer ,
ss_promo_sk integer ,
ss_ticket_number integer not null,
ss_quantity integer ,
ss_wholesale_cost decimal(7,2) ,
ss_list_price decimal(7,2) ,
ss_sales_price decimal(7,2) ,
ss_ext_discount_amt decimal(7,2) ,
ss_ext_sales_price decimal(7,2) ,
ss_ext_wholesale_cost decimal(7,2) ,
ss_ext_list_price decimal(7,2) ,
ss_ext_tax decimal(7,2) ,
ss_coupon_amt decimal(7,2) ,
ss_net_paid decimal(7,2) ,
ss_net_paid_inc_tax decimal(7,2) ,
ss_net_profit decimal(7,2) ,
primary key (ss_item_sk, ss_ticket_number)
);
create table store_returns
(
sr_returned_date_sk integer ,
sr_return_time_sk integer ,
sr_item_sk integer not null,
sr_customer_sk integer ,
sr_cdemo_sk integer ,
sr_hdemo_sk integer ,
sr_addr_sk integer ,
sr_store_sk integer ,
sr_reason_sk integer ,
sr_ticket_number integer not null,
sr_return_quantity integer ,
sr_return_amt decimal(7,2) ,
sr_return_tax decimal(7,2) ,
sr_return_amt_inc_tax decimal(7,2) ,
sr_fee decimal(7,2) ,
sr_return_ship_cost decimal(7,2) ,
sr_refunded_cash decimal(7,2) ,
sr_reversed_charge decimal(7,2) ,
sr_store_credit decimal(7,2) ,
sr_net_loss decimal(7,2) ,
primary key (sr_item_sk, sr_ticket_number)
);
create table customer
(
c_customer_sk integer not null,
c_customer_id char(16) not null,
c_current_cdemo_sk integer ,
c_current_hdemo_sk integer ,
c_current_addr_sk integer ,
c_first_shipto_date_sk integer ,
c_first_sales_date_sk integer ,
c_salutation char(10) ,
c_first_name char(20) ,
c_last_name char(30) ,
c_preferred_cust_flag char(1) ,
c_birth_day integer ,
c_birth_month integer ,
c_birth_year integer ,
c_birth_country varchar(20) ,
c_login char(13) ,
c_email_address char(50) ,
c_last_review_date char(10) ,
primary key (c_customer_sk)
);
create table promotion
(
p_promo_sk integer not null,
p_promo_id char(16) not null,
p_start_date_sk integer ,
p_end_date_sk integer ,
p_item_sk integer ,
p_cost decimal(15,2) ,
p_response_target integer ,
p_promo_name char(50) ,
p_channel_dmail char(1) ,
p_channel_email char(1) ,
p_channel_catalog char(1) ,
p_channel_tv char(1) ,
p_channel_radio char(1) ,
p_channel_press char(1) ,
p_channel_event char(1) ,
p_channel_demo char(1) ,
p_channel_details varchar(100) ,
p_purpose char(15) ,
p_discount_active char(1) ,
primary key (p_promo_sk)
);
create table customer_address
(
ca_address_sk integer not null,
ca_address_id char(16) not null,
ca_street_number char(10) ,
ca_street_name varchar(60) ,
ca_street_type char(15) ,
ca_suite_number char(10) ,
ca_city varchar(60) ,
ca_county varchar(30) ,
ca_state char(2) ,
ca_zip char(10) ,
ca_country varchar(20) ,
ca_gmt_offset decimal(5,2) ,
ca_location_type char(20) ,
primary key (ca_address_sk)
);
create table item
(
i_item_sk integer not null,
i_item_id char(16) not null,
i_rec_start_date date ,
i_rec_end_date date ,
i_item_desc varchar(200) ,
i_current_price decimal(7,2) ,
i_wholesale_cost decimal(7,2) ,
i_brand_id integer ,
i_brand char(50) ,
i_class_id integer ,
i_class char(50) ,
i_category_id integer ,
i_category char(50) ,
i_manufact_id integer ,
i_manufact char(50) ,
i_size char(20) ,
i_formulation char(20) ,
i_color char(20) ,
i_units char(10) ,
i_container char(10) ,
i_manager_id integer ,
i_product_name char(50) ,
primary key (i_item_sk)
);
不使用 Hint 的查询，其执行计划如下：
EXPLAIN
SELECT i_product_name product_name,
i_item_sk item_sk,
s_store_name store_name,
s_zip store_zip,
ad2.ca_street_number c_street_number,
ad2.ca_street_name c_street_name,
ad2.ca_city c_city,
ad2.ca_zip c_zip,
count(*) cnt,
sum(ss_wholesale_cost) s1,
sum(ss_list_price) s2,
sum(ss_coupon_amt) s3
FROM store_sales,
store_returns,
store,
customer,
promotion,
customer_address ad2,
item
WHERE ss_store_sk = s_store_sk
AND ss_customer_sk = c_customer_sk
AND ss_item_sk = i_item_sk
and ss_item_sk = sr_item_sk
and ss_ticket_number = sr_ticket_number
and c_current_addr_sk = ad2.ca_address_sk
and ss_promo_sk = p_promo_sk
and i_color in ('maroon', 'burnished', 'dim', 'steel', 'navajo', 'chocolate')
and i_current_price between 35 and 35 + 10
and i_current_price between 35 + 1 and 35 + 15
group by i_product_name,
i_item_sk,
s_store_name,
s_zip,
ad2.ca_street_number,
ad2.ca_street_name,
ad2.ca_city,
ad2.ca_zip;
|QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|HashAggregate (cost=20.38..20.39 rows=1 width=385) |
| Group By Key: item.i_product_name, item.i_item_sk, store.s_store_name, store.s_zip, ad2.ca_street_number, ad2.ca_street_name, ad2.ca_city, ad2.ca_zip |
| -> Nested Loop (cost=0.00..20.35 rows=1 width=281) |
| -> Nested Loop (cost=0.00..19.93 rows=1 width=161) |
| -> Nested Loop (cost=0.00..19.63 rows=1 width=165) |
| -> Nested Loop (cost=0.00..19.22 rows=1 width=165) |
| -> Nested Loop (cost=0.00..18.81 rows=1 width=112) |
| Join Filter: (item.i_item_sk = store_sales.ss_item_sk) |
| -> Nested Loop (cost=0.00..18.02 rows=2 width=66) |
| -> Seq Scan on item (cost=0.00..13.71 rows=1 width=58) |
| Filter: ((i_current_price >= 35::numeric) AND (i_current_price <= 45::numeric) AND (i_current_price >= 36::numeric) AND (i_current_price <= 50::numeric) AND (i_color = ANY ('{maroon,burnished,dim,steel,navajo,chocolate}'::bpchar[])))|
| -> Index Only Scan using store_returns_pkey on store_returns (cost=0.00..4.29 rows=2 width=8) |
| Index Cond: (sr_item_sk = item.i_item_sk) |
| -> Index Scan using store_sales_pkey on store_sales (cost=0.00..0.38 rows=1 width=62) |
| Index Cond: ((ss_item_sk = store_returns.sr_item_sk) AND (ss_ticket_number = store_returns.sr_ticket_number)) |
| -> Index Scan using store_pkey on store (cost=0.00..0.40 rows=1 width=61) |
| Index Cond: (s_store_sk = store_sales.ss_store_sk) |
| -> Index Scan using customer_pkey on customer (cost=0.00..0.40 rows=1 width=8) |
| Index Cond: (c_customer_sk = store_sales.ss_customer_sk) |
| -> Index Only Scan using promotion_pkey on promotion (cost=0.00..0.28 rows=1 width=4) |
| Index Cond: (p_promo_sk = store_sales.ss_promo_sk) |
| -> Index Scan using customer_address_pkey on customer_address ad2 (cost=0.00..0.42 rows=1 width=128) |
| Index Cond: (ca_address_sk = customer.c_current_addr_sk) |
Join 顺序的 Hints
使用 Hint 指定 Join 的顺序，包括内外表顺序。
语法：
- 仅指定 Join 顺序，不指定内外表顺序。
leading(join_table_list)
- 同时指定 Join 顺序和内外表顺序，内外表顺序仅在最外层生效。
leading((join_table_list))
说明：
join_table_list：为表示表 Join 顺序的 Hint 字符串，可以包含当前层的任意个表（别名），或对于子查询提升的场景，也可以包含子查询的 Hint 别名，同时任意表可以使用括号指定优先级，表之间使用空格分隔。
注意：
- 表只能用单个字符串表示，不能带 Schema。
- 表如果存在别名，需要优先使用别名来表示该表。
join_table_list 中指定的表需要满足以下要求，否则会报语义错误。
- 表必须在当前层或提升的子查询中存在。
- 表在当前层或提升的子查询中必须是唯一的。如果不唯一，需要使用不同的别名进行区分。
- 同一个表只能出现一次。
- 如果表存在别名，则需要使用别名。
例如：
leading(t1 t2 t3 t4 t5)表示：t1、t2、t3、t4、t5先 Join，五表 Join 顺序及内外表不限。leading((t1 t2 t3 t4 t5))表示：t1和t2先 Join，t2做内表；再和t3Join，t3做内表；再和t4Join，t4做内表；再和t5Join，t5做内表。leading(t1 (t2 t3 t4) t5)表示：t2、t3、t4先 Join，内外表不限；再和t1、t5Join，内外表不限。leading((t1 (t2 t3 t4) t5))表示：t2、t3、t4先 Join，内外表不限；在最外层，t1再和t2、t3、t4的 Join 表 Join，t1为外表；再和t5Join，t5为内表。leading((t1 (t2 t3) t4 t5)) leading((t3 t2))表示：t2、t3先 Join，t2做内表；然后再和t1Join，t2、t3的 Join 表做内表；然后再依次跟t4、t5做 Join，t4、t5做内表。
例子：使用 Hint 指定 Join 顺序
EXPLAIN
SELECT /*+ leading((((((store_sales store) promotion) item) customer) ad2) store_returns) leading((store store_sales))*/
i_product_name product_name,
i_item_sk item_sk,
s_store_name store_name,
s_zip store_zip,
ad2.ca_street_number c_street_number,
ad2.ca_street_name c_street_name,
ad2.ca_city c_city,
ad2.ca_zip c_zip,
count(*) cnt,
sum(ss_wholesale_cost) s1,
sum(ss_list_price) s2,
sum(ss_coupon_amt) s3
FROM store_sales,
store_returns,
store,
customer,
promotion,
customer_address ad2,
item
WHERE ss_store_sk = s_store_sk
AND ss_customer_sk = c_customer_sk
AND ss_item_sk = i_item_sk
and ss_item_sk = sr_item_sk
and ss_ticket_number = sr_ticket_number
and c_current_addr_sk = ad2.ca_address_sk
and ss_promo_sk = p_promo_sk
and i_color in ('maroon', 'burnished', 'dim', 'steel', 'navajo', 'chocolate')
and i_current_price between 35 and 35 + 10
and i_current_price between 35 + 1 and 35 + 15
group by i_product_name,
i_item_sk,
s_store_name,
s_zip,
ad2.ca_street_number,
ad2.ca_street_name,
ad2.ca_city,
ad2.ca_zip;
WARNING: Duplicated or conflict hint: Leading(store_sales store), will be discarded.
|QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|HashAggregate (cost=60.94..60.95 rows=1 width=385) |
| Group By Key: item.i_product_name, item.i_item_sk, store.s_store_name, store.s_zip, ad2.ca_street_number, ad2.ca_street_name, ad2.ca_city, ad2.ca_zip |
| -> Nested Loop (cost=47.48..60.91 rows=1 width=281) |
| -> Nested Loop (cost=47.48..60.62 rows=1 width=289) |
| -> Nested Loop (cost=47.48..60.19 rows=1 width=169) |
| -> Hash Join (cost=47.48..59.78 rows=1 width=169) |
| Hash Cond: (store_sales.ss_item_sk = item.i_item_sk) |
| -> Hash Join (cost=33.76..45.55 rows=196 width=111) |
| Hash Cond: (promotion.p_promo_sk = store_sales.ss_promo_sk) |
| -> Index Only Scan using promotion_pkey on promotion (cost=0.00..8.72 rows=298 width=4) |
| -> Hash (cost=31.30..31.30 rows=196 width=115) |
| -> Hash Join (cost=17.61..31.30 rows=196 width=115) |
| Hash Cond: (store.s_store_sk = store_sales.ss_store_sk) |
| -> Seq Scan on store (cost=0.00..11.16 rows=116 width=61) |
| -> Hash (cost=13.38..13.38 rows=338 width=62) |
| -> Seq Scan on store_sales (cost=0.00..13.38 rows=338 width=62) |
| -> Hash (cost=13.71..13.71 rows=1 width=58) |
| -> Seq Scan on item (cost=0.00..13.71 rows=1 width=58) |
| Filter: ((i_current_price >= 35::numeric) AND (i_current_price <= 45::numeric) AND (i_current_price >= 36::numeric) AND (i_current_price <= 50::numeric) AND (i_color = ANY ('{maroon,burnished,dim,steel,navajo,chocolate}'::bpchar[])))|
| -> Index Scan using customer_pkey on customer (cost=0.00..0.40 rows=1 width=8) |
| Index Cond: (c_customer_sk = store_sales.ss_customer_sk) |
| -> Index Scan using customer_address_pkey on customer_address ad2 (cost=0.00..0.42 rows=1 width=128) |
| Index Cond: (ca_address_sk = customer.c_current_addr_sk) |
| -> Index Only Scan using store_returns_pkey on store_returns (cost=0.00..0.29 rows=1 width=8) |
| Index Cond: ((sr_item_sk = store_sales.ss_item_sk) AND (sr_ticket_number = store_sales.ss_ticket_number)) |
该 Hint 表示表之间的 Join关系是：store_sales 和 store 先 Join，store_sales 做内表，然后依次跟 promotion，item，customer，ad2，store_returns 做 Join。
Join 方式的 Hints
使用 Hint 指定 Join 使用的方法，可以为 Nested Loop、Hash Join 和 Merge Join。
语法：
[no] nestloop|hashjoin|mergejoin(table_list)
说明：
no：表示不适应 Hint 的 Join 方式。table_list：表示 Hint 表集合的字符串，该字符串中的表与前面的join_table_list相同，只是中间不允许出现括号来指定 Join 的优先级。
例如：
no nestloop(t1 t2 t3) 表示：生成 t1、t2、t3 三表连接计划时，不使用 Nested Loop。三表连接计划可能是 t2 和 t3 先 Join，再跟 t1 Join，或 t1 和 t2 先 Join，再跟 t3 Join。此 Hint 只 Hint 最后一次 Join 的 Join 方式，如果需要，可以对其他 Join 进行 Hint，例如：可以增加 Hint：no nestloop(t2 t3)，以便让 t2 和 t3 先 Join，且不允许 Nested Loop 连接。
例子：使用 Hint 指定 Join 方式
EXPLAIN
SELECT /*+ nestloop(store_sales store_returns item) */
i_product_name product_name,
i_item_sk item_sk,
s_store_name store_name,
s_zip store_zip,
ad2.ca_street_number c_street_number,
ad2.ca_street_name c_street_name,
ad2.ca_city c_city,
ad2.ca_zip c_zip,
count(*) cnt,
sum(ss_wholesale_cost) s1,
sum(ss_list_price) s2,
sum(ss_coupon_amt) s3
FROM store_sales,
store_returns,
store,
customer,
promotion,
customer_address ad2,
item
WHERE ss_store_sk = s_store_sk
AND ss_customer_sk = c_customer_sk
AND ss_item_sk = i_item_sk
and ss_item_sk = sr_item_sk
and ss_ticket_number = sr_ticket_number
and c_current_addr_sk = ad2.ca_address_sk
and ss_promo_sk = p_promo_sk
and i_color in ('maroon', 'burnished', 'dim', 'steel', 'navajo', 'chocolate')
and i_current_price between 35 and 35 + 10
and i_current_price between 35 + 1 and 35 + 15
group by i_product_name,
i_item_sk,
s_store_name,
s_zip,
ad2.ca_street_number,
ad2.ca_street_name,
ad2.ca_city,
ad2.ca_zip;
|QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|HashAggregate (cost=20.38..20.39 rows=1 width=385) |
| Group By Key: item.i_product_name, item.i_item_sk, store.s_store_name, store.s_zip, ad2.ca_street_number, ad2.ca_street_name, ad2.ca_city, ad2.ca_zip |
| -> Nested Loop (cost=0.00..20.35 rows=1 width=281) |
| -> Nested Loop (cost=0.00..19.93 rows=1 width=161) |
| -> Nested Loop (cost=0.00..19.63 rows=1 width=165) |
| -> Nested Loop (cost=0.00..19.22 rows=1 width=165) |
| -> Nested Loop (cost=0.00..18.81 rows=1 width=112) |
| Join Filter: (item.i_item_sk = store_sales.ss_item_sk) |
| -> Nested Loop (cost=0.00..18.02 rows=2 width=66) |
| -> Seq Scan on item (cost=0.00..13.71 rows=1 width=58) |
| Filter: ((i_current_price >= 35::numeric) AND (i_current_price <= 45::numeric) AND (i_current_price >= 36::numeric) AND (i_current_price <= 50::numeric) AND (i_color = ANY ('{maroon,burnished,dim,steel,navajo,chocolate}'::bpchar[])))|
| -> Index Only Scan using store_returns_pkey on store_returns (cost=0.00..4.29 rows=2 width=8) |
| Index Cond: (sr_item_sk = item.i_item_sk) |
| -> Index Scan using store_sales_pkey on store_sales (cost=0.00..0.38 rows=1 width=62) |
| Index Cond: ((ss_item_sk = store_returns.sr_item_sk) AND (ss_ticket_number = store_returns.sr_ticket_number)) |
| -> Index Scan using store_pkey on store (cost=0.00..0.40 rows=1 width=61) |
| Index Cond: (s_store_sk = store_sales.ss_store_sk) |
| -> Index Scan using customer_pkey on customer (cost=0.00..0.40 rows=1 width=8) |
| Index Cond: (c_customer_sk = store_sales.ss_customer_sk) |
| -> Index Only Scan using promotion_pkey on promotion (cost=0.00..0.28 rows=1 width=4) |
| Index Cond: (p_promo_sk = store_sales.ss_promo_sk) |
| -> Index Scan using customer_address_pkey on customer_address ad2 (cost=0.00..0.42 rows=1 width=128) |
| Index Cond: (ca_address_sk = customer.c_current_addr_sk) |
该 Hint表示生成 store_sales、store_returns 和 item 三表的结果集时，最后的两表关联使用 Nested Loop。
行数的 Hints
使用 Hint 指定中间结果集的大小，支持绝对值和相对值。
语法：
rows(table_list #|+|-|* const)
说明：
#、+、-、*：进行行数估算 Hint 的四种操作符号。#表示直接使用后面的行数进行 Hint。+、-、*表示对原来估算的行数进行加、减、乘操作，运算后的行数最小值为 1 行。table_list为 Hint 对应的单表或多表 Join 结果集，与前面的join_table_list相同。const：可以是任意非负数，支持科学计数法。
例如：
rows(t1 #5)表示指定t1表的结果集为 5 行。rows(t1 t2 t3 *1000)表示指定t1、t2、t3表 Join 完的结果集的行数乘以 1000。
建议：
- 推荐使用两个表的 Hint。对于两个表的采用
*操作符的 Hint，只要两个表出现在 Join 的两端，都会触发 Hint。例如：设置 Hint 为rows(t1 t2 * 3)，对于(t1 t3 t4)和(t2 t5 t6)Join 时，由于t1和t2出现在 Join 的两端，所以其 Join 的结果集也会应用该 Hint 规则乘以 3。 - 行数的 Hint 可用在单表、多表、Function Table 及 Subquery Scan Table 这些表的结果集上。
例子：使用 Hint 指定行数
EXPLAIN
SELECT /*+ rows(store_sales store_returns *50) */
i_product_name product_name,
i_item_sk item_sk,
s_store_name store_name,
s_zip store_zip,
ad2.ca_street_number c_street_number,
ad2.ca_street_name c_street_name,
ad2.ca_city c_city,
ad2.ca_zip c_zip,
count(*) cnt,
sum(ss_wholesale_cost) s1,
sum(ss_list_price) s2,
sum(ss_coupon_amt) s3
FROM store_sales,
store_returns,
store,
customer,
promotion,
customer_address ad2,
item
WHERE ss_store_sk = s_store_sk
AND ss_customer_sk = c_customer_sk
AND ss_item_sk = i_item_sk
and ss_item_sk = sr_item_sk
and ss_ticket_number = sr_ticket_number
and c_current_addr_sk = ad2.ca_address_sk
and ss_promo_sk = p_promo_sk
and i_color in ('maroon', 'burnished', 'dim', 'steel', 'navajo', 'chocolate')
and i_current_price between 35 and 35 + 10
and i_current_price between 35 + 1 and 35 + 15
group by i_product_name,
i_item_sk,
s_store_name,
s_zip,
ad2.ca_street_number,
ad2.ca_street_name,
ad2.ca_city,
ad2.ca_zip;
|QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|HashAggregate (cost=20.38..20.39 rows=1 width=385) |
| Group By Key: item.i_product_name, item.i_item_sk, store.s_store_name, store.s_zip, ad2.ca_street_number, ad2.ca_street_name, ad2.ca_city, ad2.ca_zip |
| -> Nested Loop (cost=0.00..20.35 rows=1 width=281) |
| -> Nested Loop (cost=0.00..19.93 rows=1 width=161) |
| -> Nested Loop (cost=0.00..19.63 rows=1 width=165) |
| -> Nested Loop (cost=0.00..19.22 rows=1 width=165) |
| -> Nested Loop (cost=0.00..18.81 rows=1 width=112) |
| Join Filter: (item.i_item_sk = store_sales.ss_item_sk) |
| -> Nested Loop (cost=0.00..18.02 rows=2 width=66) |
| -> Seq Scan on item (cost=0.00..13.71 rows=1 width=58) |
| Filter: ((i_current_price >= 35::numeric) AND (i_current_price <= 45::numeric) AND (i_current_price >= 36::numeric) AND (i_current_price <= 50::numeric) AND (i_color = ANY ('{maroon,burnished,dim,steel,navajo,chocolate}'::bpchar[])))|
| -> Index Only Scan using store_returns_pkey on store_returns (cost=0.00..4.29 rows=2 width=8) |
| Index Cond: (sr_item_sk = item.i_item_sk) |
| -> Index Scan using store_sales_pkey on store_sales (cost=0.00..0.38 rows=1 width=62) |
| Index Cond: ((ss_item_sk = store_returns.sr_item_sk) AND (ss_ticket_number = store_returns.sr_ticket_number)) |
| -> Index Scan using store_pkey on store (cost=0.00..0.40 rows=1 width=61) |
| Index Cond: (s_store_sk = store_sales.ss_store_sk) |
| -> Index Scan using customer_pkey on customer (cost=0.00..0.40 rows=1 width=8) |
| Index Cond: (c_customer_sk = store_sales.ss_customer_sk) |
| -> Index Only Scan using promotion_pkey on promotion (cost=0.00..0.28 rows=1 width=4) |
| Index Cond: (p_promo_sk = store_sales.ss_promo_sk) |
| -> Index Scan using customer_address_pkey on customer_address ad2 (cost=0.00..0.42 rows=1 width=128) |
| Index Cond: (ca_address_sk = customer.c_current_addr_sk) |
该 Hint 表示 store_sales、store_returns 关联的结果集估算行数在原估算行数基础上乘以50。
Scan 方式的 Hints
使用 Hint 指定 Scan 使用的方法，可以是 tablescan、indexscan 和 indexonlyscan。
语法：
[no] tablescan|indexscan|indexonlyscan(table [index])
说明：
no：表示不使用 Hint 的 Scan 方式。table：表示 Hint 指定的表，只能指定一个表，如果表存在别名应优先使用别名。index：表示使用indexscan或indexonlyscan的 Hint 时，指定的索引名称，当前只能指定一个。
注意：
- 对于
indexscan或indexonlyscan，只有 Hint 的索引属于 Hint 的表时，才能使用该 Hint。 - Scan Hint 支持在行列存表、OBS 表、子查询表上指定。
例子：使用 Hint 指定查询使用索引扫描
先创建索引：
stone@postgres=> create index i on item(i_item_sk);
CREATE INDEX
然后使用 Hint：
EXPLAIN
SELECT /*+ indexscan(item i) */
i_product_name product_name,
i_item_sk item_sk,
s_store_name store_name,
s_zip store_zip,
ad2.ca_street_number c_street_number,
ad2.ca_street_name c_street_name,
ad2.ca_city c_city,
ad2.ca_zip c_zip,
count(*) cnt,
sum(ss_wholesale_cost) s1,
sum(ss_list_price) s2,
sum(ss_coupon_amt) s3
FROM store_sales,
store_returns,
store,
customer,
promotion,
customer_address ad2,
item
WHERE ss_store_sk = s_store_sk
AND ss_customer_sk = c_customer_sk
AND ss_item_sk = i_item_sk
and ss_item_sk = sr_item_sk
and ss_ticket_number = sr_ticket_number
and c_current_addr_sk = ad2.ca_address_sk
and ss_promo_sk = p_promo_sk
and i_color in ('maroon', 'burnished', 'dim', 'steel', 'navajo', 'chocolate')
and i_current_price between 35 and 35 + 10
and i_current_price between 35 + 1 and 35 + 15
group by i_product_name,
i_item_sk,
s_store_name,
s_zip,
ad2.ca_street_number,
ad2.ca_street_name,
ad2.ca_city,
ad2.ca_zip;
|QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|HashAggregate (cost=34.70..34.71 rows=1 width=385) |
| Group By Key: item.i_product_name, item.i_item_sk, store.s_store_name, store.s_zip, ad2.ca_street_number, ad2.ca_street_name, ad2.ca_city, ad2.ca_zip |
| -> Nested Loop (cost=18.45..34.67 rows=1 width=281) |
| -> Nested Loop (cost=18.45..34.25 rows=1 width=161) |
| -> Nested Loop (cost=18.45..33.95 rows=1 width=165) |
| -> Nested Loop (cost=18.45..33.54 rows=1 width=165) |
| -> Nested Loop (cost=18.45..33.13 rows=1 width=112) |
| Join Filter: (store_sales.ss_item_sk = item.i_item_sk) |
| -> Hash Join (cost=18.45..31.91 rows=3 width=62) |
| Hash Cond: ((store_returns.sr_item_sk = store_sales.ss_item_sk) AND (store_returns.sr_ticket_number = store_sales.ss_ticket_number)) |
| -> Index Only Scan using store_returns_pkey on store_returns (cost=0.00..10.37 rows=408 width=8) |
| -> Hash (cost=13.38..13.38 rows=338 width=62) |
| -> Seq Scan on store_sales (cost=0.00..13.38 rows=338 width=62) |
| -> Index Scan using i on item (cost=0.00..0.40 rows=1 width=58) |
| Index Cond: (i_item_sk = store_returns.sr_item_sk) |
| Filter: ((i_current_price >= 35::numeric) AND (i_current_price <= 45::numeric) AND (i_current_price >= 36::numeric) AND (i_current_price <= 50::numeric) AND (i_color = ANY ('{maroon,burnished,dim,steel,navajo,chocolate}'::bpchar[])))|
| -> Index Scan using store_pkey on store (cost=0.00..0.40 rows=1 width=61) |
| Index Cond: (s_store_sk = store_sales.ss_store_sk) |
| -> Index Scan using customer_pkey on customer (cost=0.00..0.40 rows=1 width=8) |
| Index Cond: (c_customer_sk = store_sales.ss_customer_sk) |
| -> Index Only Scan using promotion_pkey on promotion (cost=0.00..0.28 rows=1 width=4) |
| Index Cond: (p_promo_sk = store_sales.ss_promo_sk) |
| -> Index Scan using customer_address_pkey on customer_address ad2 (cost=0.00..0.42 rows=1 width=128) |
| Index Cond: (ca_address_sk = customer.c_current_addr_sk) |
索引的 Hints
使用 Hint 指定在扫描表时期望使用的索引名称。
语法：
tbl_name [ partition_clause ] [ [ AS ] alias ] [ index_hint_list ]
index_hint_list:
index_hint [ index_hint ]
index_hint:
USE { INDEX | KEY } ( [ index_list ] )
| { FORCE | IGNORE } { INDEX | KEY } ( index_list )
index_list:
index_name [ , index_name ] ...
说明：
index_list：索引的名称，使用逗号分隔。当加载 Dolpin 插件时，此处可以使用 PRIMARY 关键字，表示使用这个表的主键索引。tbl_name：泛指一个表名。
注意：
- 当使用
USE INDEX指定索引时，会考虑扫描索引的成本和顺序扫描的成本。会使用成本较低的索引。 - 如果通过
FORCE INDEX指定索引，则会强制使用该索引进行扫描。 FORCE INDEX和USE INDEX不能在同一时间用于同一张表。- 使用多个索引 Hint 等同于在索引列表中写入多个索引名称。
IGNORE INDEX所指定要忽略的索引的级别是更高的，意味着当IGNORE INDEX指定了某个索引要被忽略时，不会考虑是否有其他 Hint 指定要使用这个索引。
例子：使用 Hint 指定索引
openGauss=# explain (costs off,verbose true )select * from db_1097149_tb force key (index_1097149_4) where col2= 3 and col4 = 'a';
QUERY PLAN
----------------------------------------------------------
Index Scan using index_1097149_4 on public.db_1097149_tb
Output: col1, col2, col3, col4
Index Cond: ((db_1097149_tb.col4)::text = 'a'::text)
Filter: (db_1097149_tb.col2 = 3)
(4 rows)
openGauss=# explain (costs off) select * from db_1130449_tb IGNORE INDEX (index_1130449) where col2= 3;
QUERY PLAN
---------------------------
Seq Scan on db_1130449_tb
Filter: (col2 = 3)
(2 rows)
-- PRIMARY示例
openGauss=# explain (costs off) select a from t1 force index(primary) where a > 2 order by a;
QUERY PLAN
-------------------------------------
[Bypass]
Index Only Scan using t1_pkey on t1
Index Cond: (a > 2)
(3 rows)
子链接块名的 Hints
使用 Hint 指定子链接块的名称。
语法：
blockname (table)
说明：
table：为该子链接块指定的名称。
注意：
- 该 Hint 仅在对应的子链接块没有提升时才会被上层查询使用。目前支持的子链接提升包括 IN 子链接提升、EXISTS 子链接提升和包含 Agg 等值相关子链接提升。该 Hint 通常会和前面章节提到的 Hint 联合使用。
- 对于 FROM 关键字后的子查询，则需要使用子查询的别名进行 Hint，此时该 Hint 无效。
- 如果子链接中含有多个表，则提升后这些表可与外层表以任意优化顺序连接，此时该 Hint 也无效。
例子：使用 Hint 指定子链接块的名称
explain
select /*+ nestloop(store_sales tt) */
*
from store_sales
where ss_item_sk in (select /*+ blockname(tt) */
i_item_sk
from item
group by 1);
|QUERY PLAN |
|------------------------------------------------------------------------------------------|
|Nested Loop (cost=0.00..93.81 rows=169 width=212) |
| -> Group (cost=0.00..6.61 rows=135 width=4) |
| Group By Key: item.i_item_sk |
| -> Index Only Scan using i on item (cost=0.00..6.28 rows=135 width=4) |
| -> Index Scan using store_sales_pkey on store_sales (cost=0.00..0.62 rows=2 width=212)|
| Index Cond: (ss_item_sk = item.i_item_sk) |
GUC 参数的 Hints
使用 Hint 指定查询优化相关 GUC 参数。
语法：
set(param value)
说明：
param：表示参数名。value：表示参数的取值。
目前支持使用 Hint 设置的参数有：
布尔类：
enable_bitmapscanenable_hashaggenable_hashjoinenable_indexscanenable_indexonlyscanenable_materialenable_mergejoinenable_nestloopenable_index_nestloopenable_seqscanenable_sortenable_incremental_sortenable_tidscanpartition_iterator_eliminationpartition_page_estimationenable_functional_dependencyvar_eq_const_selectivityenable_inner_unique_opt
整形类
query_dop
浮点类
cost_weight_indexdefault_limit_rowsseq_page_costrandom_page_costcpu_tuple_costcpu_index_tuple_costcpu_operator_costeffective_cache_size
枚举类型
try_vector_engine_strategy
注意：
- 设置不在白名单中的参数，参数取值不合法，或 Hint 语法错误时，不会影响查询执行的正确性。使用
explain(verbose on)可以看到 Hint 解析错误的报错提示。 - GUC 参数的 Hint 只在最外层查询生效，子查询内的 GUC 参数 Hint 不生效。
- 视图定义内的 GUC 参数 Hint 不生效。
CREATE TABLE … AS …查询最外层的 GUC 参数 Hint 可以生效。
选择 Custom Plan 或 Generic Plan 的 Hint
对于以 PBE（Parse Bind Execute，由参数 enable_pbe_optimization 设置，默认为 on） 方式执行的查询语句和 DML 语句，优化器会基于规则、成本、参数等因素生成 Custom Plan 或 Generic Plan 执行计划。用户可以使用 use_cplan 或 use_gplan Hint 来指定使用哪种计划执行。
语法：
- 指定使用 Custom Plan：
use_cplan
- 指定使用 Generic Plan：
use_gplan
说明：
- 对于非 PBE 方式执行的 SQL 语句，设置本 Hint 不会影响执行方式。
- 本 Hint 的优先级仅高于基于成本的选择和
plan_cache_mode参数，即plan_cache_mode无法强制选择执行方式的语句,本 Hint 也无法生效。
例子：强制使用 Custom Plan
stone@postgres=> create table t (a int, b int, c int);
CREATE TABLE
stone@postgres=> prepare p as select /*+ use_cplan */ * from t where a = $1;
PREPARE
stone@postgres=> explain execute p(1);
QUERY PLAN
----------------------------------------------------
Seq Scan on t (cost=0.00..34.31 rows=10 width=12)
Filter: (a = 1)
(2 rows)
可以看到过滤条件为入参的实际值，即此计划为 Custom Plan。
例子：强制使用 Generic Plan
stone@postgres=> deallocate p;
DEALLOCATE
stone@postgres=> prepare p as select /*+ use_gplan */ * from t where a = $1;
PREPARE
stone@postgres=> explain execute p(1);
QUERY PLAN
----------------------------------------------------
Seq Scan on t (cost=0.00..34.31 rows=10 width=12)
Filter: (a = $1)
(2 rows)
可以看到过滤条件为待填充的入参，即此计划为 Generic Plan。
指定子查询不展开的 Hint
在数据库优化查询逻辑时，部分子查询可通过提升至上层以避免嵌套执行，但对于选择率低且能利用索引过滤访问页的子查询，嵌套执行不会显著降低性能，而强行提升反而可能因扩大查询范围导致性能恶化。此时可通过 no_expand Hint 进行调试，但多数情况下不建议使用该Hint。
语法：
no_expand
例子：使用 Hint 指定子查询不展开
正常查询的执行计划：
stone@postgres=> CREATE TABLE t1(a int, b varchar(10));
CREATE TABLE
stone@postgres=> CREATE TABLE t2(a int, b varchar(10));
CREATE TABLE
stone@postgres=> explain select * from t1 where t1.a in (select t2.a from t2);
QUERY PLAN
------------------------------------------------------------------------
Hash Join (cost=35.77..84.07 rows=851 width=18)
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1 (cost=0.00..27.02 rows=1702 width=18)
-> Hash (cost=33.27..33.27 rows=200 width=4)
-> HashAggregate (cost=31.27..33.27 rows=200 width=4)
Group By Key: t2.a
-> Seq Scan on t2 (cost=0.00..27.02 rows=1702 width=4)
(7 rows)
加入 no_expand Hint 后的执行计划：
stone@postgres=> explain select * from t1 where t1.a in (select /*+ no_expand*/ t2.a from t2);
QUERY PLAN
--------------------------------------------------------------
Seq Scan on t1 (cost=31.27..62.55 rows=851 width=18)
Filter: (hashed SubPlan 1)
SubPlan 1
-> Seq Scan on t2 (cost=0.00..27.02 rows=1702 width=4)
(4 rows)
指定不使用全局执行计划缓存的 Hint
全局执行计划缓存打开时，可以通过 no_gpc Hint 来强制单个查询语句不在全局共享执行计划缓存，只保留会话生命周期的执行计划缓存。
语法：
no_gpc
说明：
- 本 Hint 仅在
enable_global_plancache=on（默认为off）时对 PBE 执行的语句生效。
Hint 的错误、冲突及告警
Hint 中的错误不会影响语句的执行，只是不能生效，该错误会根据语句类型以不同方式提示用户。对于 EXPLAIN 语句，Hint 的错误会以 WARNING 形式显示在界面上，对于非 EXPLAIN 语句，会以 DEBUG 1 级别日志显示在日志中，关键字为 PLANHINT。
如果存在 Hint 重复或冲突，只有第一个 Hint 生效，其它 Hint 均会失效，并给出提示。
Hint 不会被使用的情况：
- 非等值 Join 使用
hashjoinHint 或mergejoinHint。 - 不包含索引的表使用
indexscanHint 或indexonlyscanHint。 - 通常只有在索引列上使用过滤条件才会生成相应的索引路径，全表扫描将不会使用索引，因此使用
indexscanHint 或indexonlyscanHint 将不会使用。 indexonlyscan只有输出列仅包含索引列才会使用，否则指定时 Hint 不会被使用。- 多个表存在等值连接时，仅尝试有等值连接条件的表的连接，此时没有关联条件的表之间的路径将不会生成，所以指定相应的
leading、join、rowsHint 将不使用，例如：t1，t2，t3表 Join，t1和t2，t2和t3有等值连接条件，则t1和t3不会优先连接，leading (t1 t3)不会被使用。 - 如果子链接未被提升，则
blocknameHint 不会被使用。
慢 SQL 诊断
慢 SQL 诊断提供诊断慢 SQL 所需要的必要信息，帮助开发者回溯执行时间超过阈值的 SQL，诊断 SQL 性能瓶颈。
慢 SQL 能根据用户提供的执行时间阈值（参数 log_min_duration_statement，默认 30 分钟），记录所有超过阈值的执行完毕的 SQL 信息。
omm@postgres=# SELECT * FROM pg_settings WHERE NAME='log_min_duration_statement';
-[ RECORD 1 ]----------------------------------------------------------------------
name | log_min_duration_statement
setting | 1800000
unit | ms
category | Reporting and Logging / When to Log
short_desc | Sets the minimum execution time above which statements will be logged.
extra_desc | Zero prints all queries. -1 turns this feature off.
context | superuser
vartype | integer
source | configuration file
min_val | -1
max_val | 2147483647
enumvals |
boot_val | -1
reset_val | 1800000
sourcefile | /opt/huawei/install/data/dn/postgresql.conf
sourceline | 480
在主机中，慢 SQL 提供表和函数两种维度的查询接口。用户从接口中能查询到作业的执行计划，开始、结束执行时间，执行查询的语句，行活动，内核时间，CPU 时间，执行时间，解析时间，编译时间，查询重写时间，计划生成时间，网络时间，IO 时间，网络开销，锁开销，等待事件等。以上所有信息都是脱敏的。
在备机中，慢 SQL 提供一个专用的函数作为查询接口。由于备机无法往表（statement_history）中写入性能诊断数据，因此备机使用了新的方式来记录数据，并通过函数接口查询，用户在接口中查询到的各类信息，与主机的表（statement_history）完全一致。
查询 SQL 语句信息，需要合理设置 GUC 参数 track_stmt_stat_level。track_stmt_stat_level 参数控制语句执行跟踪的级别，第一部分控制全量 SQL，第二部分控制慢 SQL。对于慢 SQL，当 track_stmt_stat_level 的第二部分值为非 OFF 时，且 SQL 执行时间超过 log_min_duration_statement，会记录为慢 SQL。默认值为 OFF,L0，表示仅记录慢 SQL。建议设置为 L0,L0。
omm@postgres=# SELECT * FROM pg_settings WHERE NAME='track_stmt_stat_level';
-[ RECORD 1 ]----------------------------------------------------------
name | track_stmt_stat_level
setting | OFF,L0
unit |
category | Column Encryption
short_desc | specify which level statement's statistics to be gathered.
extra_desc |
context | user
vartype | string
source | default
min_val |
max_val |
enumvals |
boot_val | OFF,L0
reset_val | OFF,L0
sourcefile |
sourceline |
- 主机查看 SQL 语句执行信息的语法：
select * from dbe_perf.get_global_full_sql_by_timestamp(start_timestamp, end_timestamp);
例如：
omm@postgres=# select * from DBE_PERF.get_global_full_sql_by_timestamp('2025-04-18 10:00:00', '2025-04-18 12:00:00');
- 主机查看慢 SQL 语句执行信息的语法：
select * from dbe_perf.get_global_slow_sql_by_timestamp(start_timestamp, end_timestamp);
例如：
omm@postgres=# select * from DBE_PERF.get_global_full_sql_by_timestamp('2025-04-18 10:00:00', '2025-04-18 12:00:00');
- 主机查看当前节点 SQL 语句执行信息的语法：
select * from statement_history;
例如：
omm@postgres=# select * from statement_history;
- 备机查看当前节点 SQ L语句执行信息的语法：
select * from dbe_perf.standby_statement_history(only_slow, start_time, end_time);
例如：
omm@postgres=# select * from dbe_perf.standby_statement_history(true, '2025-04-18 10:00:00', '2025-04-18 12:00:00');
使用限制：
- 目前的 SQL 跟踪信息，基于正常的执行逻辑。执行失败的 SQL，其跟踪信息不具有准确的参考价值。
- 节点重启，可能导致该节点的数据丢失。
- SQL 语句执行完立即退出会话，可能会丢失该会话未刷新到系统表中的数据。
- 通过 GUC 参数设置收集 SQL 语句的数量，如果超过阈值，新的 SQL 语句执行信息不会被收集。
- 通过 GUC 参数设置单条 SQL 语句收集的锁事件详细信息的最大字节数，如果超过阈值，新的锁事件详细信息不会被收集。
- 通过异步刷新方式刷新用户执行中的 SQL 信息，所以用户 Query 执行结束后，存在查询相关视图函数结果短暂时延。
- 当
track_stmt_parameter为off时，query字段最大长度受track_activity_query_size控制。 - 部分指标信息（行活动、Cache/IO、时间分布等）依赖于
dbe_perf.statement视图收集，如果该视图对应记录数超过预定大小（依赖 GUC：instr_unique_sql_count），则本特性可能不收集相关指标。 statement_history表相关函数以及视图、备机dbe_perf.standby_statement_history中的details字段为二进制格式，如果需要解析详细内容，请使用对应函数pg_catalog.statement_detail_decode (details, 'plaintext', true)。statement_history表查询需要切换至postgres库，其他库中数据为空。- 备机
dbe_perf.standby_statement_history函数查询需要切换至postgres库，其他库中查询会提示不可用。 - 备机通过参数
track_stmt_standby_chain_size进行记录数据所占用内存、磁盘空间的大小限制。 statement_history表以及备机dbe_perf.standby_statement_history函数的内容受track_stmt_stat_level控制，，默认为OFF,L0，参数第一部分代表 Full SQL，第二部分是慢 SQL；对于慢 SQL，只有 SQL 运行时间超过log_min_duration_statement时才会被记录。
SQL PATCH
SQL PATCH 能够在避免直接修改用户业务语句的前提下对查询执行的方式做一定调整。在发现查询语句的执行计划、执行方式未达预期的场景下，可以通过创建查询补丁的方式，使用 Hint 对查询计划进行调优或对特定的语句进行报错短路处理。
SQL PATCH 主要设计给 DBA、运维人员及其他需要对 SQL 进行调优的角色使用，用户通过其他运维视图或定位手段识别到业务语句存在执行计划不优导致的性能问题时，可以通过创建 SQL PATCH 对业务语句进行基于 Hint 的调优。目前支持行数、扫描方式、连接方式、连接顺序、PBE Custom/Generic 计划选择、语句级参数设置、参数化路径的 Hint。此外，对于部分由特定语句触发系统内部问题导致系统可服务性受损的语句，在不对业务语句变更的情况下，也可以通过创建用于单点规避的 SQL PATCH，对问题场景提前报错处理，避免更大的损失。
SQL PATCH 的实现当前基于 Unique SQL ID，所以需要打开相关的运维参数才可以生效，Unique SQL ID 在 WDR 报告和慢 SQL 视图中都可以获取到，在创建 SQL PATCH 时需要指定 Unique SQL ID。下面给出简单的使用样例。
例子：使用 SQL PATCH 对特定语句进行 Hint 调优
-- 切换到 stone 用户
omm@postgres=# \c - stone
-- 创建表
stone@postgres=> CREATE TABLE hint_t1 (a int, b int, c int);
CREATE TABLE
-- 创建索引
stone@postgres=> CREATE UNIQUE INDEX hint_t1_idx ON hint_t1(a);
CREATE INDEX
-- 创建函数，用于为表生成数据
stone@postgres=> CREATE OR REPLACE FUNCTION batch_insert()
RETURNS int AS $$
DECLARE
i INT;
start INT;
row_count INT := 1000;
BEGIN
SELECT COUNT(*) INTO start FROM hint_t1;
FOR i IN SELECT generate_series(1, row_count) LOOP
INSERT INTO hint_t1 VALUES (start + i, start + i, start + i);
END LOOP;
RETURN row_count;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
-- 执行函数，为表插入 1000 条数据
stone@postgres=> select batch_insert();
batch_insert
--------------
1000
(1 row)
-- 切换到 omm 用户
stone@postgres=> \c - omm
-- 使用 omm 用户打开 Full SQL 统计信息
omm@postgres=# set track_stmt_stat_level = 'L1,L1';
SET
-- 使用 omm 用户执行查询
omm@postgres=# select * from stone.hint_t1 where a in (10, 20, 30, 40, 50, 60, 70, 80, 90, 100);
a | b | c
-----+-----+-----
10 | 10 | 10
20 | 20 | 20
30 | 30 | 30
40 | 40 | 40
50 | 50 | 50
60 | 60 | 60
70 | 70 | 70
80 | 80 | 80
90 | 90 | 90
100 | 100 | 100
(10 rows)
omm@postgres=# \x
Expanded display is on.
-- 使用 omm 用户查询 SQL 的 ID 及执行计划
omm@postgres=# select unique_query_id, query, query_plan from dbe_perf.statement_history where query like '%hint_t1%';
-[ RECORD 1 ]---+-----------------------------------------------------------------------
unique_query_id | 84571582
query | select * from stone.hint_t1 where a in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
query_plan | Datanode Name: dn_6001
| Seq Scan on hint_t1 (cost=0.00..26.50 rows=10 width=12)
| Filter: (a = ANY ('***'::integer[]))
|
|
omm@postgres=# \x
Expanded display is off.
-- 对指定的 Unique SQL ID 指定 Hint Patch
omm@postgres=# select * from dbe_sql_util.create_hint_sql_patch('patch1', 84571582, 'indexscan(hint_t1)');
create_hint_sql_patch
-----------------------
t
(1 row)
-- 通过 explain 可以确认 Hint 是否生效
omm@postgres=# explain select * from stone.hint_t1 where a in (10, 20, 30, 40, 50, 60, 70, 80, 90, 100);
NOTICE: Plan influenced by SQL hint patch
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using hint_t1_idx on hint_t1 (cost=0.01..30.69 rows=10 width=12)
Index Cond: (a = ANY ('{10,20,30,40,50,60,70,80,90,100}'::integer[]))
(2 rows)
-- 再次执行 SQL
omm@postgres=# select * from stone.hint_t1 where a in (10, 20, 30, 40, 50, 60, 70, 80, 90, 100);
a | b | c
-----+-----+-----
10 | 10 | 10
20 | 20 | 20
30 | 30 | 30
40 | 40 | 40
50 | 50 | 50
60 | 60 | 60
70 | 70 | 70
80 | 80 | 80
90 | 90 | 90
100 | 100 | 100
(10 rows)
omm@postgres=# \x
Expanded display is on.
-- 可以看到新的执行计划已经改变了
omm@postgres=# select unique_query_id, query, query_plan from dbe_perf.statement_history where query like '%hint_t1%';
-[ RECORD 1 ]---+-----------------------------------------------------------------------------
unique_query_id | 84571582
query | select * from stone.hint_t1 where a in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
query_plan | Datanode Name: dn_6001
| Seq Scan on hint_t1 (cost=0.00..26.50 rows=10 width=12)
| Filter: (a = ANY ('***'::integer[]))
|
|
-[ RECORD 2 ]---+-----------------------------------------------------------------------------
unique_query_id | 84571582
query | select * from stone.hint_t1 where a in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
query_plan | Datanode Name: dn_6001
| Index Scan using hint_t1_idx on hint_t1 (cost=0.01..30.69 rows=10 width=12)
| Index Cond: (a = ANY ('***'::integer[]))
|
|
-- 删除该 Hint Patch
omm@postgres=# select * from dbe_sql_util.drop_sql_patch('patch1');
-[ RECORD 1 ]--+--
drop_sql_patch | t
omm@postgres=# \x
Expanded display is off.
-- 查看执行计划，又回到了之前的执行计划
omm@postgres=# explain select * from stone.hint_t1 where a in (10, 20, 30, 40, 50, 60, 70, 80, 90, 100);
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on hint_t1 (cost=0.00..26.50 rows=10 width=12)
Filter: (a = ANY ('{10,20,30,40,50,60,70,80,90,100}'::integer[]))
(2 rows)
例子：使用 SQL PATCH 对特定语句进行提前报错规避
-- 使用 omm 用户执行查询
omm@postgres=# select * from stone.hint_t1 t1 where t1.a = 1;
a | b | c
---+---+---
1 | 1 | 1
(1 row)
omm@postgres=# \x
Expanded display is on.
-- 使用 omm 用户查询 SQL 的 ID 及执行计划
omm@postgres=# select unique_query_id, query, query_plan from dbe_perf.statement_history where query like '%hint_t1%';
-[ RECORD 1 ]---+------------------------------------------------------------------------------
unique_query_id | 84571582
query | select * from stone.hint_t1 where a in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
query_plan | Datanode Name: dn_6001
| Index Scan using hint_t1_idx on hint_t1 (cost=0.01..30.69 rows=10 width=12)
| Index Cond: (a = ANY ('***'::integer[]))
|
|
-[ RECORD 2 ]---+------------------------------------------------------------------------------
unique_query_id | 925015436
query | select * from stone.hint_t1 t1 where t1.a = ?;
query_plan | Datanode Name: dn_6001
| Index Scan using hint_t1_idx on hint_t1 t1 (cost=0.00..8.27 rows=1 width=12)
| Index Cond: (a = '***')
|
|
omm@postgres=# \x
Expanded display is off.
-- 对语句的 Unique SQL ID 创建 Abort Patch
omm@postgres=# select * from dbe_sql_util.create_abort_sql_patch('patch2', 925015436);
create_abort_sql_patch
------------------------
t
(1 row)
-- 再次执行语句会提前报错
omm@postgres=# select * from stone.hint_t1 t1 where t1.a = 1;
ERROR: Statement 925015436 canceled by abort patch patch2
使用限制：
- 仅支持针对 Unique SQL ID 打 PATCH，如果存在 Unique SQL ID 冲突，用于 Hint 调优的 SQL PATCH 可能影响性能，但不影响语义正确性。
- 仅支持不改变 SQL 语义的 Hint 作为 PATCH，不支持 SQL 改写。
- 不支持逻辑备份、恢复。
- 不支持创建时校验 PATCH 合法性，如果 PATCH 的 Hint 存在语法或语义错误，不影响查询正确执行。
- 仅初始用户、运维管理员、监控管理员、系统管理员用户有权限执行。
- 库之间不共享，创建 SQL PATCH 时需要连接目标库。
- 配置集中式备机可读时，需要指定主机执行 SQL PATCH 创建 / 修改 / 删除函数调用，备机执行报错。
- SQL PATCH 同步给备机存在一定延迟，待备机回放相关日志后 PATCH 生效。
- 不支持对存储过程中的 SQL 语句生效，当前机制不会对存储过程内语句生成 Unique SQL ID。
- 用于规避的 Abort Patch 不建议在数据库中长期使用，只应该作为临时规避方法。遇到内核问题所导致的特定语句触发数据库服务不可用问题，需要尽快修改业务或升级内核版本解决问题。并且升级后由于 Unique SQL ID 生成方法可能变化，可能导致规避方法失效。
- 当前，除 DML 语句之外，其他 SQL 语句（如 CREATE TABLE 等）的 Unique SQL ID 是对语句文本直接哈希生成的，所以对于此类语句，SQL PATCH 对大小写、空格、换行等敏感，即不同的文本的语句，即使语义相对，仍然需要对应不同的 SQL PATCH。对于 DML，则同一个 SQL PATCH 可以对不同入参的语句生效，并且忽略大小写和空格。
- 本特性依赖于资源实时监控功能，需要开启
enable_resource_track参数并且设置instr_unique_sql_count大于0。对于不同的语句，如果生成的 Unique SQL ID 冲突，会导致 SQL PATCH 错误的命中预期外的其他语句。其中用于调优的 Hint PATCH 副作用相对较小，Abort Patch 需要谨慎使用。
WDR
WDR（Workload Diagnosis Report）是 openGauss 数据库的工作负载诊断工具，通过对比两次系统性能快照数据生成诊断报告，帮助 DBA 分析数据库在特定时间段的性能表现，识别资源瓶颈和 SQL 效率问题。类似于 Oracle 的 AWR。
WDR 主要依赖两个组件：
- SNAPSHOT 性能快照：可以配置成按一定时间间隔从内核采集一定量的性能数据，并持久化在用户表空间。任何一个 SNAPSHOT 都可以作为一个性能基线，通过与其他 SNAPSHOT 比较，分析出与基线的性能表现。
- WDR Reporter：报表生成工具，基于两个 SNAPSHOT 分析系统总体性能表现，计算出更多具体性能指标在两个时间段之间的变化量，生成 SUMMARY 和 DETAIL 两个不同级别的性能数据。
参数配置
启用和优化 WDR 需配置以下关键参数：
| 参数名 | 作用 | 默认值 | 配置示例 |
|---|---|---|---|
enable_wdr_snapshot | 启用/禁用快照功能 | off | ALTER SYSTEM SET enable_wdr_snapshot=on; |
wdr_snapshot_interval | 自动快照间隔（分钟） | 60 | 设为30分钟：ALTER SYSTEM SET wdr_snapshot_interval=30; |
wdr_snapshot_retention_days | 快照保留天数 | 8 | 设为7天：ALTER SYSTEM SET wdr_snapshot_retention_days=7; |
说明：
- 设置参数
enable_wdr_snapshot为on会在用户表空间pg_default，数据库postgres下新建模式snapshot，用于持久化 WDR 快照数据。默认初始化用户omm或monadmin用户可以访问snapshot模式 。 - 禁止对
snapshot模式下的表进行增删改等操作，人为对这些表的修改或破坏可能会导致 WDR 各种异常情况，甚至导致 WDR 不可用。 - WDR 快照数据表来源于
DBE_PERF模式下的视图。
生成步骤
WDR 报告生成步骤如下：
- 确认参数
enable_wdr_snapshot为on，且当前快照数量大于等于2。
omm@postgres=# show enable_wdr_snapshot;
enable_wdr_snapshot
---------------------
on
(1 row)
omm@postgres=# select * from snapshot.snapshot order by start_ts desc limit 10;
snapshot_id | start_ts | end_ts
-------------+-------------------------------+-------------------------------
1 | 2025-04-18 10:13:20.355957+08 | 2025-04-18 10:13:24.690371+08
(1 row)
- 数据库中只有一个快照或者需要查看在当前时间段数据库的监控数据，可以手动创建快照。在单机节点或集群主节点上执行如下命令手动创建快照：
omm@postgres=# select create_wdr_snapshot();
create_wdr_snapshot
-----------------------------------------
WDR snapshot request has been submitted
(1 row)
omm@postgres=# select * from snapshot.snapshot order by start_ts desc limit 10;
snapshot_id | start_ts | end_ts
-------------+-------------------------------+-------------------------------
2 | 2025-04-18 10:52:50.351289+08 | 2025-04-18 10:52:53.458109+08
1 | 2025-04-18 10:13:20.355957+08 | 2025-04-18 10:13:24.690371+08
(2 rows)
- 创建报告文件，设置报告格式
omm@postgres=# \! touch /home/omm/wdrtest.html
omm@postgres=# \a \t \o /home/omm/wdrtest.html
Output format is unaligned.
Showing only tuples.
其中：
\a：不显示表行列符号\t：不显示列名\o：指定输出文件
- 生成 HTML 格式的 WDR 报告
语法：
select generate_wdr_report(begin_snap_id Oid, end_snap_id Oid, int report_type, int report_scope, int node_name );
参数说明：
begin_snap_id：查询时间段开始的快照的 ID（表snapshot.snapshot中的snapshot_id）end_snap_id：查询时间段结束的快照的 ID（表snapshot.snapshot中的snapshot_id）report_type：指定生成的报告类型，有：summary：汇总数据detail：明细数据all：包含summary和detail
report_scope：指定生成报告的范围，可以为：cluster：数据库级别的信息node：节点级别的信息
node_name：节点名称- 在
report_scope指定为node时，需要把该参数指定为对应节点的名称。（节点名称可以执行select * from pg_node_env;查询）。 - 在
report_scope为cluster时，该值可以省略或者指定为空或NULL。
- 在
例子：生成当前节点的 WDR 报告
omm@postgres=# select generate_wdr_report(1, 2, 'all', 'node', pgxc_node_str()::cstring);
- 关闭输出选项及格式化输出命令
omm@postgres=# \o \a \t
Output format is aligned.
Tuples only is off.
- 查看报告，报告内容包括：
| 项目 | 描述 |
|---|---|
| Database Stat（集群范围） | 数据库维度性能统计信息：事务，读写，行活动，写冲突，死锁等。 |
| Load Profile（集群范围） | 集群维度的性能统计信息：CPU 时间，DB 时间，逻辑读/物理读，IO 性能，登入登出，负载强度，负载性能表现等。 |
| Instance Efficiency Percentages（集群/节点范围） | 集群级或者节点缓冲命中率。 |
| IO Profile（集群/节点范围） | 集群或者节点维度的 IO 的使用情况。 |
| Top 10 Events by Total Wait Time（节点范围） | 最消耗时间的事件。 |
| Wait Classes by Total Wait Time（节点范围） | 最消耗时间的等待时间分类。 |
| Host CPU（节点范围） | 主机 CPU 消耗。 |
| Memory Statistics（节点范围） | 内核内存使用分布。 |
| Time Model（节点范围） | 节点范围的语句的时间分布信息。 |
| Wait Events（节点范围） | 节点级别的等待事件的统计信息。 |
| Cache IO Stats (集群/节点范围) | 用户的表、索引的 IO 的统计信息。 |
| Utility status （节点范围） | 复制槽和后台 CHECKPOINT 的状态信息。 |
| Object stats（集群/节点范围） | 表、索引维度的性能统计信息。 |
| Configuration settings（节点范围） | 节点配置。 |
| SQL Statistics（集群/节点范围） | SQL 语句各个维度性能统计：端到端时间，行活动，缓存命中，CPU 消耗，时间消耗细分。 |
| SQL Detail（集群/节点范围） | SQL 语句文本详情。 |
Session 性能诊断
Session 性能诊断提供给用户 Session 级别的性能问题诊断。
- 查看最近用户 Session 最耗资源的事件。
- 查看最近比较占资源的 SQL 把资源都消耗在哪些等待事件上。
- 查看最近比较耗资源的 Session 把资源都花费在哪些等待事件上。
- 查看最近最耗资源的用户的信息。
- 查看过去 Session 相互阻塞的等待关系。
Session 性能诊断提供对当前系统所有活跃 Session 进行诊断的能力。由于实时采集所有活跃 Session 的指标对用户负载的影响加大，因此采取 Session 快照的技术对活跃 Session 的指标进行采样。
从采样中统计出活跃 Session 的统计指标，这些统计指标从客户端信息、执行开始、结束时间、SQL 文本、等待事件、当前数据库对象等维度，反映活跃 Session 的基本信息、状态、持有的资源。
基于概率统计的活跃 Session 信息，可以帮助用户诊断系统中哪些 Session 消耗了更多的 CPU、内存资源，哪些数据库对象是热对象，哪些 SQL 消耗了更多的关键事件资源等，从而定位出有问题 Session、SQL、数据库设计。
Session 采样数据分为两级：
- 第一级为实时信息，存储在内存中，展示最近几分钟的活跃 Session 信息，具有最高的精度；
- 第二级为持久化历史信息，存储在磁盘文件中，展示过去很长一段时间的历史活跃 Session 信息，从内存数据中抽样而来，适合长时间跨度的统计分析。
例子：查看 Session 之间的阻塞关系
omm@postgres=# select sessionid, block_sessionid from pg_thread_wait_status where block_sessionid is not null;
sessionid | block_sessionid
-----------------+-----------------
139766982637312 | 139767474743040
(1 row)
sessionid 表示被阻塞者的会话 ID，block_sessionid 表示阻塞者的会话 ID。
例子：查看最耗资源的等待事件
SELECT s.type, s.event, t.count
FROM dbe_perf.wait_events s,
(SELECT event, COUNT(*)
FROM dbe_perf.local_active_session
WHERE sample_time > now() - 5 / (24 * 60)
GROUP BY event) t
WHERE s.event = t.event
ORDER BY count DESC;
type | event | count
------------+-----------------------+-------
STATUS | none | 1145
STATUS | wait cmd | 299
LOCK_EVENT | transactionid | 114
STATUS | HashAgg - build hash | 2
STATUS | HashJoin - build hash | 1
(5 rows)
例子：查看最近五分钟较耗资源的 Session 把资源都花费在哪些事件上
SELECT sessionid, start_time, event, count
FROM (SELECT sessionid, start_time, event, COUNT(*)
FROM dbe_perf.local_active_session
WHERE sample_time > now() - 5 / (24 * 60)
GROUP BY sessionid, start_time, event) as t
ORDER BY SUM(t.count) OVER(PARTITION BY t. sessionid, start_time) DESC,
t.event;
sessionid | start_time | event | count
-----------------+-------------------------------+-----------------------+-------
139767676266240 | 2025-04-18 10:07:03.90451+08 | none | 286
139767623776000 | 2025-04-18 10:07:03.939161+08 | none | 286
139767977207552 | 2025-04-18 10:07:03.070277+08 | none | 286
139767910496000 | 2025-04-18 10:07:03.911593+08 | none | 286
139766982637312 | 2025-04-18 16:11:30.048049+08 | transactionid | 113
139766982637312 | 2025-04-18 16:11:30.048049+08 | wait cmd | 173
139767474743040 | 2025-04-18 15:36:41.11685+08 | wait cmd | 286
139767051908864 | 2025-04-18 16:12:13.760989+08 | HashAgg - build hash | 3
139767051908864 | 2025-04-18 16:12:13.760989+08 | HashJoin - build hash | 1
139767521998592 | 2025-04-18 10:07:03.053767+08 | none | 1
(10 rows)
例子：最近五分钟比较占资源的 SQL 把资源都消耗在哪些事件上
SELECT query_id, event, count
FROM (SELECT query_id, event, COUNT(*)
FROM dbe_perf.local_active_session
WHERE sample_time > now() - 5 / (24 * 60)
GROUP BY query_id, event) t
ORDER BY SUM(t.count) OVER(PARTITION BY t.query_id) DESC, t.event DESC;
query_id | event | count
------------------+----------------------+-------
0 | wait cmd | 520
0 | none | 1145
7881299347909159 | transactionid | 52
7881299347909240 | HashAgg - build hash | 1
7881299347909260 | HashAgg - build hash | 1
(5 rows)