摘要:
2024-05-08 postgres-調試及分析-記錄
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)
)
)
