摘要:
DDL:
创建库表及插入数据：
create database d1;\c d1;create table t1( a int, b int );
create table t2( a int, b int );insert into t1(a,b) values(3,4);
insert into t1(a,b) values(5,6);insert into t2(a,b) values(3,1);
insert into t2(a,b) values(7,2);准备查询SQL:
SELECT * FROM t1 LEFT JOIN t2 ON t2.a = t1.a;一. 客户端连接pg服务
参考: 2024-05-08 postgres-编译初始化及运行-记录-CSDN博客
二. 找到客户端连接的pg的后端进程的pid
命令:
ps -ef | grep postgres | grep -v grep | grep -v psql
执行结果:
[root@192 trunk]# ps -ef | grep postgres | grep -v grep | grep -v psql
kevin 41716 1 0 02:00 ? 00:00:00 /usr/local/pgsql/bin/postgres
kevin 41718 41716 0 02:00 ? 00:00:00 postgres: checkpointer
kevin 41719 41716 0 02:00 ? 00:00:00 postgres: background writer
kevin 41720 41716 0 02:00 ? 00:00:00 postgres: walwriter
kevin 41721 41716 0 02:00 ? 00:00:00 postgres: autovacuum launcher
kevin 41722 41716 0 02:00 ? 00:00:00 postgres: stats collector
kevin 41723 41716 0 02:00 ? 00:00:00 postgres: logical replication launcher
kevin 41772 41716 0 02:18 ? 00:00:00 postgres: kevin d1 [local] idle
客户端连接的pg后端进程pid:
kevin 41772 41716 0 02:18 ? 00:00:00 postgres: kevin d1 [local] idle
三. gdb挂在pg后端进程并打断点
gdb挂载进程：
[root@192 trunk]# gdb -p 41772
GNU gdb (GDB) Red Hat Enterprise Linux 8.2-20.el8
Copyright (C) 2018 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
Type "show copying" and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
Type "show configuration" for configuration details.
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
Find the GDB manual and other documentation resources online at:<http://www.gnu.org/software/gdb/documentation/>.For help, type "help".
Type "apropos word" to search for commands related to "word".
Attaching to process 41772
Reading symbols from /usr/local/pgsql/bin/postgres...done.
Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done.
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
Reading symbols from /lib64/librt.so.1...(no debugging symbols found)...done.
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Reading symbols from /lib64/libm.so.6...Reading symbols from .gnu_debugdata for /lib64/libm.so.6...(no debugging symbols found)...done.
(no debugging symbols found)...done.
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Reading symbols from /lib64/ld-linux-x86-64.so.2...done.
Reading symbols from /lib64/libnss_files.so.2...Reading symbols from .gnu_debugdata for /lib64/libnss_files.so.2...(no debugging symbols found)...done.
(no debugging symbols found)...done.
0x00007fa384b8e2cb in epoll_wait () from /lib64/libc.so.6
Missing separate debuginfos, use: yum debuginfo-install glibc-2.28-251.el8.x86_64
(gdb)
为核心函数打断点:
例如： create_scan_plan
(gdb)
(gdb) b create_scan_plan
Breakpoint 1 at 0x7dbe25: file createplan.c, line 558.
(gdb) c
Continuing.
四. 客户端执行查询，查看gdb的堆栈
客户端执行查询:
使用空值拒绝的外连接转内连接的规则的sql
SELECT * FROM t1 LEFT JOIN t2 ON t2.a = t1.a WHERE t2.b < 5;
gdb中的函数调用堆栈:
(gdb) c
Continuing.Breakpoint 1, create_scan_plan (root=0x2ce5808, best_path=0x2cfb508, flags=2) at createplan.c:558
558 RelOptInfo *rel = best_path->parent;
(gdb) bt
#0 create_scan_plan (root=0x2ce5808, best_path=0x2cfb508, flags=2) at createplan.c:558
#1 0x00000000007dbaa4 in create_plan_recurse (root=0x2ce5808, best_path=0x2cfb508, flags=2) at createplan.c:410
#2 0x00000000007e2777 in create_mergejoin_plan (root=0x2ce5808, best_path=0x2cfbee8) at createplan.c:4376
#3 0x00000000007dc862 in create_join_plan (root=0x2ce5808, best_path=0x2cfbee8) at createplan.c:1067
#4 0x00000000007dbac0 in create_plan_recurse (root=0x2ce5808, best_path=0x2cfbee8, flags=1) at createplan.c:415
#5 0x00000000007db9ba in create_plan (root=0x2ce5808, best_path=0x2cfbee8) at createplan.c:346
#6 0x00000000007ecfbf in standard_planner (parse=0x2c16948, query_string=0x2c15718 "SELECT * FROM t1 LEFT JOIN t2 ON t2.a = t1.a;", cursorOptions=2048, boundParams=0x0)at planner.c:407
#7 0x00000000007ecd2a in planner (parse=0x2c16948, query_string=0x2c15718 "SELECT * FROM t1 LEFT JOIN t2 ON t2.a = t1.a;", cursorOptions=2048, boundParams=0x0) at planner.c:271
#8 0x000000000090572b in pg_plan_query (querytree=0x2c16948, query_string=0x2c15718 "SELECT * FROM t1 LEFT JOIN t2 ON t2.a = t1.a;", cursorOptions=2048, boundParams=0x0)at postgres.c:847
#9 0x0000000000905869 in pg_plan_queries (querytrees=0x2ce5728, query_string=0x2c15718 "SELECT * FROM t1 LEFT JOIN t2 ON t2.a = t1.a;", cursorOptions=2048, boundParams=0x0)at postgres.c:939
#10 0x0000000000905bc0 in exec_simple_query (query_string=0x2c15718 "SELECT * FROM t1 LEFT JOIN t2 ON t2.a = t1.a;") at postgres.c:1133
#11 0x000000000090a0ef in PostgresMain (argc=1, argv=0x7fff70e94eb0, dbname=0x2c42428 "d1", username=0x2c10a58 "kevin") at postgres.c:4496
#12 0x0000000000857a54 in BackendRun (port=0x2c39e30) at postmaster.c:4530
#13 0x00000000008573c1 in BackendStartup (port=0x2c39e30) at postmaster.c:4252
#14 0x0000000000853b10 in ServerLoop () at postmaster.c:1745
#15 0x00000000008533c9 in PostmasterMain (argc=1, argv=0x2c0ea10) at postmaster.c:1417
#16 0x0000000000760270 in main (argc=1, argv=0x2c0ea10) at main.c:209
查询优化后, 查询执行的调用堆栈:
#0 ExecScanFetch (node=0x14328d8, accessMtd=0x730efe <SeqNext>, recheckMtd=0x730fa8 <SeqRecheck>) at execScan.c:39
#1 0x00000000006f86b3 in ExecScan (node=0x14328d8, accessMtd=0x730efe <SeqNext>, recheckMtd=0x730fa8 <SeqRecheck>) at execScan.c:199
#2 0x0000000000730ff3 in ExecSeqScan (pstate=0x14328d8) at nodeSeqscan.c:112
#3 0x00000000006f4ca9 in ExecProcNodeFirst (node=0x14328d8) at execProcnode.c:463
#4 0x0000000000732343 in ExecProcNode (node=0x14328d8) at ../../../src/include/executor/executor.h:257
#5 0x000000000073248a in ExecSort (pstate=0x14326c8) at nodeSort.c:108
#6 0x00000000006f4ca9 in ExecProcNodeFirst (node=0x14326c8) at execProcnode.c:463
#7 0x0000000000726e97 in ExecProcNode (node=0x14326c8) at ../../../src/include/executor/executor.h:257
#8 0x0000000000727af0 in ExecMergeJoin (pstate=0x14322b8) at nodeMergejoin.c:656
#9 0x00000000006f4ca9 in ExecProcNodeFirst (node=0x14322b8) at execProcnode.c:463
#10 0x00000000006ea204 in ExecProcNode (node=0x14322b8) at ../../../src/include/executor/executor.h:257
#11 0x00000000006ec6bb in ExecutePlan (estate=0x1432078, planstate=0x14322b8, use_parallel_mode=false, operation=CMD_SELECT, sendTuples=true, numberTuples=0, direction=ForwardScanDirection, dest=0x1423f98, execute_once=true) at execMain.c:1551
#12 0x00000000006ea76a in standard_ExecutorRun (queryDesc=0x136dfc8, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:361
#13 0x00000000006ea602 in ExecutorRun (queryDesc=0x136dfc8, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:305
#14 0x000000000090c03e in PortalRunSelect (portal=0x13ad5d8, forward=true, count=0, dest=0x1423f98) at pquery.c:921
#15 0x000000000090bd2d in PortalRun (portal=0x13ad5d8, count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x1423f98, altdest=0x1423f98, qc=0x7ffff3ea58b0)at pquery.c:765
#16 0x0000000000905d39 in exec_simple_query (query_string=0x134a598 "SELECT * FROM t1 LEFT JOIN t2 ON t2.a = t1.a WHERE t2.b < 5;") at postgres.c:1214
#17 0x000000000090a0ef in PostgresMain (argc=1, argv=0x7ffff3ea5b40, dbname=0x13775d8 "d1", username=0x1345a48 "kevin") at postgres.c:4496
#18 0x0000000000857a54 in BackendRun (port=0x136f010) at postmaster.c:4530
#19 0x00000000008573c1 in BackendStartup (port=0x136f010) at postmaster.c:4252
#20 0x0000000000853b10 in ServerLoop () at postmaster.c:1745
#21 0x00000000008533c9 in PostmasterMain (argc=1, argv=0x1343a00) at postmaster.c:1417
#22 0x0000000000760270 in main (argc=1, argv=0x1343a00) at main.c:209
五. 查询计划分析
PostgreSQL: Documentation: 16: 14.1. Using EXPLAIN
explain参数:
explain [ ( option [,...] ) ] statement
explain [ analyze ] [ verbose ] statementoption选项有：
analyze [ boolean ] //会实际执行SQL，并返回SQL实际执行的相关统计信息
verbose [ boolean ] //显示执行计划的附加信息
costs [ boolean ] //默认开启，显示每个计划节点的启动成本、总成本，预计返回行数，预估返回结果集每行平均宽度
buffers [ boolean ] //显示缓冲区使用信息
format [ text | xml | json | yaml ] //执行计划执行输出格式
例子:
d1=# EXPLAIN ANALYZE VERBOSE
d1-# SELECT * FROM t1 LEFT JOIN t2 ON t2.a = t1.a WHERE t2.b < 5;
***(Single step mode: verify command)*******************************************
explain analyze verbose
SELECT * FROM t1 LEFT JOIN t2 ON t2.a = t1.a WHERE t2.b < 5;
***(press return to proceed or enter x and return to cancel)********************QUERY PLAN
-------------------------------------------------------------------------------------------------------------------Merge Join (cost=232.74..364.14 rows=8509 width=16) (actual time=0.027..0.030 rows=1 loops=1)Output: t1.a, t1.b, t2.a, t2.bMerge Cond: (t2.a = t1.a)-> Sort (cost=74.23..76.11 rows=753 width=8) (actual time=0.018..0.018 rows=2 loops=1)Output: t2.a, t2.bSort Key: t2.aSort Method: quicksort Memory: 25kB-> Seq Scan on public.t2 (cost=0.00..38.25 rows=753 width=8) (actual time=0.009..0.011 rows=2 loops=1)Output: t2.a, t2.bFilter: (t2.b < 5)-> Sort (cost=158.51..164.16 rows=2260 width=8) (actual time=0.005..0.006 rows=2 loops=1)Output: t1.a, t1.bSort Key: t1.aSort Method: quicksort Memory: 25kB-> Seq Scan on public.t1 (cost=0.00..32.60 rows=2260 width=8) (actual time=0.002..0.003 rows=2 loops=1)Output: t1.a, t1.bPlanning Time: 0.177 msExecution Time: 0.064 ms
(18 rows)
